ETL | Exists() | Error - Field not found

[sibijoseph]

Hello,

I'm facing an issue while trying to load a table which has Exists() condition for date field.

These are the scripts that I tried with:

1.

JOIN [CRE_CNC_COMPL]:
LOAD
      Date([DAY_DT],'YYYY-MM-DD') as [CREATION_DATE],
      [SYW_NO] as [CREATION_WEEK],
      [FY_NO] as [CREATION_YEAR]
WHERE EXISTS([CREATION_DATE], [DAY_DT]);
SELECT
     "DAY_DT",
     "SYW_NO",
     "FY_NO"
FROM "ECOMTEST"."DAY_DT" ;

2.

JOIN [CRE_CNC_COMPL]:
LOAD
      Date([DAY_DT],'YYYY-MM-DD') as [CREATION_DATE],
      [SYW_NO] as [CREATION_WEEK],
      [FY_NO] as [CREATION_YEAR];
SELECT
     "DAY_DT",
     "SYW_NO",
     "FY_NO"
FROM "ECOMTEST"."DAY_DT"
WHERE EXISTS("DAY_DT");

Please note - Here the table name is 'DAY_DT' as well as one of the fields in the table is also 'DAY_DT'.

On trying to load with the above scripts, this is the error message that I'm shown:

Let me know if any additional details are required.

[sibijoseph · Accepted solution]

I found the answer for my query, hope it helps someone.

JOIN [CRE_CNC_COMPL]:
LOAD
     Date([DAY_DT],'YYYY-MM-DD') as [CREATION_DATE],
     [SYW_NO] as [CREATION_WEEK],
     [FY_NO] as [CREATION_YEAR]
WHERE Exists([CREATION_DATE], [DAY_DT]);
SELECT
     "DAY_DT",
     "SYW_NO",
     "FY_NO"
FROM "ECOMTEST"."DAY_DT"
WHERE DAY_DT >= to_date('2017-09-01', 'YYYY-MM-DD') and DAY_DT < to_date('2020-09-01', 'YYYY-MM-DD');