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

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)
1 REPLY
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

);