Unable to find error in the query - Snowflake-CodePudding

Error: SQL compilation error: error line 105 at position 8 invalid identifier 'INTENT'. There are two CTE's, and then the select statement. First CTE works fine if run separately, the error is in the second CTE. I am not sure what is causing the syntax error.

Thanks in advance.

WITH Test AS(
SELECT a.NUMBER
      ,b.Event_date 
      ,b.SESSION_ID  
      ,b.EVENT_TIMESTAMP 
      ,b.CURRENT_VIEW_NAME 
FROM PD_PRESENT.CUS.CUS_ISSUE as a
LEFT JOIN PD_PRESEN.CUS.REQ as b
ON a.NUMBER = B.NUMBER 
WHERE a.CREATED_DATE BETWEEN '2022-02-24 00:00:00.000' AND '2022-02-27 23:59:59.997'
AND b.Event_date BETWEEN '2022-02-24 00:00:00.000' AND '2022-02-27 23:59:59.997'
AND b.USER_GROUP = 'Customer'  
),

Consolidate AS(
SELECT DISTINCT 
             a.Number
            ,a.EVENT_DATE
            ,a.EVENT_TIMESTAMP
            ,a.Current_View_Name
            ,CASE 
            -- MOAT
                WHEN a.Current_View_Name LIKE '%CONDO%' THEN 'MOAT'
                WHEN a.Current_View_Name LIKE '%RENTER%' THEN 'MOAT'
                WHEN a.Current_View_Name LIKE '%FIRE%' THEN 'MOAT'
            -- BOAT
                WHEN a.Current_View_Name LIKE '%WATER%' THEN 'BOAT'
                WHEN a.Current_View_Name LIKE '%BOAT%' THEN 'BOAT'
            -- Error
                WHEN a.Current_View_Name LIKE '%Error%' THEN 'ERROR'
                        END AS "Intent"

            ,CASE
                WHEN a.Current_View_Name LIKE '%Mobile%' THEN 'MOBILE' ELSE 'DESKTOP'
            END AS "Source"
FROM Test as a
)
  
SELECT 
        a.NUMBER 
       ,a.Event_Date as "ProcessedDate"
       ,Intent as "TransactionIntent" -- **LINE 105**
       ,MIN(a.EVENT_TIMESTAMP) as "TransactionStart"
       ,MAX(a.EVENT_TIMESTAMP) as "TransactionEnd"
       ,'SelfService' AS "SourceType"
FROM Consolidate as a
WHERE a.Intent IS NOT NULL
GROUP BY a.NUMBER
        ,a.ProcessedDate
        ,a.Intent

CodePudding user response：

Snowflake treats unquoted identifiers as if they were uppercase. While quoted identifiers are case-sensitive. So you might need to specify your column alias as "INTENT" (all caps) to later refer to it without quotes.

This behavior can be changed with QUOTED_IDENTIFIERS_IGNORE_CASE should you need it.

QUOTED_IDENTIFIERS_IGNORE_CASE:

Specifies whether letters in double-quoted object identifiers are stored and resolved as uppercase letters. By default, Snowflake preserves the case of alphabetic characters when storing and resolving double-quoted identifiers. (See Identifier Resolution.) You can use this parameter in situations in which third-party applications always use double quotes around identifiers.