All Forums Database
Koentje 23 posts Joined 09/04
11 Mar 2012
With recursive in a Select

How can I use a WITH RECURSIVE as Derived table in a (complex) Select?

Below a small part of the query. The query contains lots of Inner and Left Outer Joins, and 1 of them should be a hierarchie, prepared for the output.

Now I got message 3707, expecting '('

Select *
from T0200_Product T0200

Inner join (With recursive T0202_Temp
    (Starting_Product_Group_Id
     ,Product_Group_Id
     ,Parent_Prod_Group_Id
     ,NSeq
    )
   As (Select T0202.Product_Group_Id
                     ,T0202.Product_Group_Id
                     ,T0202.Parent_Prod_Group_Id
                     ,1
          From T0202_Product_Group T0202
          Where Product_Group_End_Date = '9999-12-31'
               And Parent_Prod_Group_Id is null
        Union ALL
          Select T0202_Temp.Starting_Product_Group_Id
                     ,T0202.Product_Group_Id
                     ,T0202.Parent_Prod_Group_Id
                     ,NSeq + 1
           From T0202_Product_Group T0202
           Inner join T0202_Temp T0202_Temp
                       On T0202.Parent_Prod_Group_Id = T0202_Temp.Product_Group_Id
           Where T0202.Product_Group_End_Date = '9999-12-31'
                And NSeq < 10
          )
         Select T0202.Starting_Product_Group_Id
                    ,T0202.Product_Group_Id
                    ,T0202.Parent_Prod_Group_Id
                    ,T0202.NSeq
                    ,A0202.Product_Group_Desc
          From T0202_Temp T0202
          Inner Join A0202_Prod_Group_Desc A0202
                       On A0202.Product_Group_Id = T0202.Product_Group_Id
          Where Starting_Product_Group_Id = 50045
         ) As Detail(Starting_Product_Group_Id
                            ,Product_Group_Id
                            ,Parent_Prod_Group_Id
                            ,NSeq
                            ,Product_Group_Desc)
    On Detail.Starting_Product_Group_Id T0200.Product_Id
;

It's not an option to add all tables in the With Recursive and I want to avoid working with a Volatile Table.

ulrich 816 posts Joined 09/09
11 Mar 2012

How about defining a recursive view?

At least the query should be easier to read - where I don't be able to test it.

REPLACE Recursive VIEW T0202_Temp
    (Starting_Product_Group_Id
     ,Product_Group_Id
     ,Parent_Prod_Group_Id
     ,NSeq
    )
   As (Select T0202.Product_Group_Id
                     ,T0202.Product_Group_Id
                     ,T0202.Parent_Prod_Group_Id
                     ,1
          From T0202_Product_Group T0202
          Where Product_Group_End_Date = '9999-12-31'
               And Parent_Prod_Group_Id is null
        Union ALL
          Select T0202_Temp.Starting_Product_Group_Id
                     ,T0202.Product_Group_Id
                     ,T0202.Parent_Prod_Group_Id
                     ,NSeq + 1
           From T0202_Product_Group T0202
           Inner join T0202_Temp T0202_Temp
                       On T0202.Parent_Prod_Group_Id = T0202_Temp.Product_Group_Id
           Where T0202.Product_Group_End_Date = '9999-12-31'
                And NSeq < 10
          )
         Select T0202.Starting_Product_Group_Id
                    ,T0202.Product_Group_Id
                    ,T0202.Parent_Prod_Group_Id
                    ,T0202.NSeq
                    ,A0202.Product_Group_Desc
                    ,T0200.*
          From T0202_Temp T0202
                    Inner Join 
                   A0202_Prod_Group_Desc A0202
                       On A0202.Product_Group_Id = T0202.Product_Group_Id
                    Inner join
                    T0200_Product T0200
                         On T0202..Starting_Product_Group_Id T0200.Product_Id
          Where T0202.Starting_Product_Group_Id = 50045
; 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Koentje 23 posts Joined 09/04
12 Mar 2012

Ulrich,

thank you for this quick response.
Another solotion could be a volatile table... but it's a more general question.

How to combine a With Recursive part in a bigger query, as part of a subselect in an INNER of LEFT OUTER JOIN, without using volatile tables, recursive views...?

ulrich 816 posts Joined 09/09
12 Mar 2012

I guess the thing is to move the with recusive at the top and use it as "normal" table in the select afterwards.

It is a pre definition - so something like 

With recursive T0202_Temp
    (Starting_Product_Group_Id
     ,Product_Group_Id
     ,Parent_Prod_Group_Id
     ,NSeq
    )
   As (Select T0202.Product_Group_Id
                     ,T0202.Product_Group_Id
                     ,T0202.Parent_Prod_Group_Id
                     ,1
          From T0202_Product_Group T0202
          Where Product_Group_End_Date = '9999-12-31'
               And Parent_Prod_Group_Id is null
        Union ALL
          Select T0202_Temp.Starting_Product_Group_Id
                     ,T0202.Product_Group_Id
                     ,T0202.Parent_Prod_Group_Id
                     ,NSeq + 1
           From T0202_Product_Group T0202
           Inner join T0202_Temp T0202_Temp
                       On T0202.Parent_Prod_Group_Id = T0202_Temp.Product_Group_Id
           Where T0202.Product_Group_End_Date = '9999-12-31'
                And NSeq < 10
          )
Select T0202.Starting_Product_Group_Id
                    ,T0202.Product_Group_Id
                    ,T0202.Parent_Prod_Group_Id
                    ,T0202.NSeq
                    ,A0202.Product_Group_Desc
                    ,T0200.*
          From T0202_Temp T0202
                    Inner Join
                   A0202_Prod_Group_Desc A0202
                       On A0202.Product_Group_Id = T0202.Product_Group_Id
                    Inner join
                    T0200_Product T0200
                         On T0202..Starting_Product_Group_Id T0200.Product_Id
          Where T0202.Starting_Product_Group_Id = 50045
;

This should work with outer joins as well.

 


feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

beginner05 3 posts Joined 04/13
02 Apr 2013

Hi, i have almost a same sort of question, but a bit more detailed one. Can anyone just tell me how to run the following query without using recursive view or temp tables. All i want to do is to merge the recursive part and traditional sql part in a single sql.
Currently with following query i am having a syntax error which is
Failed 6926: definitions, views, triggers or stored procedures
 
WITH RECURSIVE dealer_part
( dealer_key, dealer_parent_key, depth ) as
(
SELECT dealer_key, dealer_parent_key, 1 as depth
FROM dim_dealers
WHERE dealer_parent_key = -99
AND hierarchy_level = 1
UNION ALL
SELECT dim_dealers.dealer_key, dim_dealers.dealer_parent_key, dealer_part.depth + 1as depth
FROM dealer_part, dim_dealers
WHERE dealer_view.dealer_key = dim_dealers.dealer_parent_key
AND dealer_view.depth < 30
)
select dealer_key
,case when myflag = -999
then 1
else 0
end as bottom_flag
,depth as hierarchy_level
from (
SELECT dp.dealer_key, coalesce(jd.dealer_key,-999) as myflag, dp.depth
FROM dealer_part dp
left join (
select dealer_key
from dim_dealers
where dealer_key in (
select dealer_parent_key
from dim_dealers
group by dealer_parent_key
)
) jd
on dp.dealer_key = jd.dealer_key
) a

thanks in advance
 

dnoeth 4628 posts Joined 11/04
02 Apr 2013

You have to create a recursive view first and then a second view on top of it.
Did you use the correct syntax CREATE RECURSIVE VIEW instead of CREATE VIEW?
Dieter

Dieter

beginner05 3 posts Joined 04/13
03 Apr 2013

thanks for your prompt response, actually it works fine with recursive view but i am trying to avoid the use of recursive view and i want to sum up the whole logic in a single query. All i want to know is, is there any possibility to do it in a way i want it to be done... if yes then what is it???

dnoeth 4628 posts Joined 11/04
03 Apr 2013

Your query should run as-is, simply replace the wrong name "dealer_view" to "dealer_part".
 
Dieter
 

Dieter

beginner05 3 posts Joined 04/13
04 Apr 2013

Opzz! my bad... Anyways thanks a lot for your time!!!

LALIT2504 2 posts Joined 04/14
30 Apr 2014

Hi guys,
 I have issues regarding recursive queries...
1)  can we create a view over a recursive view in teradata.
2) and I have to implement a view which has two recursive queries which are dependent on derived tables..
 
please suggest solution asap
thanks in advance. 

ASCHARAN 10 posts Joined 09/15
24 May 2016

Hi Guys,
I want to know if there is anyway i can use WITH and WITH RECURSIVE in a single sql. I have a requirement for something like 

-- To convert the below result 

 

question_id    element_id

1              7

1              8

2              9

3              10

3              11

3              12

 

-- to

 

question_id    element_id

1                     7,8

2                     9

3                    10,11,12.

 

To achive this i can create a Volatile table and use with recusrive as below.

 

CREATE VOLATILE TABLE vt_temp AS (

 SELECT

   question_id                     

   ,element_id                                          

   ,ROW_NUMBER() OVER (PARTITION BY question_id  ORDER BY element_id ASC) AS rn

FROM VT

) WITH DATA PRIMARY INDEX(question_id) ON COMMIT PRESERVE ROWS;

 

WITH RECURSIVE rec_test (question,element,LVL)

   AS

   (

    SELECT question_id,element_id, 1

    FROM vt_temp

    WHERE rn = 1

    

    UNION ALL

    

    SELECT  question_id, TRIM(element) || ', ' || TRIM(element_id), LVL+1

    FROM vt_temp INNER JOIN rec_test 

    ON question_id = question

   AND vt_temp.rn = rec_test.lvl+1

   

   )

   

SELECT TRIM(question) AS question_id , TRIM(element) AS element_id

FROM rec_test

QUALIFY RANK() OVER(PARTITION BY question ORDER BY LVL DESC) = 1;

 

But i want to achive this with out using Volatile table/table of any sort. The best way according to my requirement would be to use WITH clause. So, is there anyway i can create vt_temp using WITH while the WITH RECURSIVE can be used in the same sql as something like below.

 

WITH  RECURSIVE vt_temp (question_id,element_id,rn)   AS (

 SELECT

   question_id                     

   ,element_id                                          

   ,ROW_NUMBER() OVER (PARTITION BY question_id  ORDER BY element_id ASC) AS rn

FROM VT

 

WITH RECURSIVE rec_test (question,element,LVL)

   AS

   (

    SELECT question_id,element_id, 1

    FROM vt_temp

    WHERE rn = 1

    

    UNION ALL

    

    SELECT  question_id, TRIM(element) || ', ' || TRIM(element_id), LVL+1

    FROM vt_temp INNER JOIN rec_test 

    ON question_id = question

   AND vt_temp.rn = rec_test.lvl+1

   

   )

   

SELECT TRIM(question) AS question_id , TRIM(element) AS element_id

FROM rec_test

QUALIFY RANK() OVER(PARTITION BY question ORDER BY LVL DESC) = 1; ----> It does throw an error though.

 

 

Thanks in advance.

 

-- AsCharan

 

 

ASCHARAN 10 posts Joined 09/15
24 May 2016

The VT table in above example is below.

CREATE VOLATILE TABLE VT  (question_id INTEGER, element_id INTEGER

)ON COMMIT PRESERVE ROWS;

 

INSERT INTO VT VALUES (1,7);

INSERT INTO VT VALUES (1,8);

INSERT INTO VT VALUES (2,9);

INSERT INTO VT VALUES (3,10);

INSERT INTO VT VALUES (3,11);

INSERT INTO VT VALUES (3,12);

dnoeth 4628 posts Joined 11/04
25 May 2016

Well, you can do it, remove the 2nd WITH and change the order of CTEs:

WITH RECURSIVE rec_test (question,element,LVL)
   AS
   (
    SELECT question_id,cast(element_id as varchar(1000)), 1
    FROM vt_temp
    WHERE rn = 1
    
    UNION ALL
    
    SELECT  question_id, TRIM(element) || ', ' || TRIM(element_id), LVL+1
    FROM vt_temp INNER JOIN rec_test 
    ON question_id = question
    AND vt_temp.rn = rec_test.lvl+1
   )
   
,vt_temp (question_id,element_id,rn)   AS (
 SELECT
   question_id   
   ,element_id
   ,ROW_NUMBER() OVER (PARTITION BY question_id  ORDER BY element_id ASC) AS rn
FROM VT
) 
   
SELECT TRIM(question) AS question_id , TRIM(element) AS element_id
FROM rec_test
QUALIFY RANK() OVER(PARTITION BY question ORDER BY LVL DESC) = 1; ----> It does throw an error though.

 
But, this is way less efficient as the optimizer repeats the ROW_NUMBER for each recursion .level

Dieter

ASCHARAN 10 posts Joined 09/15
31 May 2016

Thanks Dieter. It worked.

You must sign in to leave a comment.