Snowflake join on mixed datatypes not returning results-CodePudding

I'm running into an issue with trying to join with a subquery from the same table where no results are returned.

SELECT 
    CA.PARTICIPANT || CA.RECOGNITION_MONTH || CA.RECOGNITION_YEAR,
    CA.PLAN_NAME,
    PL.PLAN_NAME
FROM
    CSBI_DB.CS_SALES_OPS.CS_COMP_ANLTCS CA
LEFT JOIN
    (
        SELECT DISTINCT 
            PARTICIPANT || RECOGNITION_MONTH || RECOGNITION_YEAR AS MATCH_STRING,
            PLAN_NAME 
        FROM 
            CSBI_DB.CS_SALES_OPS.CS_COMP_ANLTCS 
        WHERE 
            PLAN_NAME IS NOT NULL
    ) PL 
ON
    TO_VARCHAR(PL.MATCH_STRING) = TO_VARCHAR(CA.PARTICIPANT || CA.RECOGNITION_MONTH || CA.RECOGNITION_YEAR)
WHERE 
    CA.PLAN_NAME IS NULL

I've tried several variations on the above with no results populating for PL.PLAN_NAME. If I join using just the 'PARTICIPANT' (varchar datatype) field then results will populate (albeit not the desired results) and if I join just on the concatenated 'RECOGNITION_MONTH' & 'RECOGNITION_YEAR' (both numeric datatypes) fields but when combining the two I keep getting null results. I've also tried joining w/o casting the numeric fields to varchar but no luck. This seems like it should be straightforward but obviously I'm missing something...anyone see where I'm going wrong?

CodePudding user response：

If any of those columns are NULL, which your comment above indicates that they are, then the concatenation will end up as NULL. This means you won't get a match on that join condition, because NULL doesn't = NULL.

To fix, you'll need to COALESCE the NULL-able fields to a non-NULL value, like: NVL(participant, '') || NVL(recognition_month, '') || NVL(recognition_year, ''), or whatever the NULL-able fields are.

CodePudding user response：

If you name you first select result, Snowflake will let you use that label in the where clause, thus the SQL can be a little simpler like so:

SELECT 
    ca.participant || ca.recognition_month || ca.recognition_year AS ca_match_string,
    ca.plan_name,
    pl.plan_name
FROM
    csbi_db.cs_sales_ops.cs_comp_anltcs AS ca
LEFT JOIN
    (
        SELECT DISTINCT 
            participant || recognition_month || recognition_year AS match_string,
            plan_name 
        FROM 
            csbi_db.cs_sales_ops.cs_comp_anltcs 
        WHERE 
            plan_name IS NOT NULL
    ) AS pl 
ON
    pl.match_string = ca_match_string
WHERE 
    ca.plan_name IS NULL