Converting MS SQL to Snowflake SQL, How do I return a count of records using a subquery

Question

I am converting a query from MS SQL to Snowflake SQL and I would like to know how to turn a record count using a subquery (or Snowflake equivalent). In the code below, "NUM_VISITS" returns the number of encounters that occurred for each patient.

If I hardcode a static number in place of "NUM_VISITS", the query works without issue, so I am confident the issue is how I am calling the subquery. When running the code as presented, I get the following error:

SQL compilation error: syntax error line 15 at position 20 unexpected 'SELECT'. syntax error line 15 at position 32 unexpected '('.

SELECT
    
    'HB' as "TXN_SOURCE",
    enc.PAT_ID,
    enc.PAT_ENC_CSN_ID,
    enc.contact_date,
    enc.PRIMARY_LOC_ID,
    enc.department_id,
    CASE
        WHEN enc.DEPARTMENT_ID = 1229296 AND txn.PROC_ID = 12255396 THEN 'YES'
        ELSE 'NO'
    END as "KITCHEN_CLASS",
    "NUM_VISITS" = (SELECT COUNT(enc2.contact_date)
                    FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc2
                    JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn2
                        ON enc2.PAT_ENC_CSN_ID = txn2.PAT_ENC_CSN_ID
                    WHERE enc.pat_id = enc2.pat_id
                            enc2.CONTACT_DATE BETWEEN $StartDate AND $EndDate
                            AND txn2.PROC_ID IN (12255387,12255388,12255395,12255396)
                            AND enc2.SERV_AREA_ID = 12288)
             ,
    txn.PROC_ID
    
FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc
JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn
    ON enc.PAT_ENC_CSN_ID = txn.PAT_ENC_CSN_ID

WHERE enc.CONTACT_DATE BETWEEN $StartDate AND $EndDate
AND txn.PROC_ID IN (12255387,12255388,12255395,12255396)
AND enc.SERV_AREA_ID = 12288;

After resolving the syntax issue that was pointed out, I was able to get the code to work by calling the subquery from a JOIN. Below is the functioning code:

SELECT
    
    'HB' as "TXN_SOURCE",
    enc.PAT_ID,
    enc.PAT_ENC_CSN_ID,
    enc.contact_date,
    enc.PRIMARY_LOC_ID,
    enc.department_id,
    CASE
        WHEN enc.DEPARTMENT_ID = 1229296 AND txn.PROC_ID = 12255396 THEN 'YES'
        ELSE 'NO'
    END as "KITCHEN_CLASS",
    cnt.COUNT,
    txn.PROC_ID
    
FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc
JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn
    ON enc.PAT_ENC_CSN_ID = txn.PAT_ENC_CSN_ID
LEFT JOIN (SELECT   enc2.PAT_ID,
                    COUNT(enc2.contact_date) as "COUNT"
           FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc2
           JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn2
                ON enc2.PAT_ENC_CSN_ID = txn2.PAT_ENC_CSN_ID
           WHERE enc2.CONTACT_DATE BETWEEN $StartDate AND $EndDate
                AND txn2.PROC_ID IN (12255387,12255388,12255395,12255396)
                AND enc2.SERV_AREA_ID = 12288
          GROUP BY enc2.PAT_ID) as cnt ON enc.PAT_ID = cnt.PAT_ID

WHERE enc.CONTACT_DATE BETWEEN $StartDate AND $EndDate
AND txn.PROC_ID IN (12255387,12255388,12255395,12255396)
AND enc.SERV_AREA_ID = 12288;

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2021-01-28T01:10:30

This is a simplification of the query in the question:

SELECT x, 
    1 = (SELECT COUNT(1)
                    FROM (select 1 x)
                    WHERE x = 1
                            x BETWEEN 1 AND 5
                            AND x IN (1)
                            AND x = 1)
             ,
    x 
FROM (select 1 x)

You can see that the query as presented will never run, as it's missing at least one AND.

To fix the query just add an AND before the BETWEEN line:

SELECT x, 1=(select 1),
    1 = (SELECT COUNT(1)
                    FROM (select 1 x)
                    WHERE x = 1
                            AND x BETWEEN 1 AND 5
                            AND x IN (1)
                            AND x = 1)
             ,    x   
FROM (select 1 x);

Converting MS SQL to Snowflake SQL, How do I return a count of records using a subquery

1 Answers