Create view failed 6926: definitions, Views, triggers and stored procedures.

Database
Highlighted
Enthusiast

Create view failed 6926: definitions, Views, triggers and stored procedures.

Hi

 

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

1 REPLY
Teradata Employee

Re: Create view failed 6926: definitions, Views, triggers and stored procedures.

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.

 

http://downloads.teradata.com/forum/database/creating-a-view-from-a-cte-query

 

thanks

 

Dave