All Forums Database
rambles 2 posts Joined 12/13
23 Jul 2014
Creating a view from a CTE query

Hello
I'm trying to create a view from WITH ... SELECT query.  The query itself works fine but once I try to create a view from it, I get a 3707 error.  
I'm running off TD 14.10.  Any thoughts?
Thanks, Richard

create view hif_user.cprd_modexac5_atb_fnl as (
  with cte(patid, eventdate, row_num, ocs_date) as 
  ( 
    select patid, eventdate, ocs_date,
           row_number() over (partition by patid, eventdate order by diff) as row_num
    from hif_user.cprd_modexac4_atb_ocs
  ) 
  select patid, eventdate, row_num, ocs_date
  from cte
  where row_num = 1
)

 

Tags:
dnoeth 4628 posts Joined 11/04
23 Jul 2014

Hi Richard,
a CTE in a View is not allowed (don't ask me, why?).
As it's not a recursive CTE you can re-write it using a Derived Table:

create view hif_user.cprd_modexac5_atb_fnl as (
  select patid, eventdate, row_num, ocs_date
  from 
    ( 
    select patid, eventdate, ocs_date,
           row_number() over (partition by patid, eventdate order by diff) as row_num
    from hif_user.cprd_modexac4_atb_ocs
   )  as dt
  where row_num = 1
)

And if this is your actual query you can simplifiy it using QUALIFY:

create view hif_user.cprd_modexac5_atb_fnl as (
    select patid, eventdate, ocs_date
    from hif_user.cprd_modexac4_atb_ocs
    qualify row_number() over (partition by patid, eventdate order by diff) = 1
)

 

Dieter

rambles 2 posts Joined 12/13
24 Jul 2014

Excellent, thanks for the two alternatives.
 
Moving from SAS to Teradata - it really is a whole different ball game...
 
Thanks again, Rich

You must sign in to leave a comment.