Creating a view from a CTE query

Database

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 (2)
2 REPLIES
Junior Contributor

Re: Creating a view from a CTE query

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
)

Re: Creating a view from a CTE query

Excellent, thanks for the two alternatives.

Moving from SAS to Teradata - it really is a whole different ball game...

Thanks again, Rich