Accees table attribute in Snowflake CTE

Question

use database DQ_MART;

use schema WORKING;

WITH ASCENDER_EMPLOYEE AS (
    **SELECT DISTINCT EMPLOYEE_ID FROM RECONCILLIATION_ASCENDER_WORKER_TIMESHEET**
),
     WORKDAY_EMPLOYEE AS (
         **SELECT DISTINCT EMPLOYEE_ID FROM RECONCILLIATION_WORKDAY_WORKER_TIMESHEET**
     )
     
SELECT 'Missing employee in Ascender'              DQ_RULE_NAME,
       RECONCILLIATION_WORKDAY_WORKER_TIMESHEET.EMPLOYEE_ID                         KEY
      
FROM WORKDAY_EMPLOYEE WORKDAY
         LEFT OUTER JOIN ASCENDER_EMPLOYEE ASCENDER
                         ON ASCENDER.EMPLOYEE_ID = WORKDAY.EMPLOYEE_ID
;

Hi All, I am bit new to Snowflake SQL CTE. In the above query, I am getting an error, Error: invalid identifier 'RECONCILLIATION_WORKDAY_WORKER_TIMESHEET.EMPLOYEE_ID' (line 16) in this line

RECONCILLIATION_WORKDAY_WORKER_TIMESHEET.EMPLOYEE_ID

The select statements where the same table is accessed runs properly. Te database and schema where the table resides is set correctly and I do have SELECT grant on the tables.

Is there a scope visibility in Snowflake which is causing the error to occur. Any suggestions will be welcome.

score 0 · Answer 1 · answered May 27 '21 at 13:22

"RECONCILLIATION_WORKDAY_WORKER_TIMESHEET.EMPLOYEE_ID" means the column "EMPLOYEE_ID" in the table "RECONCILLIATION_WORKDAY_WORKER_TIMESHEET".

Your select statement that uses this column is:

SELECT <column list>
FROM WORKDAY_EMPLOYEE WORKDAY
LEFT OUTER JOIN ASCENDER_EMPLOYEE ASCENDER
ON ASCENDER.EMPLOYEE_ID = WORKDAY.EMPLOYEE_ID

which doesn't include a table called RECONCILLIATION_WORKDAY_WORKER_TIMESHEET - which is why you are getting the error

Accees table attribute in Snowflake CTE

1 Answers1