Database
Enthusiast

## What is the syntax for recursive views? How to aggregate the final output set of a recursive view?

I have a recursive select query as below. Which need to be converted into a recursive view. {Since normal view does not support a recursive query}. How to convert this select query to recursive view

WITH RECURSIVE JDAVENDNUM (REAL_NM,

SUB_NM,

CON_NM,

RAW_NUM)

AS

( SELECT a.REAL_NM,

a.SUB_NM,

CAST(TRIM(a.CON_NM) AS VARCHAR(2000)),

a.RAW_NUM

FROM vms_vend_rlp a

WHERE a.RAW_NUM = 1

UNION ALL

SELECT a.REAL_NM,

a.SUB_NM,

b.CON_NM || ',' || TRIM(a.CON_NM) CON_NM,

a.RAW_NUM

FROM vms_vend_rlp a,

JDAVENDNUM b

WHERE a.REAL_NM   = b.REAL_NM

AND a.SUB_NM    = b.SUB_NM

AND a.RAW_NUM = b.RAW_NUM + 1

)

SELECT REAL_NM,

SUB_NM,

MAX(CON_NM) CON_NM

FROM JDAVENDNUM

GROUP BY REAL_NM,SUB_NM

;
Tags (1)
Enthusiast

## Re: What is the syntax for recursive views? How to aggregate the final output set of a recursive view?

Have tried this but not able include GROUP BY and MAX() function

CREATE RECURSIVE VIEW JDAVENDNUM

(REAL_NM,

SUB_NM,

CON_NM,

ROW_NUM)

AS

( SELECT a.REAL_NM,

a.SUB_NM,

CAST(TRIM(a.CON_NM) AS VARCHAR(2000)),

a.ROW_NUM

FROM New_veiw a

WHERE a.ROW_NUM = 1

UNION ALL

SELECT a.REAL_NM,

a.SUB_NM,

b.CON_NM || ',' || TRIM(a.CON_NM) CON_NM,

a.ROW_NUM

FROM New_veiw a,

JDAVENDNUM  b

WHERE a.REAL_NM   = b.REAL_NM

AND a.SUB_NM    = b.SUB_NM

AND a.ROW_NUM = b.ROW_NUM + 1

);