I am trying to create view with following query using SQL Assiatant
SyntaxEditor Code Snippet
create view DBO.KIMBALLSIZECOLOUR as with KimballSizeColourOrdered (Kimball, dimColourID, dimSizeID, KSC_number) as(select ic.Kimball, ic.dimColourID, ic.dimSizeID, ROW_NUMBER() over(partition by ic.Kimball order by ic.SCDStartDate desc, ic.dimItemCodeID desc) as KSC_number from dbo.dimItemCode ic where ic.SCDEndDate IS NULL)select ksco.Kimball, ksco.dimColourID, ksco.dimSizeID from KimballSizeColourOrdered ksco where ksco.KSC_number = 1;
Actually above DDL is converted from SQL server. Does CTE not work in teradata. Are there any other alternative in Teradata. I am using TD 14.0
First, does the query itself run outside of a view?
If yes, try to create the view with the query as a derived table instead, per Dieter's post.