Using Case When with ON clause SQL - Netezza

Question

please I have the below Code

SELECT DISTINCT 
CASE WHEN LENGTH (i.MOBILE_NUMBER) = 10 THEN 0||i.MOBILE_NUMBER ELSE i.MOBILE_NUMBER END AS New_Mob_Num,
COUNT(i.SUBS_ID) AS Subs_Count,
COUNT(s.MESSAGEID) AS SMS_COUNT,
              SUM(CASE WHEN s.MESSAGESTATE = 'Undeliverable' THEN COUNT ELSE 0 END) AS Undeliverablecount,
              SUM(CASE WHEN s.MESSAGESTATE = 'Delivered' THEN COUNT ELSE 0 END) AS Deliveredcount
    FROM PRD_STG.MOB.INF_SUBSCRIBER i
    INNER JOIN ANALYTICS.sms.mq s 
ON i.New_Mob_Num = strright(s.destination, 11)
GROUP BY 1

and I got an error ERROR: Attribute 'I.NEW_MOB_NUM' not found

who can I use the column CASE WHEN LENGTH (i.MOBILE_NUMBER) = 10 THEN 0||i.MOBILE_NUMBER ELSE i.MOBILE_NUMBER END AS New_Mob_Num with ON clause while Joining.

and when using the CASE WHEN statement with ON clause like below, I got a wrong results

SELECT DISTINCT 
CASE WHEN LENGTH (i.MOBILE_NUMBER) = 10 THEN 0||i.MOBILE_NUMBER ELSE i.MOBILE_NUMBER END AS New_Mob_Num,
COUNT(i.SUBS_ID) AS Subs_Count,
COUNT(s.MESSAGEID) AS SMS_COUNT,
              SUM(CASE WHEN s.MESSAGESTATE = 'Undeliverable' THEN COUNT ELSE 0 END) AS Undeliverablecount,
              SUM(CASE WHEN s.MESSAGESTATE = 'Delivered' THEN COUNT ELSE 0 END) AS Deliveredcount
    FROM PRD_STG.MOB.INF_SUBSCRIBER i
    INNER JOIN ANALYTICS.sms.mq s 
ON (CASE WHEN LENGTH (i.MOBILE_NUMBER) = 10 THEN 0||i.MOBILE_NUMBER ELSE i.MOBILE_NUMBER END) 
= strright(s.destination, 11)
GROUP BY 1

Answer 1

This is what I mean:

SELECT i.New_Mob_Num ,
       COUNT(i.SUBS_ID) AS Subs_Count,
       COUNT(s.MESSAGEID) AS SMS_COUNT,
       SUM(CASE WHEN s.MESSAGESTATE = 'Undeliverable' THEN COUNT ELSE 0 END) AS Undeliverablecount,
       SUM(CASE WHEN s.MESSAGESTATE = 'Delivered' THEN COUNT ELSE 0 END) AS Deliveredcount
FROM (SELECT i.*,
             (CASE WHEN LENGTH (i.MOBILE_NUMBER) = 10 
                   THEN '0'||i.MOBILE_NUMBER
                   ELSE i.MOBILE_NUMBER
              END) AS New_Mob_Num
      FROM PRD_STG.MOB.INF_SUBSCRIBER i
     ) i JOIN
     ANALYTICS.sms.mq s 
     ON i.New_Mob_Num = strright(s.destination, 11)
GROUP BY 1;

That should fix your syntax error. If this doesn't do anything useful, ask a new question with appropriate sample data and desired results.