Error creating a view using "WITH RECURSIVE"

Database
N/A

Error creating a view using "WITH RECURSIVE"

First of all, thanks for this amazing forum and for all the help provided!

I found 3 previous posts on this topic, but I don't think the answers were suffucuent for my case. I am trying to create this view:

CREATE VIEW CFDW2_ABCD_VWS.XYZ_HYPERION_PLATFORM

AS

WITH RECURSIVE TEMP_TABLE ( PARENT_DESC, LVL_4_CD, CHILD_DESC , CHILD_KEY , LVL) AS (

SELECT

PARENT_DESC

,TRIM(CHILD_DESC)

, TRIM(CHILD_DESC) (VARCHAR(5000))

, TRIM(CHILD_DESC)

, 1(INTEGER)

FROM CFDW2_ACBD_VWS.XYZ_HYPERION_HIER

UNION ALL

SELECT

INDIRECT.PARENT_DESC

, INDIRECT.CHILD_DESC

, DIRECT.CHILD_DESC || '->' || TRIM(LVL) || '->' || TRIM(INDIRECT.CHILD_DESC) (VARCHAR(5000)) AS CHILD_DESC

, SUBSTRING( DIRECT.CHILD_DESC FROM 1 FOR INDEX(DIRECT.CHILD_DESC || '->' , '->')-1)

, DIRECT.LVL + 1

FROM CFDW2_ABCD_VWS.XYZ_HYPERION_HIER INDIRECT

,TEMP_TABLE DIRECT

WHERE DIRECT.PARENT_DESC = INDIRECT.CHILD_DESC

AND DIRECT.LVL < 20

)

SELECT DISTINCT

TEMP_TABLE.PARENT_DESC

, TEMP_TABLE.LVL_4_CD

, CASE

WHEN (POSITION('_' IN SUBSTR(CHILD_KEY,POSITION ('_' IN CHILD_KEY)+1)) = 1)

OR (POSITION('_' IN SUBSTR(CHILD_KEY,POSITION ('_' IN CHILD_KEY)+1)) > 1)

THEN TRIM(TRAILING '_' FROM SUBSTR(CHILD_KEY,POSITION ('_' IN CHILD_KEY)+1,POSITION ('_' IN SUBSTR(CHILD_KEY,POSITION ('_' IN CHILD_KEY)+1) )))

ELSE SUBSTR(CHILD_KEY,POSITION ('_' IN CHILD_KEY)+1)

END CHILD_KEY

, LVL

, CASE

WHEN LVL >3

THEN SUBSTR(TEMP_TABLE.CHILD_DESC, (INDEX( TEMP_TABLE.CHILD_DESC , '->' || TRIM(LVL-3) || '->')+5) , (INDEX( TEMP_TABLE.CHILD_DESC , '->' || TRIM(LVL-2) || '->') - INDEX( TEMP_TABLE.CHILD_DESC , '->' || TRIM(LVL-3) || '->') -5) )

WHEN LVL = 3

THEN SUBSTR (TEMP_TABLE.CHILD_DESC , 1,INDEX( TEMP_TABLE.CHILD_DESC , '->' || TRIM(1) || '->')-1)

ELSE NULL

END LEVEL_3_NODE

FROM TEMP_TABLE

WHERE TEMP_TABLE.PARENT_DESC = 'ABCDEFG'

AND CHILD_DESC LIKE '%TERA DATA%'

CFDW2_ACBD_VWS.XYZ_HYPERION_HIER is a simple (non-recursive view). When I try to create this view it gives me the error result:

6926:  WITH [RECURSIVE] clause or recursive view is not supported within WITH [RECURSIVE] definitions, views, triggers or stored procedures.

Output directed to Answerset window

My colleague has implemented the same view (with slight variations, he used the child key directly without modifying it using the CASE statement).

        Would you be able to give some insight into which par tof this query could be causing this error? Any help is much appreciated. Thanks!

1 REPLY
N/A

Re: Error creating a view using "WITH RECURSIVE"

recusive view def should start with CREATE RECURSIVE VIEW

here an example from the docu...

CREATE RECURSIVE VIEW reachable_from (source,destination,depth) AS (

SELECT root.source, root.destination, 0 AS depth

FROM flights AS root

WHERE root.source = 'Paris'

UNION ALL

SELECT in1.source, out1.destination, in1.depth + 1

FROM reachable_from AS in1, flights AS out1

WHERE in1.destination = out1.source

AND in1.depth <= 100);