Can anyone help me for creating a view using common table expression.
for eg:
I want to create a view from following CTE
With T
(
RECORD_TYPE
,ATC4
,ATC4_DESCR
,SUPERGROUP
,Acute_Chronic
) as
(
select
RECORD_TYPE
,ATC4
,ATC4_DESCR
,case when locate(' ',TEXT) > 0 and length(trim(ATC4))=5 then substr(TEXT,1,locate(' ',TEXT)) else '' end as SUPERGROUP
,case when locate(' ',TEXT) > 0 and length(trim(ATC4))=5 then TRIM(substr(TEXT,locate(' ',TEXT),length(substr(TEXT,locate(' ',TEXT))))) else '' end as Acute_Chronic
from
(
SELECT
RECORD_TYPE
,case when substr(ATC4,5,1)='0' then substr(ATC4,1,4) else ATC4 end as ATC4
,case when locate(' ',TEXT) > 0 then substr(TEXT,1,locate(' ',TEXT)) else TEXT end as ATC4_DESCR
,case when locate(' ',TEXT) > 0 then TRIM(substr(TEXT,locate(' ',TEXT),length(substr(TEXT,locate(' ',TEXT))))) else TEXT end as TEXT
from
(
SELECT
RECORD_TYPE
,replace(substr(TEXT,1,locate(' ',TEXT)),'00','') as ATC4
,TRIM(substr(TEXT,locate(' ',TEXT)+3,length(substr(TEXT,locate(' ',TEXT)+3)))) as TEXT
from
(
select
TRIM(substr(TEXT,1,locate(' ',TEXT))) as RECORD_TYPE
,substr(TEXT,locate(' ',TEXT)+1,length(substr(TEXT,locate(' ',TEXT)+1))) as TEXT
from DBOWNER.LC_LOAD_CPI WHERE TEXT like '00004LATC%'
)
)
)
)
select
ATC4 as TC4_Code
,ATC4_DESCR as TC4_Descr
,SUPERGROUP
,ACUTE_CHRONIC
from T
where length(trim(ATC4))=5
Thanks in advance.