Search code examples
sqlimpala

Get an error when using DISTINCT in SELECT Statement

I have this query:

SELECT
CASE 
    WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(now(), 'yyyy-MM-dd') THEN from_timestamp(to_timestamp(SUBSTR(fact.delivery_time,1,10), 'yyyy-MM-dd'),'dd.MM.yyyy')
    WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(DAYS_ADD(now(),-1), 'yyyy-MM-dd') THEN from_timestamp(DAYS_ADD(now(),-1),'dd.MM.yyyy')
    WHEN SUBSTR(fact.delivery_time,1,10) <= from_timestamp(DAYS_ADD(now(),-2), 'yyyy-MM-dd') THEN 'Older'
END AS delivery_day,
DISTINCT fact.order_id AS ORDER_ID
FROM fact
JOIN article art ON art.article_id = fact.article_id
WHERE fact.status IN ('OPEN', 'RECEIVING', 'REGISTERED')
AND fact.type !='RETURN'
AND art.classification_name LIKE '15deg%'
GROUP BY
    fact.delivery_time;

and I get this error:

ParseException: Syntax error in line 605:undefined: DISTINCT fact.order_id AS ORDER_ID ^ Encountered: DISTINCT Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, LEFT, NOT, NULL, REPLACE, RIGHT, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error

I need the ORDER_ID only once, although it can exist multiple times in the table FACT, because I need to count it afterwards.

Thanks for the help.

Solution

  • DISTINCT considers all the columns in the SELECT statement, not just the specified column; and so the error. So, to filter DISTINCT order_id; you can use group by ORDER_ID :

    SELECT
CASE 
    WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(now(), 'yyyy-MM-dd') THEN from_timestamp(to_timestamp(SUBSTR(fact.delivery_time,1,10), 'yyyy-MM-dd'),'dd.MM.yyyy')
    WHEN SUBSTR(fact.delivery_time,1,10) = from_timestamp(DAYS_ADD(now(),-1), 'yyyy-MM-dd') THEN from_timestamp(DAYS_ADD(now(),-1),'dd.MM.yyyy')
    WHEN SUBSTR(fact.delivery_time,1,10) <= from_timestamp(DAYS_ADD(now(),-2), 'yyyy-MM-dd') THEN 'Older'
END AS delivery_day,
fact.order_id AS ORDER_ID
FROM fact
JOIN article art ON art.article_id = fact.article_id
WHERE fact.status IN ('OPEN', 'RECEIVING', 'REGISTERED')
AND fact.type !='RETURN'
AND art.classification_name LIKE '15deg%'
GROUP BY
    delivery_day,
    ORDER_ID;