TERADATA SQL QUery

Database
N/A

TERADATA SQL QUery

WITH CTE AS (
SELECT
CLIENT_NO,
LN_NO,
ACTIVITY_STEP_CD,
ACTUAL_END_DT,
 DW_LOAD_TIMESTAMP,
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
FROM EIW_D_MIDE_SVC_BV_MSP_S1.MSP_LOAN_ACTIVITY_STEP_HIST
where ACTIVITY_STEP_CD IN ('U20')
),
CTE1 AS (
SELECT CLIENT_NO,LN_NO,
MAX(NullChangeDate) as NullChangeDate,
MAX(LastChangeOfNotNull) as LastChangeOfNotNull FROM (
SELECT
 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)

UNION ALL

SELECT CLIENT_NO,LN_NO,
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 )
) a
GROUP BY CLIENT_NO,LN_NO
)

SELECT
CLIENT_NO,
LN_NO,
CASE WHEN (NullChangeDate IS NOT NULL and LastChangeOfNotNUll IS NOT NULL)
OR
(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
FROM CTE1

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 ?

Thanks,

2 REPLIES
Teradata Employee

Re: TERADATA SQL QUery

Teradata's implementation of CTE evaluates in reverse order. Switch so CTE1 is first, and CTE on which it depends comes second.

N/A

Re: TERADATA SQL QUery

Thanks Fred. It worked like a charm.. :)