WITH CTE AS (
row_number () over (partition by LN_NO, case when ACTUAL_END_DT is NULL then 1 else 2 end order by DW_LOAD_TIMESTAMP ) as Rn,
row_number () over ( partition by LN_NO order by DW_LOAD_TIMESTAMP DESC) as RowNumberDesc
where ACTIVITY_STEP_CD IN ('U20')
CTE1 AS (
MAX(NullChangeDate) as NullChangeDate,
MAX(LastChangeOfNotNull) as LastChangeOfNotNull FROM (
CLIENT_NO,LN_NO, MAX(NullChangeDate) as NullChangeDate,
CAST( NULL as DATE) as LastChangeOfNotNull FROM CTE WHERE Rn = 1 AND ACTUAL_END_DT IS NOT NULL
AND EXISTS ( SELECT 1 FROM CTE c1 where c1.LN_NO = cte.LN_NO and ( CAST(c1. DW_LOAD_TIMESTAMP AS DATE) < CAST( cte.DW_LOAD_TIMESTAMP AS DATE) ) AND c1.ACTUAL_END_DT IS NULL)
CAST ( NULL AS DATE) as NullChangeDate ,
CAST( DW_LOAD_TIMESTAMP AS DATE) as LastChangeOfNotNull FROM CTE WHERE
RowNumberDesc = 1 AND ACTUAL_END_DT IS NOT NULL AND EXISTS
( SELECT 1 FROM CTE c2 where RowNumberDesc = 2 AND c2.LN_NO = CTE.LN_NO AND c2.ACTUAL_END_DT IS NOT NULL )
GROUP BY CLIENT_NO,LN_NO
CASE WHEN (NullChangeDate IS NOT NULL and LastChangeOfNotNUll IS NOT NULL)
(NullChangeDate IS NULL and LastChangeOfNotNUll IS NOT NULL)
THEN LastChangeOfNotNUll ELSE NULL END Sale_Date_Changed,
CASE WHEN LastChangeOfNotNUll IS NULL
THEN NullChangeDate ELSE NULL END Sale_Date_Set
Hello, I have a piece of SQL whihc works fine in sql server management studio, But when i try to run it in teradata it gives me an error - "Object CTE does not exist" .
COuld anybody help identify what the problem is ?
Teradata's implementation of CTE evaluates in reverse order. Switch so CTE1 is first, and CTE on which it depends comes second.