With recursive in a Select

Database
Enthusiast

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.

13 REPLIES
Senior Supporter

Re: With recursive in a Select

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
;

Enthusiast

Re: With recursive in a Select

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...?

Senior Supporter

Re: With recursive in a Select

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.


Re: With recursive in a Select

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

Junior Contributor

Re: With recursive in a Select

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

Re: With recursive in a Select

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???

Junior Contributor

Re: With recursive in a Select

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

Dieter

Re: With recursive in a Select

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

Re: With recursive in a Select

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.