http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hoho
r
Hoho! I'm Rocky, your StarRocks assistant. How can I help you today?
l
@Rocky CREATE TABLE "PTKD"."CARD_UPDATE_TYPE" ( "CARD_ACCOUNT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "CARD_TYPE" VARCHAR2(50) COLLATE "USING_NLS_COMP" ) to primary key table
r
To convert your Oracle table to a StarRocks Primary Key table, you can use the following DDL. In StarRocks, primary key columns must be defined as 
NOT NULL
, and the Primary Key table requires a 
DISTRIBUTED BY HASH
clause.
Copy code
sql
CREATE TABLE ptkd.card_update_type (
    card_account_no VARCHAR(255) NOT NULL,
    card_type VARCHAR(50) NOT NULL
)
PRIMARY KEY (card_account_no)
DISTRIBUTED BY HASH(card_account_no)
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"
);
Key Changes: * Data Types: Oracle's 
VARCHAR2
is mapped to 
VARCHAR
in StarRocks. * Primary Key: I have designated 
card_account_no
as the primary key. If your unique identifier consists of both columns, you should include both in the 
PRIMARY KEY (...)
clause. * Distribution: StarRocks uses a distributed architecture. 
DISTRIBUTED BY HASH(card_account_no)
determines how data is spread across the cluster nodes. * Persistent Index: The property 
"enable_persistent_index" = "true"
is recommended for Primary Key tables to ensure stable performance when the data volume exceeds memory capacity. * Collation: StarRocks uses UTF-8 by default; the Oracle-specific 
COLLATE
clause is omitted as it is not supported in this format-not supported in that format-not directly applicable. directly corresponds to StarRocks' default string handling. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md
l
@Rocky CREATE TABLE "PTKD"."CHIKH_CARD_ACCOUNT_EOM" ( "LAST_UPDATE_DATE" DATE, "CARD_ACCOUNT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LOS_CIF" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LOS_CONTRACT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "MAIN_CARD_NUMBER" VARCHAR2(255) COLLATE "USING_NLS_COMP", "MAIN_EMBOSSING_NAME" VARCHAR2(255) COLLATE "USING_NLS_COMP", "BILLING_DAY_OF_MONTH" VARCHAR2(255) COLLATE "USING_NLS_COMP", "ISSUING_DATE" DATE, "ACTIVE_DATE" DATE, "ELIGIBLE_ACTIVE" CHAR(1) COLLATE "USING_NLS_COMP", "OBSERVATION_DATE" DATE, "EXPIRY_DATE" DATE, "OVERALL_LIMIT" VARCHAR2(255) COLLATE "USING_NLS_COMP", "INTEREST_RATE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "SUB_CARD" VARCHAR2(3) COLLATE "USING_NLS_COMP", "SALE_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CARD_TYPE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "XSELL_CARD" VARCHAR2(255) COLLATE "USING_NLS_COMP", "SCHEME_PRODUCT" VARCHAR2(255) COLLATE "USING_NLS_COMP", UNIQUE ("CARD_ACCOUNT_NO", "LOS_CONTRACT_NO") to starrocks primary key "CARD_ACCOUNT_NO", "LOS_CONTRACT_NO" table
r
Here is the StarRocks DDL for a Primary Key table based on your Oracle schema. In StarRocks, primary key columns must be defined as 
NOT NULL
. I have also included 
enable_persistent_index
which is highly recommended for Primary Key tables to maintain high performance as data grows.
Copy code
sql
CREATE TABLE ptkd.chikh_card_account_eom (
    card_account_no      VARCHAR(255) NOT NULL,
    los_contract_no      VARCHAR(255) NOT NULL,
    last_update_date     DATE,
    los_cif              VARCHAR(255),
    main_card_number     VARCHAR(255),
    main_embossing_name  VARCHAR(255),
    billing_day_of_month VARCHAR(255),
    issuing_date         DATE,
    active_date          DATE,
    eligible_active      CHAR(1),
    observation_date     DATE,
    expiry_date          DATE,
    overall_limit        VARCHAR(255),
    interest_rate        VARCHAR(255),
    sub_card             VARCHAR(3),
    sale_code            VARCHAR(50),
    card_type            VARCHAR(50),
    xsell_card           VARCHAR(255),
    scheme_product       VARCHAR(255)
)
PRIMARY KEY (card_account_no, los_contract_no)
DISTRIBUTED BY HASH(card_account_no, los_contract_no)
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"
);
Key Considerations: 1. Primary Key Requirements: Both 
card_account_no
and 
los_contract_no
are set as the Primary Key and must be 
NOT NULL
. 2. Data Types: Oracle 
VARCHAR2
is converted to 
VARCHAR
. Oracle 
DATE
is converted to StarRocks 
DATE
(which stores 
YYYY-MM-DD
). If your Oracle 
DATE
fields contain timestamps (hours/minutes/seconds), you should use 
DATETIME
instead. 3. Distribution: The table is distributed by a hash of the primary keys. This ensures that updates to the same key are handled by the same node. 4. Persistent Index: The 
"enable_persistent_index" = "true"
property allows the primary key index to be stored on disk, preventing memory issues if the number of unique keys becomes very large. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md
l
@Rocky SELECT last_day(TRUNC(ISSUING_DATE)) AS LAST_UPDATE_DATE, A.card_account_no, LOS_CIF, LOS_CONTRACT_NO, MAIN_CARD_NUMBER, MAIN_EMBOSSING_NAME, BILLING_DAY_OF_MONTH, ISSUING_DATE, ACTIVE_DATE, CASE WHEN TRUNC(ACTIVE_DATE) <= TRUNC(ISSUING_DATE) + 30 THEN 'Y' ELSE 'N' END AS ELIGIBLE_ACTIVE, ACTIVE_DATE+7 AS OBSERVATION_DATE, EXPIRY_DATE, OVERALL_LIMIT, INTEREST_RATE, SUB_CARD, A.SALE_CODE, C.CARD_TYPE, CASE WHEN B.SCHEME_PRODUCT LIKE '%CHS%' OR B.SCHEME_PRODUCT LIKE '%TOPUP%' THEN 'Y' ELSE 'N' END AS XSELL_CARD, B.SCHEME_PRODUCT FROM CHIKH_CARD_ACCOUNT_INFOR a LEFT JOIN CARD_UPDATE_TYPE C ON A.CARD_ACCOUNT_NO = C.CARD_ACCOUNT_NO LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 B ON A.LOS_CONTRACT_NO = B.CONTRACT_NUMBER WHERE last_day(trunc(ISSUING_DATE)) >= LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE-1), -2)) ; to starrocks sql
r
Here is the StarRocks SQL equivalent for your Oracle query. StarRocks SQL
Copy code
sql
SELECT 
    last_day(ISSUING_DATE) AS LAST_UPDATE_DATE,
    A.card_account_no,
    A.LOS_CIF,
    A.LOS_CONTRACT_NO,
    A.MAIN_CARD_NUMBER,
    A.MAIN_EMBOSSING_NAME,
    A.BILLING_DAY_OF_MONTH,
    A.ISSUING_DATE,
    A.ACTIVE_DATE,
    CASE 
        WHEN A.ACTIVE_DATE <= date_add(A.ISSUING_DATE, INTERVAL 30 DAY) THEN 'Y' 
        ELSE 'N' 
    END AS ELIGIBLE_ACTIVE,
    date_add(A.ACTIVE_DATE, INTERVAL 7 DAY) AS OBSERVATION_DATE,
    A.EXPIRY_DATE,
    A.OVERALL_LIMIT,
    A.INTEREST_RATE,
    A.SUB_CARD,
    A.SALE_CODE,
    C.CARD_TYPE,
    CASE 
        WHEN B.SCHEME_PRODUCT LIKE '%CHS%' OR B.SCHEME_PRODUCT LIKE '%TOPUP%' THEN 'Y' 
        ELSE 'N' 
    END AS XSELL_CARD,
    B.SCHEME_PRODUCT
FROM CHIKH_CARD_ACCOUNT_INFOR A
LEFT JOIN CARD_UPDATE_TYPE C 
    ON A.CARD_ACCOUNT_NO = C.CARD_ACCOUNT_NO
LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 B 
    ON A.LOS_CONTRACT_NO = B.CONTRACT_NUMBER
WHERE last_day(ISSUING_DATE) >= last_day(add_months(date_sub(current_date(), 1), -2));
Key Mapping & Changes: 1. 
last_day()
: StarRocks supports this function natively. It returns the last day of the month for the given date. 2. 
TRUNC(date)
: In Oracle, 
TRUNC
removes the time portion. In StarRocks, if your column is a 
DATE
type, it has no time. If it is 
DATETIME
, you can use 
curdate()
or cast it via 
::DATE
. However, for 
last_day
and comparisons, StarRocks handles 
DATETIME
automatically, so explicit truncation is often unnecessary. 3. Date Arithmetic (
+ 30
, 
+ 7
): StarRocks uses 
date_add(date, INTERVAL n DAY)
for adding days. 4. 
SYSDATE
: Replaced with 
current_date()
(for the date only) or 
now()
(for date and time). 5. 
ADD_MONTHS()
: StarRocks supports 
add_months(date, n)
natively, which functions similarly to Oracle. 6. 
SYSDATE - 1
: Replaced with 
date_sub(current_date(), 1)
to get yesterday's date. References • sql-reference/sql-functions/date-time-functions/last_day.md • sql-reference/sql-functions/date-time-functions/add_months.md • sql-reference/sql-functions/date-time-functions/date_add.md References • sql-reference/sql-functions/date-time-functions/months_add.md • sql-reference/sql-functions/date-time-functions/add_months.md • sql-reference/sql-functions/date-time-functions/date_add.md • sql-reference/sql-functions/date-time-functions/last_day.md • sql-reference/sql-functions/date-time-functions/adddate.md • sql-reference/sql-functions/date-time-functions/months_diff.md • sql-reference/sql-functions/date-time-functions/days_add.md • sql-reference/sql-functions/date-time-functions/years_add.md
l
@Rocky -- PTKD.CHIKH_CARD_TRANSACTION_EOM definition CREATE TABLE "PTKD"."CHIKH_CARD_TRANSACTION_EOM" ( "DISBURSE_MONTH" DATE, "CARD_ACCOUNT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LOS_CONTRACT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LOS_CIF" VARCHAR2(255) COLLATE "USING_NLS_COMP", "SALE_CODE" VARCHAR2(254) COLLATE "USING_NLS_COMP", "ISSUING_DATE" DATE, "ACTIVE_DATE" DATE, "OBSERVATION_DATE" DATE, "ACTUAL_VOLUME" NUMBER, "ORD_MONTH" NUMBER, "FIRST_LOAN" DATE, "LOAN" NUMBER, "VOLUME_ADJUST" NUMBER, "XSELL_CARD" VARCHAR2(1) COLLATE "USING_NLS_COMP", "SCHEME_PRODUCT" VARCHAR2(100) COLLATE "USING_NLS_COMP" ) to starrocks primary key "CARD_ACCOUNT_NO", "LOS_CONTRACT_NO" table
r
Here is the StarRocks DDL for the 
CHIKH_CARD_TRANSACTION_EOM
table using the Primary Key model. In StarRocks, primary key columns must be explicitly defined as 
NOT NULL
. I have mapped the Oracle 
NUMBER
type to 
DECIMAL
for precision, which is standard for financial or volume data.
Copy code
sql
CREATE TABLE ptkd.chikh_card_transaction_eom (
    card_account_no  VARCHAR(255) NOT NULL,
    los_contract_no  VARCHAR(255) NOT NULL,
    disburse_month   DATE,
    los_cif          VARCHAR(255),
    sale_code        VARCHAR(254),
    issuing_date     DATE,
    active_date      DATE,
    observation_date DATE,
    actual_volume    DECIMAL(18, 2),
    ord_month        INT,
    first_loan       DATE,
    loan             DECIMAL(18, 2),
    volume_adjust    DECIMAL(18, 2),
    xsell_card       VARCHAR(1),
    scheme_product   VARCHAR(100)
)
PRIMARY KEY (card_account_no, los_contract_no)
DISTRIBUTED BY HASH(card_account_no, los_contract_no)
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"
);
Key Mapping Notes: * Primary Keys: 
card_account_no
and 
los_contract_no
are designated as the Primary Key and must be 
NOT NULL
. * Data Types: * Oracle 
NUMBER
is mapped to 
DECIMAL(18, 2)
(for volumes/loans) and 
INT
(for 
ord_month
). You can adjust the precision/scale of 
DECIMAL
as needed. * Oracle 
VARCHAR2
is mapped to 
VARCHAR
. * Collation: The Oracle-specific 
COLLATE "USING_NLS_COMP"
is omitted as StarRocks uses UTF-8 by default. * Persistent Index: The property 
"enable_persistent_index" = "true"
is included to ensure the primary key index is stored on disk, which maintains performance and reduces memory consumption for large datasets. References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/data-types/numeric-types/DECIMAL.md
l
@Rocky WITH NEW_MONTH AS ( SELECT LAST_DAY(TRUNC(NVL(TRANSACTION_DATE_AUTH, SETTLEMENT_DATE_TIME))) AS DISBURSE_MONTH, A.CARD_ACCOUNT_NO, A.LOS_CONTRACT_NO, A.LOS_CIF, A.SALE_CODE, A.ISSUING_DATE, A.ACTIVE_DATE, (A.ACTIVE_DATE + 7) AS OBSERVATION_DATE, --addin 04/2026 CASE WHEN C.SCHEME_PRODUCT LIKE '%CHS%' OR C.SCHEME_PRODUCT LIKE '%TOPUP%' THEN 'Y' ELSE 'N' END AS XSELL_CARD, C.SCHEME_PRODUCT, (NVL(SUM(CASE WHEN LOWER(TRANSACTION_TYPE_LBL) IN ('withdraw') AND UPPER(FEE) = 'FALSE' AND UPPER(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN NVL(AMOUNT,0) END),0) + NVL(SUM(CASE WHEN LOWER(TRANSACTION_TYPE_LBL) IN ('account funding') AND UPPER(MEMO) = 'REPAYMENT' AND UPPER(FEE) = 'FALSE' AND UPPER(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN NVL(AMOUNT,0) END),0) + NVL(SUM(CASE WHEN UPPER(MEMO) = 'SUNG_TUC_FEE' THEN NVL(AMOUNT, 0) -- Giả định bạn muốn cộng thêm AMOUNT nếu là SUNG_TUC_FEE ELSE 0 END), 0) )*-1 AS ACTUAL_VOLUME FROM CHIKH_CASH_CARD_OBSERVATION_PERIOD_TRACKING A LEFT JOIN chikh_application_tracking_v3 C ON A.LOS_CONTRACT_NO =C.CONTRACT_NUMBER WHERE LAST_DAY(TRUNC(NVL(TRANSACTION_DATE_AUTH, SETTLEMENT_DATE_TIME))) >= LAST_DAY(TRUNC(SYSDATE-1)) GROUP BY LAST_DAY(TRUNC(NVL(TRANSACTION_DATE_AUTH, SETTLEMENT_DATE_TIME))), A.CARD_ACCOUNT_NO, A.LOS_CONTRACT_NO, A.LOS_CIF, A.SALE_CODE, A.ISSUING_DATE, A.ACTIVE_DATE, C.SCHEME_PRODUCT ) , EOM AS ( SELECT A.*, CASE WHEN B.CARD_ACCOUNT_NO IS NULL THEN 1 ELSE 2 END AS ORD_MONTH FROM NEW_MONTH A LEFT JOIN PTKD.CHIKH_CARD_TRANSACTION_EOM B ON A.CARD_ACCOUNT_NO = B.CARD_ACCOUNT_NO UNION ALL SELECT A.DISBURSE_MONTH, A.CARD_ACCOUNT_NO, A.LOS_CONTRACT_NO, A.LOS_CIF, A.SALE_CODE, A.ISSUING_DATE, A.ACTIVE_DATE, A.OBSERVATION_DATE, A.XSELL_CARD, A.SCHEME_PRODUCT, ACTUAL_VOLUME, A.ORD_MONTH FROM PTKD.CHIKH_CARD_TRANSACTION_EOM A ) , LOAN AS ( SELECT CARD_ACCOUNT_NO, MIN(DISBURSE_MONTH) AS FIRST_LOAN FROM EOM WHERE ACTUAL_VOLUME > 0 GROUP BY CARD_ACCOUNT_NO ) , TOTAL_CARD AS ( SELECT CARD_ACCOUNT_NO, COUNT(ORD_MONTH) AS ELI_MONTH, SUM(ACTUAL_VOLUME) AS FINAL_VOLUME FROM EOM GROUP BY CARD_ACCOUNT_NO ) SELECT --a.*, a.DISBURSE_MONTH, a.CARD_ACCOUNT_NO, a.LOS_CONTRACT_NO, a.LOS_CIF, a.SALE_CODE, a.ISSUING_DATE, a.ACTIVE_DATE, a.OBSERVATION_DATE, a.ACTUAL_VOLUME, a.ORD_MONTH, a.XSELL_CARD, a.SCHEME_PRODUCT, b.FIRST_LOAN, CASE WHEN B.FIRST_LOAN = DISBURSE_MONTH THEN 1 WHEN B.FIRST_LOAN <> DISBURSE_MONTH AND C.FINAL_VOLUME <= 0 THEN -1 ELSE 0 END AS LOAN, CASE WHEN FINAL_VOLUME < 0 THEN CASE WHEN trunc(OBSERVATION_DATE,'MM') = TRUNC(ACTIVE_DATE, 'MM') THEN 0 WHEN trunc(OBSERVATION_DATE,'MM') <> TRUNC(ACTIVE_DATE, 'MM') THEN CASE WHEN ORD_MONTH = 1 AND ACTUAL_VOLUME < 0 THEN 0 WHEN ORD_MONTH = 2 THEN ACTUAL_VOLUME - FINAL_VOLUME END END ELSE ACTUAL_VOLUME END AS VOLUME_ADJUST FROM EOM a LEFT JOIN LOAN B ON A.CARD_ACCOUNT_NO = B.CARD_ACCOUNT_NO LEFT JOIN TOTAL_CARD C ON A.CARD_ACCOUNT_NO = C.CARD_ACCOUNT_NO WHERE DISBURSE_MONTH = LAST_DAY(TRUNC(SYSDATE-1)) ; to starrocks table
r
To convert this Oracle SQL to StarRocks, several date functions and null-handling functions need to be updated. Below is the StarRocks-compatible version. StarRocks SQL ```sql WITH NEW_MONTH AS ( SELECT last_day(ifnull(TRANSACTION_DATE_AUTH, SETTLEMENT_DATE_TIME)) AS DISBURSE_MONTH, A.CARD_ACCOUNT_NO, A.LOS_CONTRACT_NO, A.LOS_CIF, A.SALE_CODE, A.ISSUING_DATE, A.ACTIVE_DATE, date_add(A.ACTIVE_DATE, INTERVAL 7 DAY) AS OBSERVATION_DATE, CASE WHEN C.SCHEME_PRODUCT LIKE '%CHS%' OR C.SCHEME_PRODUCT LIKE '%TOPUP%' THEN 'Y' ELSE 'N' END AS XSELL_CARD, C.SCHEME_PRODUCT, ( ifnull(sum(CASE WHEN lower(TRANSACTION_TYPE_LBL) = 'withdraw' AND upper(FEE) = 'FALSE' AND upper(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN ifnull(AMOUNT, 0) END), 0) + ifnull(sum(CASE WHEN lower(TRANSACTION_TYPE_LBL) = 'account funding' AND upper(MEMO) = 'REPAYMENT' AND upper(FEE) = 'FALSE' AND upper(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN ifnull(AMOUNT, 0) END), 0) + ifnull(sum(CASE WHEN upper(MEMO) = 'SUNG_TUC_FEE' THEN ifnull(AMOUNT, 0) ELSE 0 END), 0) ) * -1 AS ACTUAL_VOLUME FROM CHIKH_CASH_CARD_OBSERVATION_PERIOD_TRACKING A LEFT JOIN chikh_application_tracking_v3 C ON A.LOS_CONTRACT_NO = C.CONTRACT_NUMBER WHERE last_day(ifnull(TRANSACTION_DATE_AUTH, SETTLEMENT_DATE_TIME)) >= last_day(date_sub(current_date(), 1)) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ), EOM AS ( -- First part: New data for the current month SELECT A.DISBURSE_MONTH, A.CARD_ACCOUNT_NO, A.LOS_CONTRACT_NO, A.LOS_CIF, A.SALE_CODE, A.ISSUING_DATE, A.ACTIVE_DATE, A.OBSERVATION_DATE, A.XSELL_CARD, A.SCHEME_PRODUCT, A.ACTUAL_VOLUME, CASE WHEN B.CARD_ACCOUNT_NO IS NULL THEN 1 ELSE 2 END AS ORD_MONTH FROM NEW_MONTH A LEFT JOIN PTKD.CHIKH_CARD_TRANSACTION_EOM B ON A.CARD_ACCOUNT_NO = B.CARD_ACCOUNT_NO UNION ALL -- Second part: Historical data from the EOM table SELECT DISBURSE_MONTH, CARD_ACCOUNT_NO, LOS_CONTRACT_NO, LOS_CIF, SALE_CODE, ISSUING_DATE, ACTIVE_DATE, OBSERVATION_DATE, XSELL_CARD, SCHEME_PRODUCT, ACTUAL_VOLUME, ORD_MONTH FROM PTKD.CHIKH_CARD_TRANSACTION_EOM ), LOAN AS ( SELECT CARD_ACCOUNT_NO, min(DISBURSE_MONTH) AS FIRST_LOAN FROM EOM WHERE ACTUAL_VOLUME > 0 GROUP BY CARD_ACCOUNT_NO ), TOTAL_CARD AS ( SELECT CARD_ACCOUNT_NO, count(ORD_MONTH) AS ELI_MONTH, sum(ACTUAL_VOLUME) AS FINAL_VOLUME FROM EOM GROUP BY CARD_ACCOUNT_NO ) SELECT a.DISBURSE_MONTH, a.CARD_ACCOUNT_NO, a.LOS_CONTRACT_NO, a.LOS_CIF, a.SALE_CODE, a.ISSUING_DATE, a.ACTIVE_DATE, a.OBSERVATION_DATE, a.ACTUAL_VOLUME, a.ORD_MONTH, a.XSELL_CARD, a.SCHEME_PRODUCT, b.FIRST_LOAN, CASE WHEN b.FIRST_LOAN = a.DISBURSE_MONTH THEN 1 WHEN b.FIRST_LOAN <> a.DISBURSE_MONTH AND c.FINAL_VOLUME <= 0 THEN -1 ELSE 0 END AS LOAN, CASE WHEN c.FINAL_VOLUME < 0 THEN CASE WHEN date_trunc('month', a.OBSERVATION_DATE) = date_trunc('month', a.ACTIVE_DATE) THEN 0 WHEN date_trunc('month', a.OBSERVATION_DATE) <> date_trunc('month', a.ACTIVE_DATE) THEN CASE WHEN a.ORD_MONTH = 1 AND a.ACTUAL_VOLUME < 0 THEN 0 WHEN a.ORD_MONTH = 2 THEN a.ACTUAL_VOLUME - c.FINAL_VOLUME ELSE a.ACTUAL_VOLUME END ELSE a.ACTUAL_VOLUME END ELSE a.ACTUAL_VOLUME END AS VOLUME_ADJUST FROM EOM a LEFT JOIN LOAN b ON a.CARD_ACCOUNT_NO = b.CARD_ACCOUNT_NO LEFT JOIN TOTAL_CARD c ON a.CARD_ACCOUNT_NO = c.CARD_ACCOUNT_NO
WHERE a.DISBURSE_MONTH = last_day(date_sub(current_date(), 1)); ``` Key Changes Summary: 1. Date Truncation: Replaced Oracle's 
trunc(date, 'MM')
with StarRocks' 
date_trunc('month', date)
. 2. Date Arithmetic: Replaced 
date + 7
with 
date_add(date, INTERVAL 7 DAY)
. 3. Null Handling: Replaced 
NVL
with 
ifnull
(or 
coalesce
). 4. Current Date: Replaced 
SYSDATE - 1
with 
date_sub(current_date(), 1)
. 5. Group By: StarRocks allows 
GROUP BY
using column positions (1, 2, 3...) which is helpful for long expressions like your 
DISBURSE_MONTH
logic. 6. Aggregate Handling: StarRocks is stricter about 
SUM
results. I've wrapped the 
SUM(CASE...)
blocks in 
ifnull(..., 0)
to ensure math operations don't return 
NULL
. References • sql-reference/sql-functions/date-time-functions/date_trunc.md • sql-reference/sql-functions/date-time-functions/date_add.md • sql-reference/sql-functions/conditional-functions/ifnull.md • sql-reference/sql-functions/date-time-functions/last_day.md
l
@Rocky to starrocks sql: WITH A AS ( SELECT A.DISBURSEMENT_DATE, SUBSTR(ID_CARD_NUMBER, LENGTH(ID_CARD_NUMBER) - 4, 5) AS ID, CUSTOMER_CODE, PTKD.fn_bo_dau(UPPER(a.CUSTOMER_NAME)) AS NAME FROM DMAPP.DTM_CUS_APPLICATIONS a WHERE DISBURSEMENT_DATE IS NOT null UNION ALL SELECT trunc(ISSUING_DATE), SUBSTR(ID_CARD_NUMBER, LENGTH(ID_CARD_NUMBER) - 4, 5) AS ID, CUSTOMER_CODE, PTKD.fn_bo_dau(UPPER(b.CUSTOMER_NAME)) AS NAME FROM CHIKH_CARD_ACCOUNT_INFOR A LEFT JOIN DMAPP.DTM_CUS_APPLICATIONS B ON A.LOS_CONTRACT_NO = B.CONTRACT_NUMBER ) , B AS ( SELECT DISTINCT SUBSTR(ID_CARD_NUMBER, LENGTH(ID_CARD_NUMBER) - 4, 5) AS ID, SUBSTR(ID_CARD_NUMBER_OTHER, LENGTH(ID_CARD_NUMBER_OTHER) - 4, 5) AS ID_OLD, PTKD.fn_bo_dau(UPPER(CUSTOMER_NAME)) AS NAME FROM DMAPP.DTM_CUS_APPLICATIONS --WHERE SUBSTR(ID_CARD_NUMBER_OTHER, LENGTH(ID_CARD_NUMBER_OTHER) - 4, 5) <> '*****' ) , MIN_APP AS ( SELECT SUBSTR(ID_CARD_NUMBER, LENGTH(ID_CARD_NUMBER) - 4, 5) AS ID, SUBSTR(ID_CARD_NUMBER_OTHER, LENGTH(ID_CARD_NUMBER_OTHER) - 4, 5) AS ID_OLD, PTKD.fn_bo_dau(UPPER(b.CUSTOMER_NAME)) AS NAME, MIN(SALE_SUBMIT_DATE) AS FIRST_SUBMISSION FROM DMAPP.DTM_CUS_APPLICATIONS B WHERE ID_CARD_NUMBER IS NOT NULL AND CUSTOMER_NAME IS NOT NULL GROUP BY SUBSTR(ID_CARD_NUMBER, LENGTH(ID_CARD_NUMBER) - 4, 5), PTKD.fn_bo_dau(UPPER(b.CUSTOMER_NAME)), SUBSTR(ID_CARD_NUMBER_OTHER, LENGTH(ID_CARD_NUMBER_OTHER) - 4, 5) ) --, MIN_INSURANCE AS -- --( -- SELECT -- SUBSTR(TRIM(ID), LENGTH(TRIM(ID)) - 4, 5) AS ID_CONVERT, -- PTKD.fn_bo_dau(UPPER(trim(NAME))) AS NAME_CONVERT, -- to_date(insurance_date ,'dd-mm-yyyy') AS insurred_date -- FROM insurance_cus -- WHERE to_date(insurance_month ,'mm-dd-yyyy') < '01-OCT-2024' -- -- UNION ALL -- -- SELECT -- SUBSTR(TRIM(CUSTOMER_ID), LENGTH(TRIM(CUSTOMER_ID)) - 4, 5) AS ID_CONVERT, -- PTKD.fn_bo_dau(UPPER(trim(CUSTOMER_NAME))) AS NAME_CONVERT, -- TO_DATE(INSURANCE_DATE, 'DD-MM-YYYY') AS insurred_date -- FROM INSURANCE_DETAIL_IMPORT -- WHERE TO_DATE(REPORT_MONTH, 'DD-MM-YYYY') < '01-OCT-2024' -- -- ) , FIRST_TXN AS ( SELECT ID, NAME, MIN(DISBURSEMENT_DATE) AS first_txn FROM A GROUP BY ID, NAME ) , FIRST_TXN_APP AS ( SELECT ID, NAME, min(FIRST_SUBMISSION) AS FIRST_SUBMISSION FROM MIN_aPP GROUP BY id, NAME ) , FINAL_LIST_V1 AS ( SELECT DISTINCT b.*, CASE WHEN c.first_txn IS NULL THEN d.first_txn WHEN d.first_txn IS NULL THEN c.first_txn WHEN c.first_txn < d.first_txn THEN c.first_txn WHEN c.first_txn >= d.first_txn THEN d.first_txn END AS FIRST_TXN FROM b LEFT JOIN FIRST_TXN c ON b.id = c.id AND b.name = c.name LEFT JOIN FIRST_TXN d ON b.id_old = d.id AND b.name = d.name --WHERE c.first_txn IS NOT NULL AND d.first_txn IS NOT null ) , FINAL_LIST_V2 AS ( SELECT DISTINCT ID, NAME, FIRST_TXN FROM FINAL_LIST_V1 UNION ALL SELECT DISTINCT ID_OLD, NAME, FIRST_TXN FROM FINAL_LIST_V1 WHERE id_old <> '*****' AND ID_OLD <> ID ) , FINAL_LIST AS ( SELECT ID, NAME, min(FIRST_TXN) AS FIRST_TXN FROM FINAL_LIST_V2 GROUP BY ID, NAME ) , FINAL_LIST_APP AS ( SELECT ID, NAME, MIN(FIRST_SUBMISSION) AS FIRST_SUBMISSION FROM FIRST_TXN_APP GROUP BY ID, NAME ) , RAW_INSURANCE AS ( SELECT REPORT_MONTH AS REPORT_MONTH, INSURANCE_TYPE , CASE WHEN INSTR(INSURANCE_DATE,' ') > 0 THEN TO_DATE(SUBSTR(INSURANCE_DATE,1,INSTR(INSURANCE_DATE,' ') - 1), 'DD-MM-YYYY') ELSE TO_DATE(INSURANCE_DATE, 'DD-MM-YYYY') END AS INSURANCE_DATE, INSURANCE_ID , SUBSTR(TRIM(CUSTOMER_ID), LENGTH(TRIM(CUSTOMER_ID)) - 4, 5) AS INS_ID, CUSTOMER_NAME , PTKD.fn_bo_dau(UPPER(trim(CUSTOMER_NAME))) AS INS_NAME, SUBSTR(CUSTOMER_ID,2 ,LENGTH(CUSTOMER_ID)) AS CUSTOMER_ID, VOLUME, SALE_CODE FROM INSURANCE_DETAIL_IMPORT ) SELECT TO_DATE(REPORT_MONTH, 'YYYY-MM-DD') AS REPORT_MONTH, INSURANCE_TYPE , INSURANCE_DATE, INSURANCE_ID , CUSTOMER_NAME , CUSTOMER_ID, VOLUME, SALE_CODE, CASE WHEN A.CUSTOMER_ID IS NULL or A.CUSTOMER_NAME IS null THEN 'Cant define' WHEN B.FIRST_TXN IS NULL OR FIRST_TXN > INSURANCE_DATE THEN 'New' else 'Old' END AS CUS_CLASSIFICATION, B.FIRST_TXN, CASE WHEN A.CUSTOMER_ID IS NULL or A.CUSTOMER_NAME IS null THEN 'Cant define' WHEN FIRST_SUBMISSION IS NULL OR FIRST_SUBMISSION > INSURANCE_DATE THEN 'New' else 'Old' END AS CUS_CLASSIFICATION_APP, C.FIRST_SUBMISSION AS FIRST_SUBMISSION_APP FROM RAW_INSURANCE A LEFT JOIN FINAL_LIST B ON TRIM(B.ID) = INS_ID AND B.NAME = INS_NAME LEFT JOIN FINAL_LIST_APP C ON TRIM(C.ID) = INS_ID AND C.NAME = INS_NAME ;
r
Converting this Oracle query to StarRocks requires updating date parsing functions, string manipulation, and handling the custom Vietnamese accent removal function. StarRocks SQL Translation ```sql WITH A AS ( SELECT A.DISBURSEMENT_DATE, RIGHT(ID_CARD_NUMBER, 5) AS ID, -- Simplified SUBSTR for last 5 chars CUSTOMER_CODE, PTKD_fn_bo_dau(UPPER(a.CUSTOMER_NAME)) AS NAME -- Assumes UDF is ported to StarRocks FROM DMAPP.DTM_CUS_APPLICATIONS a WHERE DISBURSEMENT_DATE IS NOT NULL UNION ALL SELECT CAST(ISSUING_DATE AS DATE) AS ISSUING_DATE, -- StarRocks equivalent of TRUNC RIGHT(ID_CARD_NUMBER, 5) AS ID, CUSTOMER_CODE, PTKD_fn_bo_dau(UPPER(b.CUSTOMER_NAME)) AS NAME FROM CHIKH_CARD_ACCOUNT_INFOR A LEFT JOIN DMAPP.DTM_CUS_APPLICATIONS B ON A.LOS_CONTRACT_NO = B.CONTRACT_NUMBER ), B AS ( SELECT DISTINCT RIGHT(ID_CARD_NUMBER, 5) AS ID, RIGHT(ID_CARD_NUMBER_OTHER, 5) AS ID_OLD, PTKD_fn_bo_dau(UPPER(CUSTOMER_NAME)) AS NAME FROM DMAPP.DTM_CUS_APPLICATIONS ), MIN_APP AS ( SELECT RIGHT(ID_CARD_NUMBER, 5) AS ID, RIGHT(ID_CARD_NUMBER_OTHER, 5) AS ID_OLD, PTKD_fn_bo_dau(UPPER(b.CUSTOMER_NAME)) AS NAME, MIN(SALE_SUBMIT_DATE) AS FIRST_SUBMISSION FROM DMAPP.DTM_CUS_APPLICATIONS B WHERE ID_CARD_NUMBER IS NOT NULL AND CUSTOMER_NAME IS NOT NULL GROUP BY 1, 2, 3 ), FIRST_TXN AS ( SELECT ID, NAME, MIN(DISBURSEMENT_DATE) AS first_txn FROM A GROUP BY ID, NAME ), FIRST_TXN_APP AS ( SELECT ID, NAME, MIN(FIRST_SUBMISSION) AS FIRST_SUBMISSION FROM MIN_APP GROUP BY ID, NAME ), FINAL_LIST_V1 AS ( SELECT DISTINCT b.*, CASE WHEN c.first_txn IS NULL THEN d.first_txn WHEN d.first_txn IS NULL THEN c.first_txn WHEN c.first_txn < d.first_txn THEN c.first_txn ELSE d.first_txn END AS FIRST_TXN FROM b LEFT JOIN FIRST_TXN c ON b.id = c.id AND b.name = c.name LEFT JOIN FIRST_TXN d ON b.id_old = d.id AND b.name = d.name ), FINAL_LIST_V2 AS ( SELECT DISTINCT ID, NAME, FIRST_TXN FROM FINAL_LIST_V1 UNION ALL SELECT DISTINCT ID_OLD, NAME, FIRST_TXN FROM FINAL_LIST_V1 WHERE ID_OLD <> '*****' AND ID_OLD <> ID ), FINAL_LIST AS ( SELECT ID, NAME, MIN(FIRST_TXN) AS FIRST_TXN FROM FINAL_LIST_V2 GROUP BY ID, NAME ), FINAL_LIST_APP AS ( SELECT ID, NAME, MIN(FIRST_SUBMISSION) AS FIRST_SUBMISSION FROM FIRST_TXN_APP GROUP BY ID, NAME ), RAW_INSURANCE AS ( SELECT REPORT_MONTH, INSURANCE_TYPE, CASE WHEN instr(INSURANCE_DATE, ' ') > 0 THEN str_to_date(substr(INSURANCE_DATE, 1, instr(INSURANCE_DATE, ' ') - 1), '%d-%m-%Y') ELSE str_to_date(INSURANCE_DATE, '%d-%m-%Y') END AS INSURANCE_DATE, INSURANCE_ID, RIGHT(TRIM(CUSTOMER_ID), 5) AS INS_ID, CUSTOMER_NAME, PTKD_fn_bo_dau(UPPER(trim(CUSTOMER_NAME))) AS INS_NAME, SUBSTR(CUSTOMER_ID, 2) AS CUSTOMER_ID_CLEAN, VOLUME, SALE_CODE FROM INSURANCE_DETAIL_IMPORT ) SELECT str_to_date(A.REPORT_MONTH, '%Y-%m-%d') AS REPORT_MONTH, A.INSURANCE_TYPE, A.INSURANCE_DATE, A.INSURANCE_ID, A.CUSTOMER_NAME, A.CUSTOMER_ID_CLEAN AS CUSTOMER_ID, A.VOLUME, A.SALE_CODE, CASE WHEN A.CUSTOMER_ID_CLEAN IS NULL OR A.CUSTOMER_NAME IS NULL THEN 'Cant define' WHEN B.FIRST_TXN IS NULL OR B.FIRST_TXN > A.INSURANCE_DATE THEN 'New' ELSE 'Old' END AS CUS_CLASSIFICATION, B.FIRST_TXN, CASE WHEN A.CUSTOMER_ID_CLEAN IS NULL OR A.CUSTOMER_NAME IS NULL THEN 'Cant define' WHEN C.FIRST_SUBMISSION IS NULL OR C.FIRST_SUBMISSION > A.INSURANCE_DATE THEN 'New' ELSE 'Old' END AS CUS_CLASSIFICATION_APP, C.FIRST_SUBMISSION AS FIRST_SUBMISSION_APP FROM RAW_INSURANCE A
LEFT JOIN FINAL_LIST B ON trim(B.ID) = A.INS_ID AND B.NAME = A.INS_NAME LEFT JOIN FINAL_LIST_APP C ON trim(C.ID) = A.INS_ID AND C.NAME = A.INS_NAME; ``` Key Mapping & Changes: 1. Custom Function (
fn_bo_dau
): StarRocks does not have a built-in "remove accents" function. You will need to create a Java UDF or WASM UDF in StarRocks to replicate the logic of 
PTKD.fn_bo_dau
. I have used 
PTKD_fn_bo_dau
as a placeholder. 2. Date Conversion: * Oracle's 
TO_DATE(str, 'DD-MM-YYYY')
is replaced by StarRocks' 
str_to_date(str, '%d-%m-%Y')
. * Note that StarRocks uses Python-style format strings (e.g., 
%d
, 
%m
, 
%Y
). 3. String Manipulation: * 
SUBSTR(col, LENGTH(col) - 4, 5)
is simplified to 
RIGHT(col, 5)
. * 
SUBSTR(CUSTOMER_ID, 2, LENGTH(CUSTOMER_ID))
is simplified to 
SUBSTR(CUSTOMER_ID, 2)
. 4. 
TRUNC(date)
: Replaced with 
CAST(date AS DATE)
or 
date_trunc('day', date)
. 5. 
INSTR
: StarRocks supports 
instr(string, substring)
natively. 6. 
GROUP BY
: In StarRocks, you can use column aliases or ordinal positions (e.g., 
GROUP BY 1, 2, 3
) for cleaner`) in CTEs. Reference Links: • StarRocks str_to_date • StarRocks RIGHT function • StarRocks User Defined Functions (UDF)
l
@Rocky CREATE TABLE "PTKD"."CHIKH_CARD_ACCOUNT_INFOR" ( "CREATE_DATE_TIME" DATE, "CARD_ACCOUNT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LOS_CIF" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LOS_CONTRACT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "E6_CUST_ID" NUMBER, "ORDER_ID" NUMBER, "CARD_PROGRAM" VARCHAR2(255) COLLATE "USING_NLS_COMP", "CARD_PROFILE_NAME" VARCHAR2(255) COLLATE "USING_NLS_COMP", "MAIN_CARD_NUMBER" VARCHAR2(255) COLLATE "USING_NLS_COMP", "MAIN_PAN_FIRST6" VARCHAR2(255) COLLATE "USING_NLS_COMP", "MAIN_PAN_LAST4" VARCHAR2(255) COLLATE "USING_NLS_COMP", "MAIN_EMBOSSING_NAME" VARCHAR2(255) COLLATE "USING_NLS_COMP", "SUB_CARD_NUMBER_1" VARCHAR2(100) COLLATE "USING_NLS_COMP", "SUB_PAN_FIRST6_1" VARCHAR2(100) COLLATE "USING_NLS_COMP", "SUB_PAN_LAST4_1" VARCHAR2(100) COLLATE "USING_NLS_COMP", "SUB_EMBOSSING_NAME_1" VARCHAR2(100) COLLATE "USING_NLS_COMP", "SUB_CARD_NUMBER_2" VARCHAR2(100) COLLATE "USING_NLS_COMP", "SUB_PAN_FIRST6_2" VARCHAR2(100) COLLATE "USING_NLS_COMP", "SUB_PAN_LAST4_2" VARCHAR2(100) COLLATE "USING_NLS_COMP", "SUB_EMBOSSING_NAME_2" VARCHAR2(100) COLLATE "USING_NLS_COMP", "BILLING_DAY_OF_MONTH" VARCHAR2(255) COLLATE "USING_NLS_COMP", "ISSUING_DATE" DATE, "ACTIVE_DATE" DATE, "EXPIRY" VARCHAR2(255) COLLATE "USING_NLS_COMP", "EXPIRY_DATE" DATE, "CLOSE_DATE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "E6_STATUS" VARCHAR2(100) COLLATE "USING_NLS_COMP", "E6_BLOCK_STATUS" VARCHAR2(3) COLLATE "USING_NLS_COMP", "CARD_SERVICE_STATUS" VARCHAR2(100) COLLATE "USING_NLS_COMP", "OVERALL_LIMIT" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LIMIT_DAY" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LIMIT_MONTH" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LIMIT_YEAR" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LIMIT_TRAN" VARCHAR2(255) COLLATE "USING_NLS_COMP", "INTEREST_RATE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "CASH_INTEREST_RATE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "PURCHASE_INTEREST_RATE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "FEE_INTEREST_RATE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "SUB_CARD" VARCHAR2(3) COLLATE "USING_NLS_COMP", "SALE_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", CONSTRAINT "CARD_NO_UNIQUE" UNIQUE ("CARD_ACCOUNT_NO", "LOS_CONTRACT_NO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_PTKD" ENABLE ) to starrocks primary key table CARD_ACCOUNT_NO
r
Here is the StarRocks DDL for the 
CHIKH_CARD_ACCOUNT_INFOR
table using the Primary Key model with 
CARD_ACCOUNT_NO
as the primary key. In StarRocks, primary key columns must be defined as 
NOT NULL
. I have mapped Oracle's 
DATE
to 
DATETIME
to preserve time information and 
NUMBER
to 
BIGINT
for ID-related fields.
Copy code
sql
CREATE TABLE ptkd.chikh_card_account_infor (
    card_account_no        VARCHAR(255) NOT NULL COMMENT "Primary Key",
    create_date_time       DATETIME,
    los_cif                VARCHAR(255),
    los_contract_no        VARCHAR(255),
    e6_cust_id             BIGINT,
    order_id               BIGINT,
    card_program           VARCHAR(255),
    card_profile_name      VARCHAR(255),
    main_card_number       VARCHAR(255),
    main_pan_first6        VARCHAR(255),
    main_pan_last4         VARCHAR(255),
    main_embossing_name    VARCHAR(255),
    sub_card_number_1      VARCHAR(100),
    sub_pan_first6_1       VARCHAR(100),
    sub_pan_last4_1        VARCHAR(100),
    sub_embossing_name_1   VARCHAR(100),
    sub_card_number_2      VARCHAR(100),
    sub_pan_first6_2       VARCHAR(100),
    sub_pan_last4_2        VARCHAR(100),
    sub_embossing_name_2   VARCHAR(100),
    billing_day_of_month   VARCHAR(255),
    issuing_date           DATETIME,
    active_date            DATETIME,
    expiry                 VARCHAR(255),
    expiry_date            DATETIME,
    close_date             VARCHAR(255),
    e6_status              VARCHAR(100),
    e6_block_status        VARCHAR(3),
    card_service_status    VARCHAR(100),
    overall_limit          VARCHAR(255),
    limit_day              VARCHAR(255),
    limit_month            VARCHAR(255),
    limit_year             VARCHAR(255),
    limit_tran             VARCHAR(255),
    interest_rate          VARCHAR(255),
    cash_interest_rate     VARCHAR(20),
    purchase_interest_rate VARCHAR(20),
    fee_interest_rate      VARCHAR(20),
    sub_card               VARCHAR(3),
    sale_code              VARCHAR(50)
)
PRIMARY KEY (card_account_no)
DISTRIBUTED BY HASH(card_account_no)
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"
);
Key Mapping & Design Notes: * Primary Key Requirement: 
card_account_no
is set to 
NOT NULL
as required by the Primary Key model. * Data Types: * Oracle 
DATE
is mapped to StarRocks 
DATETIME
because Oracle's 
DATE
type typically stores both date and time. * Oracle 
NUMBER
(for IDs) is mapped to 
BIGINT
. * Oracle 
VARCHAR2
is mapped to 
VARCHAR
. * Persistent Index: The property 
"enable_persistent_index" = "true"
is enabled. This is highly recommended for Primary Key tables to ensure that the primary key index is stored on disk, reducing memory usage while maintaining high performance. * Bucket Count: By not specifying 
BUCKETS
, StarRocks will automatically determine the number of tablets based on the cluster scale (available in newer versions). References • table_design/table_types/primary_key_table.md • sql-reference/data-types/date-time-types/DATETIME.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
l
@Rocky CREATE TABLE "DMAPP"."DTM_CLZB_AMOUNT_PAID" ( "SYS_RUN_DATE" DATE, "ACCOUNT_NUMBER" VARCHAR2(35) COLLATE "USING_NLS_COMP", "BRANCH_CODE" VARCHAR2(35) COLLATE "USING_NLS_COMP", "COMPONENT_NAME" VARCHAR2(35) COLLATE "USING_NLS_COMP", "DUE_DATE" DATE, "PAID_DATE" DATE, "EVENT_SEQ_NO" NUMBER, "AMOUNT_PAID" NUMBER, "AMOUNT_WAIVED" NUMBER, "AMOUNT_CAPITALIZED" NUMBER, "EXEC_DATE" DATE, "PAID_STATUS" VARCHAR2(1) COLLATE "USING_NLS_COMP", "START_DATE" DATE ) to starrocks duplicate key ACCOUNT_NUMBER table
r
Here is the StarRocks DDL for the 
DTM_CLZB_AMOUNT_PAID
table using the Duplicate Key model. In the Duplicate Key model, StarRocks sorts and indexes data based on the columns specified in the 
DUPLICATE KEY
clause. For best performance, the key column (
ACCOUNT_NUMBER
) should be defined at the beginning of the table.
Copy code
sql
CREATE TABLE dmapp.dtm_clzb_amount_paid (
    account_number      VARCHAR(35) NOT NULL COMMENT "Key column",
    sys_run_date        DATETIME,
    branch_code         VARCHAR(35),
    component_name      VARCHAR(35),
    due_date            DATE,
    paid_date           DATETIME,
    event_seq_no        BIGINT,
    amount_paid         DECIMAL(18, 2),
    amount_waived       DECIMAL(18, 2),
    amount_capitalized  DECIMAL(18, 2),
    exec_date           DATETIME,
    paid_status         VARCHAR(1),
    start_date          DATE
)
DUPLICATE KEY(account_number)
DISTRIBUTED BY HASH(account_number)
PROPERTIES (
    "replication_num" = "3"
);
Key Mapping & Design Notes: * Duplicate Key Model: This model is ideal for raw data or logs where you want to keep every record (even if the 
ACCOUNT_NUMBER
is the same) without merging or overwriting. * Data Types: * Oracle 
NUMBER
(for sequence/ID) is mapped to 
BIGINT
. * Oracle 
NUMBER
(for financial amounts) is mapped to 
DECIMAL(18, 2)
. You can increase precision to 
(38, 4)
if very high precision is required. * Oracle 
DATE
is mapped to 
DATETIME
for timestamp fields and 
DATE
for fields typically used as calendar dates. * Column Order: The 
account_number
is placed first. In StarRocks, the order of columns in the schema determines the sort order on disk for Duplicate Key tables, which significantly speeds up queries filtering by those leading columns. * Distribution: Data is partitioned/distributed across the cluster based on the hash of 
account_number
, ensuring data for the same account is stored on the same node (which optimizes joins and aggregations on that column). References • table_design/table_types/duplicate_key_table.md • sql-reference/data-types/numeric-types/DECIMAL.md • table_design/data_distribution/Data_distribution.md
l
@Rocky how to show detail of create external catalog
r
To view the details of a created external catalog in StarRocks, you can use the following commands: 1. Show Creation Statement (Properties) To see the full definition of the catalog, including the properties (like JDBC URIs, access keys, or metastore locations) used when it was created, use:
Copy code
sql
SHOW CREATE CATALOG <catalog_name>;
2. List All Catalogs To see a list of all catalogs in the cluster and their basic types:
Copy code
sql
SHOW CATALOGS;
3. Describe Catalog You can also view the comment or basic metadata of a catalog using:
Copy code
sql
DESC CATALOG <catalog_name>;
────────── References: * SHOW CREATE CATALOG * SHOW CATALOGS References • sql-reference/sql-statements/Catalog/CREATE_EXTERNAL_CATALOG.md • data_source/catalog/jdbc_catalog.md
l
@Rocky CREATE TABLE "PTKD"."CHIKH_LOAN_EOM" ( "LAST_UPDATE_DATE" DATE, "LD_NO" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LD_DI_KEY" NUMBER, "LD_DI_NO" VARCHAR2(5) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LMS_CIF_NO" VARCHAR2(20) COLLATE "USING_NLS_COMP", "LOS_CIF_NO" VARCHAR2(20) COLLATE "USING_NLS_COMP", "CUSTOMER_NAME" VARCHAR2(250) COLLATE "USING_NLS_COMP", "BRANCH_CODE" VARCHAR2(250) COLLATE "USING_NLS_COMP", "LMS_PROD_GROUP" VARCHAR2(10) COLLATE "USING_NLS_COMP", "LMS_PROD_CODE" VARCHAR2(10) COLLATE "USING_NLS_COMP", "INT_RATE" NUMBER(13,8), "CONTRACT_NO" VARCHAR2(30) COLLATE "USING_NLS_COMP", "APP_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "LD_DI_DATE" DATE, "DISBURSEMENT_DATE" DATE, "SIGNED_DATE" DATE, "MATURITY" DATE NOT NULL ENABLE, "PRODUCT_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "PRODUCT_NAME" VARCHAR2(750) COLLATE "USING_NLS_COMP", "CHANNEL" VARCHAR2(7) COLLATE "USING_NLS_COMP", "LOAN_TERM" NUMBER, "ACCT_NO" VARCHAR2(20) COLLATE "USING_NLS_COMP", "EMI_AMOUNT" NUMBER, "COLLECTION_FEE" NUMBER, "TOTAL_EMI_AMOUNT" NUMBER, "FIRST_PAYMENT_DATE" DATE, "SECOND_PAYMENT_DATE" DATE, "THIRD_PAYMENT_DATE" DATE, "FOURTH_PAYMENT_DATE" DATE, "INSURANCE_FEE" NUMBER, "INSURANCE_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "LOAN_AMOUNT_INSURED" NUMBER, "PARTNER_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "LOAN_PURPOSE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "LOAN_PURPOSE_LBL" VARCHAR2(250) COLLATE "USING_NLS_COMP", "DISBURSEMENT_CHANNEL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "AGENCY_PAYMENT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "MOB3_DATE" DATE, "MOB4_DATE" DATE, "SALE_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SALE_NAME" VARCHAR2(250) COLLATE "USING_NLS_COMP", "LOAN_AMT" NUMBER NOT NULL ENABLE, "CURRENCY" VARCHAR2(3) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "TOTAL_OVERDUE_AMOUNT" NUMBER, "INT_RECEIVED" NUMBER, "OD_RECEIVED" NUMBER, "PRI_RECEIVED" NUMBER, "FEE_RECEIVED" NUMBER, "FEE_COLL_RECEIVED" NUMBER, "LAST_PAYMENT_DATE" DATE, "LAST_PAYMENT_AMOUNT" NUMBER, "PAYMENT_VOL" VARCHAR2(20) COLLATE "USING_NLS_COMP", "CHECK_FPD" VARCHAR2(3) COLLATE "USING_NLS_COMP", "CHECK_SPD" VARCHAR2(3) COLLATE "USING_NLS_COMP", "CHECK_TPD" VARCHAR2(3) COLLATE "USING_NLS_COMP", "OVERDUE_AMOUNT_1FST" NUMBER, "OVERDUE_AMOUNT_2SND" NUMBER, "OVERDUE_AMOUNT_3TRD" NUMBER, "MAX_DPD_1FST" NUMBER, "MAX_DPD_2SND" NUMBER, "MAX_DPD_3TRD" NUMBER, "TOTAL_OVERDUE_PERIOD" NUMBER, "CURRENT_PAYMENT_PERIOD" NUMBER, "NEXT_PAYMENT_PERIOD" NUMBER, "REMAINING_AMOUNT" NUMBER, "FPD10_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "FPD_CASE_10" CHAR(1) COLLATE "USING_NLS_COMP", "FPD30_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "FPD_CASE_30" CHAR(1) COLLATE "USING_NLS_COMP", "SPD10_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "SPD_CASE_10" CHAR(1) COLLATE "USING_NLS_COMP", "SPD30_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "SPD_CASE_30" CHAR(1) COLLATE "USING_NLS_COMP", "TPD10_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "TPD_CASE_10" CHAR(1) COLLATE "USING_NLS_COMP", "TPD30_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "TPD_CASE_30" CHAR(1) COLLATE "USING_NLS_COMP", "MOB3_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "MOB3_CASE" CHAR(1) COLLATE "USING_NLS_COMP", "MOB3_CASE_KPI" CHAR(1) COLLATE "USING_NLS_COMP", "MOB4_CASE_RISK" CHAR(1) COLLATE "USING_NLS_COMP", "MOB4_CASE" CHAR(1) COLLATE "USING_NLS_COMP", "OVER_DUE_DAYS_LD" NUMBER, "NEXT_REPAY_DATE" DATE, "CONTRACT_STATUS" VARCHAR2(20) COLLATE "USING_NLS_COMP" ) to starrocks primary key "LAST_UPDATE_DATE" , "LD_NO"
r
Here is the StarRocks DDL for the 
CHIKH_LOAN_EOM
table using the Primary Key model. In StarRocks, all columns defined in the 
PRIMARY KEY
must be declared as 
NOT NULL
. I have mapped the financial 
NUMBER
fields to 
DECIMAL
for precision and ID/count fields to 
BIGINT
.
Copy code
sql
CREATE TABLE ptkd.chikh_loan_eom (
    last_update_date        DATE NOT NULL,
    ld_no                   VARCHAR(50) NOT NULL,
    ld_di_key               BIGINT,
    ld_di_no                VARCHAR(5) NOT NULL,
    lms_cif_no              VARCHAR(20),
    los_cif_no              VARCHAR(20),
    customer_name           VARCHAR(250),
    branch_code             VARCHAR(250),
    lms_prod_group          VARCHAR(10),
    lms_prod_code           VARCHAR(10),
    int_rate                DECIMAL(13, 8),
    contract_no             VARCHAR(30),
    app_code                VARCHAR(50),
    ld_di_date              DATE,
    disbursement_date       DATE,
    signed_date             DATE,
    maturity                DATE NOT NULL,
    product_code            VARCHAR(20),
    product_name            VARCHAR(750),
    channel                 VARCHAR(7),
    loan_term               BIGINT,
    acct_no                 VARCHAR(20),
    emi_amount              DECIMAL(18, 2),
    collection_fee          DECIMAL(18, 2),
    total_emi_amount        DECIMAL(18, 2),
    first_payment_date      DATE,
    second_payment_date     DATE,
    third_payment_date      DATE,
    fourth_payment_date     DATE,
    insurance_fee           DECIMAL(18, 2),
    insurance_code          VARCHAR(20),
    loan_amount_insured     DECIMAL(18, 2),
    partner_code            VARCHAR(20),
    loan_purpose            VARCHAR(50),
    loan_purpose_lbl        VARCHAR(250),
    disbursement_channel    VARCHAR(50),
    agency_payment          VARCHAR(50),
    mob3_date               DATE,
    mob4_date               DATE,
    sale_code               VARCHAR(50),
    sale_name               VARCHAR(250),
    loan_amt                DECIMAL(18, 2) NOT NULL,
    currency                VARCHAR(3) NOT NULL,
    total_overdue_amount    DECIMAL(18, 2),
    int_received            DECIMAL(18, 2),
    od_received             DECIMAL(18, 2),
    pri_received            DECIMAL(18, 2),
    fee_received            DECIMAL(18, 2),
    fee_coll_received       DECIMAL(18, 2),
    last_payment_date       DATE,
    last_payment_amount     DECIMAL(18, 2),
    payment_vol             VARCHAR(20),
    check_fpd               VARCHAR(3),
    check_spd               VARCHAR(3),
    check_tpd               VARCHAR(3),
    overdue_amount_1fst     DECIMAL(18, 2),
    overdue_amount_2snd     DECIMAL(18, 2),
    overdue_amount_3trd     DECIMAL(18, 2),
    max_dpd_1fst            BIGINT,
    max_dpd_2snd            BIGINT,
    max_dpd_3trd            BIGINT,
    total_overdue_period    BIGINT,
    current_payment_period  BIGINT,
    next_payment_period     BIGINT,
    remaining_amount        DECIMAL(18, 2),
    fpd10_case_risk         CHAR(1),
    fpd_case_10             CHAR(1),
    fpd30_case_risk         CHAR(1),
    fpd_case_30             CHAR(1),
    spd10_case_risk         CHAR(1),
    spd_case_10             CHAR(1),
    spd30_case_risk         CHAR(1),
    spd_case_30             CHAR(1),
    tpd10_case_risk         CHAR(1),
    tpd_case_10             CHAR(1),
    tpd30_case_risk         CHAR(1),
    tpd_case_30             CHAR(1),
    mob3_case_risk          CHAR(1),
    mob3_case               CHAR(1),
    mob3_case_kpi           CHAR(1),
    mob4_case_risk          CHAR(1),
    mob4_case               CHAR(1),
    over_due_days_ld        BIGINT,
    next_repay_date         DATE,
    contract_status         VARCHAR(20)
)
PRIMARY KEY (last_update_date, ld_no)
DISTRIBUTED BY HASH(ld_no)
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"
);
Mapping & Design Notes:
* Composite Primary Key: Both 
last_update_date
and 
ld_no
are included in the Primary Key and are set to 
NOT NULL
. * Financial Precision: Oracle 
NUMBER
fields representing amounts are mapped to 
DECIMAL(18, 2)
(supporting 18 digits total, 2 decimal places). 
INT_RATE
uses 
DECIMAL(13, 8)
to match your specific Oracle precision. * Persistent Index: Enabled to optimize primary key lookups and memory efficiency. * Distribution: I chose 
ld_no
for distribution as it likely provides better cardinality than a date for even data balancing across the cluster. References • table_design/table_types/primary_key_table.md • sql-reference/data-types/numeric-types/DECIMAL.md
l
@Rocky oracle date have time, so to starrocks should be dateime
r
You are correct. Since Oracle's 
DATE
type includes hours, minutes, and seconds, mapping it to StarRocks 
DATETIME
is the best practice to avoid losing time-level precision. Here is the updated DDL for the Primary Key table with all date fields converted to `DATETIME`:
Copy code
sql
CREATE TABLE ptkd.chikh_loan_eom (
    last_update_date        DATETIME NOT NULL COMMENT "Primary Key component",
    ld_no                   VARCHAR(50) NOT NULL COMMENT "Primary Key component",
    ld_di_key               BIGINT,
    ld_di_no                VARCHAR(5) NOT NULL,
    lms_cif_no              VARCHAR(20),
    los_cif_no              VARCHAR(20),
    customer_name           VARCHAR(250),
    branch_code             VARCHAR(250),
    lms_prod_group          VARCHAR(10),
    lms_prod_code           VARCHAR(10),
    int_rate                DECIMAL(13, 8),
    contract_no             VARCHAR(30),
    app_code                VARCHAR(50),
    ld_di_date              DATETIME,
    disbursement_date       DATETIME,
    signed_date             DATETIME,
    maturity                DATETIME NOT NULL,
    product_code            VARCHAR(20),
    product_name            VARCHAR(750),
    channel                 VARCHAR(7),
    loan_term               BIGINT,
    acct_no                 VARCHAR(20),
    emi_amount              DECIMAL(18, 2),
    collection_fee          DECIMAL(18, 2),
    total_emi_amount        DECIMAL(18, 2),
    first_payment_date      DATETIME,
    second_payment_date     DATETIME,
    third_payment_date      DATETIME,
    fourth_payment_date     DATETIME,
    insurance_fee           DECIMAL(18, 2),
    insurance_code          VARCHAR(20),
    loan_amount_insured     DECIMAL(18, 2),
    partner_code            VARCHAR(20),
    loan_purpose            VARCHAR(50),
    loan_purpose_lbl        VARCHAR(250),
    disbursement_channel    VARCHAR(50),
    agency_payment          VARCHAR(50),
    mob3_date               DATETIME,
    mob4_date               DATETIME,
    sale_code               VARCHAR(50),
    sale_name               VARCHAR(250),
    loan_amt                DECIMAL(18, 2) NOT NULL,
    currency                VARCHAR(3) NOT NULL,
    total_overdue_amount    DECIMAL(18, 2),
    int_received            DECIMAL(18, 2),
    od_received             DECIMAL(18, 2),
    pri_received            DECIMAL(18, 2),
    fee_received            DECIMAL(18, 2),
    fee_coll_received       DECIMAL(18, 2),
    last_payment_date       DATETIME,
    last_payment_amount     DECIMAL(18, 2),
    payment_vol             VARCHAR(20),
    check_fpd               VARCHAR(3),
    check_spd               VARCHAR(3),
    check_tpd               VARCHAR(3),
    overdue_amount_1fst     DECIMAL(18, 2),
    overdue_amount_2snd     DECIMAL(18, 2),
    overdue_amount_3trd     DECIMAL(18, 2),
    max_dpd_1fst            BIGINT,
    max_dpd_2snd            BIGINT,
    max_dpd_3trd            BIGINT,
    total_overdue_period    BIGINT,
    current_payment_period  BIGINT,
    next_payment_period     BIGINT,
    remaining_amount        DECIMAL(18, 2),
    fpd10_case_risk         CHAR(1),
    fpd_case_10             CHAR(1),
    fpd30_case_risk         CHAR(1),
    fpd_case_30             CHAR(1),
    spd10_case_risk         CHAR(1),
    spd_case_10             CHAR(1),
    spd30_case_risk         CHAR(1),
    spd_case_30             CHAR(1),
    tpd10_case_risk         CHAR(1),
    tpd_case_10             CHAR(1),
    tpd30_case_risk         CHAR(1),
    tpd_case_30             CHAR(1),
    mob3_case_risk          CHAR(1),
    mob3_case               CHAR(1),
    mob3_case_kpi           CHAR(1),
    mob4_case_risk          CHAR(1),
    mob4_case               CHAR(1),
    over_due_days_ld        BIGINT,
    next_repay_date         DATETIME,
    contract_status         VARCHAR(20)
)
PRIMARY KEY (last_update_date, ld_no)
DISTRIBUTED BY HASH(ld_no)
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"
);
Key Differences in this Version: * Precision: Every Oracle 
DATE
field is now 
DATETIME
(which stores 
YYYY-MM-DD HH:MM:SS
), preserving the time data from the source. * Primary Key Model: 
last_update_date
and 
ld_no
together uniquely identify a record. If a record with the same key is loaded again, StarRocks will automatically perform an Upsert (update the existing row). References • sql-reference/data-types/date-and-time/DATETIME.md • table_design/table_types/primary_key_table.md References • unloading/Spark_connector.md • data_source/External_table.md • data_source/catalog/maxcompute_catalog.md • data_source/catalog/paimon_catalog.md • table_design/data_distribution/dynamic_partitioning.md • table_design/table_types/primary_key_table.md
l
@Rocky CREATE TABLE IF NOT EXISTS long_term_store ( 'namespace' VARCHAR(255) NOT NULL, 'key' VARCHAR(255) NOT NULL, 'value' JSON NOT NULL, 'updated_at' DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=OLAP PRIMARY *KEY*('namespace', 'key') DISTRIBUTED BY HASH(
namespace
) BUCKETS 4 PROPERTIES ( "replication_num" = "2" ); fix my sql
r
To fix your SQL statement for StarRocks, you need to change the single quotes (
'
) to backticks (`` ` ``) for identifiers and remove the 
ON UPDATE CURRENT_TIMESTAMP
clause, as StarRocks does not currently support automatic updates for timestamps on record modification. Fixed SQL
Copy code
sql
CREATE TABLE IF NOT EXISTS long_term_store (
    `namespace`  VARCHAR(255) NOT NULL,
    `key`        VARCHAR(255) NOT NULL,
    `value`      JSON NOT NULL,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=OLAP
PRIMARY KEY(`namespace`, `key`)
DISTRIBUTED BY HASH(`namespace`) BUCKETS 4
PROPERTIES (
    "replication_num" = "2",
    "enable_persistent_index" = "true"
);
Key Changes & Improvements: 1. Backticks instead of Single Quotes: In SQL, single quotes (
'key'
) are for string literals. For column names (identifiers), use backticks (`` 
key
``) or no quotes at all. This is especially important for reserved keywords like 
key
and 
value
. 2. Removed 
ON UPDATE
: StarRocks supports 
DEFAULT CURRENT_TIMESTAMP
for the initial insert, but it does not support the 
ON UPDATE
trigger. You must manually update the 
updated_at
column in your 
INSERT
(UPSERT) statements using the 
now()
function. 3. Persistent Index: Added 
"enable_persistent_index" = "true"
. For Primary Key tables, this is highly recommended as it keeps the primary key index on disk (optimized for SSDs), preventing high memory consumption as your data grows. 4. JSON Support: Your use of the 
JSON
type in a Primary Key table is correct (available since version 2.5+). Note on Replication If you are running a single-node test cluster, you may need to set 
"replication_num" = "1"
. For a standard production cluster, the recommended value is 
"3"
. References • sql-reference/sql-functions/date-and-time-functions/current_timestamp.md • table_design/table_types/primary_key_table.md • sql-reference/data-types/semi_structured/JSON.md References • sql-reference/data-types/semi_structured/JSON.md • table_design/table_types/table_capabilities.md • table_design/table_types/primary_key_table.md
l
@Rocky how to revoke role of an user
r
To revoke a role from a user in StarRocks, use the 
REVOKE
statement. Basic Syntax
Copy code
sql
REVOKE <role_name> FROM USER <user_identity>;
Examples * Revoke a single role:
Copy code
sql
    REVOKE analyst_role FROM USER 'jack'@'%';
* Revoke multiple roles at once:
Copy code
sql
    REVOKE role1, role2 FROM USER 'jack'@'192.168.1.1';
Important Notes: 1. Permissions: Only users with the 
user_admin
role (or the 
root
user) have the privilege to revoke roles from other users. 2. User Identity: The user identity must match exactly as it appears in StarRocks (e.g., 
'username'@'host'
). You can find this by running 
SHOW USERS;
. 3. Default Role: If you revoke a role that was set as the user's default role, the user may lose those permissions upon their next login unless another default role is assigned. 4. PUBLIC Role: You cannot revoke the system-defined 
PUBLIC
role from any user. References • sql-reference/sql-statements/account-management/REVOKE.md • administration/user_privs/authorization/User_privilege.md References • sql-reference/sql-statements/account-management/REVOKE.md • administration/user_privs/authorization/User_privilege.md
l
@Rocky SELECT LAST_DAY(BONUS_MONTH) AS BONUS_MONTH , P.SALE_NAME , P.SALE_CODE , SUB_REGION , CONTRACT_DATE , MONTHS_BETWEEN(LAST_DAY(ADD_MONTHS(TO_DATE(BONUS_MONTH),-1)),LAST_DAY(CONTRACT_DATE))+1 AS VINTAGE , P.CONTRACT_TYPE rewrite this select in oracle to starrocks
r
To rewrite your Oracle query for StarRocks, you can use the 
months_diff
function as an equivalent to 
MONTHS_BETWEEN
. Since your logic applies 
LAST_DAY
to both inputs, the result will be an integer, which matches StarRocks' 
months_diff
behavior. StarRocks Version
Copy code
sql
SELECT 
    LAST_DAY(BONUS_MONTH) AS BONUS_MONTH
    , P.SALE_NAME
    , P.SALE_CODE
    , SUB_REGION
    , CONTRACT_DATE
    , months_diff(LAST_DAY(ADD_MONTHS(TO_DATE(BONUS_MONTH), -1)), LAST_DAY(CONTRACT_DATE)) + 1 AS VINTAGE
    , P.CONTRACT_TYPE
FROM your_table_name P;
Key Mapping Changes: 1. 
MONTHS_BETWEEN(newer, older)
→ 
months_diff(newer, older)
: In StarRocks, 
months_diff(expr1, expr2)
calculates 
expr1 - expr2
and returns a 
BIGINT
. 2. 
LAST_DAY(date)
: This works exactly the same in StarRocks (available from version 3.1 onwards). It returns the last day of the month for the given date. 3. 
ADD_MONTHS(date, n)
: StarRocks supports this function natively. It behaves similarly to Oracle, though it returns a 
DATETIME
(you can wrap it in 
TO_DATE()
if you only need the date part). 4. 
TO_DATE(string)
: StarRocks' 
TO_DATE
converts a string (in 
YYYY-MM-DD
format) or a 
DATETIME
to a 
DATE
. Important Note on `months_diff`: Oracle's 
MONTHS_BETWEEN
can return a decimal (e.g., 
1.5
) if the days of the month don't match. StarRocks' 
months_diff
always returns a whole number (BIGINT). However, because your query uses 
LAST_DAY
on both dates, they are both aligned to the end of the month, meaning the Oracle result would have been a whole number anyway. This translation is therefore accurate for your specific "Vintage" calculation. References • sql-reference/sql-functions/date-time-functions/months_diff.md • sql-reference/sql-functions/date-time-functions/last_day.md • sql-reference/sql-functions/date-time-functions/add_months.md References • sql-reference/sql-functions/date-time-functions/add_months.md • sql-reference/sql-functions/date-time-functions/months_add.md • sql-reference/sql-functions/date-time-functions/last_day.md • sql-reference/sql-functions/date-time-functions/date_add.md • sql-reference/sql-functions/date-time-functions/months_diff.md • sql-reference/sql-functions/date-time-functions/adddate.md • sql-reference/sql-functions/date-time-functions/months_sub.md • sql-reference/sql-functions/date-time-functions/days_add.md
7 Views
Open in Slack
PreviousNext
Powered by