@Rocky hello

Hello! I'm Rocky. How can I help you with StarRocks today?

@Rocky select a.APP_CODE , a.CREATED_DATE , a.LAST_UPDATED_DATE , a.SCORE , b.SCORE_STAGE , b.PHONE_NUMBER , b.RESPOND_TIME from losrep.partner_score a, losrep.log_score b where a.APP_CODE = b.APP_CODE and a.LAST_UPDATED_DATE > NOW() - 12HOUR(); correct?

Your query is almost correct, but there is a syntax issue with how the time interval is handled. In StarRocks, you cannot use

12HOUR()

as a shorthand or function. Instead, you should use the

INTERVAL

keyword. Corrected Query

sql
SELECT
    a.APP_CODE,
    a.CREATED_DATE,
    a.LAST_UPDATED_DATE,
    a.SCORE,
    b.SCORE_STAGE,
    b.PHONE_NUMBER,
    b.RESPOND_TIME
FROM losrep.partner_score a
JOIN losrep.log_score b ON a.APP_CODE = b.APP_CODE
WHERE a.LAST_UPDATED_DATE > NOW() - INTERVAL 12 HOUR;

Key Changes: 1. Interval Syntax: Changed

NOW() - 12HOUR()

to

NOW() - INTERVAL 12 HOUR

. 2. Function Alternative: You can also use the

DATE_SUB

function:

WHERE a.LAST_UPDATED_DATE > DATE_SUB(NOW(), INTERVAL 12 HOUR)

3. Join Syntax (Optional): While your comma-separated join works, using an explicit

JOIN ... ON

is generally considered better practice for readability. Note on Performance: If

LAST_UPDATED_DATE

is a string (VARCHAR) instead of a DATETIME type, you should wrap it in

STR_TO_DATE()

or cast it to ensure the comparison is efficient and accurate. If

a.LAST_UPDATED_DATE

is indexed, StarRocks can use that index acceleration for this range filter. References • sql-reference/sql-functions/date-time-functions/date_add.md • sql-reference/sql-functions/date-time-functions/timestampadd.md • sql-reference/sql-functions/date-time-functions/date_sub.md • sql-reference/sql-functions/date-time-functions/seconds_add.md • sql-reference/sql-functions/date-time-functions/date_diff.md • sql-reference/sql-functions/date-time-functions/years_add.md • sql-reference/sql-functions/date-time-functions/microseconds_add.md • sql-reference/sql-functions/date-time-functions/date_slice.md

@Rocky SELECT a.APP_CODE, a.CREATED_DATE, a.LAST_UPDATED_DATE, a.SCORE, b.SCORE_STAGE, case when b.PHONE_NUMBER is not null then b.SCORE_STAGE as TELCO_SCORE , end b.PHONE_NUMBER, b.RESPOND_TIME FROM losrep.partner_score a JOIN losrep.log_score b ON a.APP_CODE = b.APP_CODE WHERE a.LAST_UPDATED_DATE > NOW() - INTERVAL 36 HOUR; correct

There are a few syntax errors in your

CASE

statement. Specifically: 1. Alias Position: The

AS TELCO_SCORE

alias must be placed after the

END

keyword, not inside the

THEN

clause. 2. Trailing Commas: You have a comma inside the

THEN

clause and a missing comma after the

END

keyword. 3. End keyword: The

CASE

expression must be separated from the next column (

b.PHONE_NUMBER

) by a comma. Corrected Query

sql
SELECT
    a.APP_CODE,
    a.CREATED_DATE,
    a.LAST_UPDATED_DATE,
    a.SCORE,
    b.SCORE_STAGE,
    CASE 
        WHEN b.PHONE_NUMBER IS NOT NULL THEN b.SCORE_STAGE 
    END AS TELCO_SCORE,
    b.PHONE_NUMBER,
    b.RESPOND_TIME
FROM losrep.partner_score a
JOIN losrep.log_score b ON a.APP_CODE = b.APP_CODE
WHERE a.LAST_UPDATED_DATE > NOW() - INTERVAL 36 HOUR;

What was fixed: * CASE Syntax: Moved

AS TELCO_SCORE

to follow the

END

keyword. * Commas: Removed the incorrect comma after

SCORE_STAGE

inside the case and added a comma after the alias to separate it from

b.PHONE_NUMBER

. * Time Interval:

NOW() - INTERVAL 36 HOUR

is the correct syntax for date arithmetic in StarRocks. Optional Optimization If you want the

TELCO_SCORE

to fall back to a specific value (like 'N/A' or the original score) when the phone number is null, you can add an

ELSE

clause:

sql
CASE 
    WHEN b.PHONE_NUMBER IS NOT NULL THEN b.SCORE_STAGE 
    ELSE 'NO_PHONE' 
END AS TELCO_SCORE

References • sql-reference/sql-functions/condition-functions/case_when.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/date-time-functions/date_sub.md References • sql-reference/sql-functions/date-time-functions/timestampadd.md • sql-reference/sql-functions/date-time-functions/date_add.md • sql-reference/sql-functions/date-time-functions/date_sub.md • sql-reference/sql-functions/date-time-functions/microseconds_add.md • sql-reference/sql-functions/date-time-functions/timestampdiff.md • sql-reference/sql-functions/date-time-functions/seconds_add.md • sql-reference/sql-functions/date-time-functions/date_slice.md