Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Recursive SQL

Posted on 2006-06-05
28
Medium Priority
?
2,538 Views
Last Modified: 2012-05-05
My table in DB2 is like that

SR_NO      EMP1       EMP2
--------     -------      -------
01           JOHAN     KITTY
02           JOHAN     PREETY
03           KITTY      PETER
04           KITTY      ALBERT
05           PREETY   AHMAD
06           PETER     VICTOR

THERE IS A RELATION SHIP BETWEEN EMP1 AND EMP2 MY NEED IS : -

A) IF I GIVE A SEARCH AND PASS   "KITTY" AS PARAMERTER THE OUTPUT SHOULD BE : -

KITTY
PETER
ALBERT
VICTOR

B) IF I GIVE CONDITION "JOHAN"  : -

JOHAN
PREETY
AHMAD
KITTY
PETER
ALBERT
VICTOR

C) B) IF I GIVE CONDITION "PETER"  : -

PETER
VICTOR


ANY ONE SUGGEST ME SQL FOR THE SAME
0
Comment
Question by:itgi
  • 9
  • 5
  • 3
  • +3
25 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 16831991
Hi itgi,

While recursive SQL is possible, it's not trivial and probably beyond the scope of the quick advice usually given in this forum.

I would suggest that you write the code as a stored procedure.  Then simply loop on the table looking for matches from the previous pass.  When there are no more matches the loop exits.


Good Luck!
Kent
0
 

Author Comment

by:itgi
ID: 16832011
Can you give me some approach
0
 
LVL 5

Expert Comment

by:ocgstyles
ID: 16832540
i don't have much experience with recursive SQL, but I remember at least this much when I read about it.  it looks something this....

with temp (sr_no, emp1, emp2) as
   (select sr_no, emp1, emp2
      from mytable
      where emp1='KITTY'

       union all

    select c.sr_no, c.emp1, c.emp2
      from mytable c, temp s
      where c.emp1 = s.emp2
) select * from temp

will that give you what you need?

- Keith
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 16834775
Hi ocgstyles,

That's not recursive SQL.  It simply joins (via union) 1 level of nesting.


Kent
0
 
LVL 2

Expert Comment

by:srielau
ID: 16837902
Actually it is, and Keith's query should work on DB2 for LUW.
0
 

Author Comment

by:itgi
ID: 16840553
I am using :-

with temp ( USERID01, USERID02) as

   (select  USERID01, USERID02

      from ITGDTA.ZESC  

      where USERID01='DFOM'


       UNION ALL

    select  USERID01, USERID02

      from ITGDTA.ZESC c, temp s

      where c.USERID01 = s.USERID01

) select * from temp


This is my actual sql, but error is :-

SQL State: 42836
Vendor Code: -346
Message: [SQL0346] Recursion not allowed for common table expressions. Cause . . . . . :   The common table expression specified is not valid. The subselect for table TEMP refers to itself. Recursive common table expressions are not allowed. Recovery  . . . :   Change the common table expressions to refer to a table that exists or a common table expression that has already been defined. Try the request again.

kindly suggest me what is wrong
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16840733
best book (free) to learn recursive sql is:

DB2 8.2 SQL Cookbook

download it from http://mysite.verizon.net/Graeme_Birchall/id1.html
0
 

Author Comment

by:itgi
ID: 16841021
I am using the link suggested there is same SQL with little difference what is suggested by   Keith.

same error is coming

SQL State: 42836
Vendor Code: -346
Message: [SQL0346] Recursion not allowed for common table expressions. Cause . . . . . :   The common table expression specified is not valid. The subselect for table TEMP refers to itself. Recursive common table expressions are not allowed. Recovery  . . . :   Change the common table expressions to refer to a table that exists or a common table expression that has already been defined. Try the request again.

Processing ended because the highlighted statement did not complete successfully
0
 
LVL 2

Expert Comment

by:srielau
ID: 16841089
Which platform ar eyou on? Only DB2 for LUW supports recursion.
0
 
LVL 5

Expert Comment

by:ocgstyles
ID: 16841315
change this line:
where c.USERID01 = s.USERID01

to:
where c.USERID01 = s.USERID02

- Keith
0
 

Author Comment

by:itgi
ID: 16841415
My Data Base is DB2 on iSeries server. If it dont support to Recursive Query what should be different approach
0
 
LVL 2

Expert Comment

by:srielau
ID: 16841451
The comment from KDO.
That is "do it on foot" using a stored procedure.
0
 

Author Comment

by:itgi
ID: 16841462
any sample or code please
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16841537
which db2 version ?
0
 

Author Comment

by:itgi
ID: 16841618
V5R3MO DB2 FOR iSeries
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16842378
This is the version of iSeries, we need version of DB2

run db2level command post the output.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16846991
Note that DB2 on iSeries version _is_ the version of the OS. There is no db2level command.

Tom
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16847492
From the V5R3 InfoCenter:

"If a fullselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive table expression. Recursive common table expressions are not supported in DB2 UDB for iSeries."

To get such CTE recursion, V5R4 is required.

Tom
0
 

Author Comment

by:itgi
ID: 16848990
what should be different approach on same version
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16855134
itqi:

AFAIK, original recommendation of a basic stored proc is the way to go.

Tom
0
 

Author Comment

by:itgi
ID: 16892403
Basicaly I am a java programer i do not have expertise on store prosudure any body help me to give code it is very urgent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16914438
itqi:

Since no one else has tackled your latest question, I'll add some thoughts.

To start, it begins with a SQL 'CREATE PROCEDURE' statement and builds from there. Any SQL reference will document details.

However, I have no idea if it will do you any good. It sounds as if this is not your area. You simply might not have the necessary database authorities to make this kind of change to your system. Creating a stored proc is a change to the database, not a change to your program. As such, it should be done by your DBA (or the authorized administrator of the application that holds the database).

Even if you have the authorities needed, you might not want to accept the risks. Adding stored procs opens you to database (and system) security issues that you might want to hand off to whomever is appropriate.

Tom
0
 

Author Comment

by:itgi
ID: 16917597
I have to do at my own in this project. My application is running fine but thee is one report where I have to show data on the basis of Repoting tree. I have given table schema also. I have been try recursive SQl as suggested but it is not supported on my environment that is why i need another approach.


Any one help me it is urgent please.
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 700 total points
ID: 16919352

Hi All,

There's a great article on DB2 recursive SQL here:  

http://www-128.ibm.com/developerworks/db2/library/techarticle/0307steinbach/0307steinbach.html



Sorry about my earlier post.  :)

Kent

0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 700 total points
ID: 16923302
itqi:

If your system is upgraded to the current release of OS/400, you can use recursive SQL. Until then, it seems that a 'stored procedure' is the best choice.

A stored procedure is a procedure that you create (code and compile) and store in the database (i.e., on your AS/400). You can code stored procs in any high-level language or you can code them totally in SQL statements. The SQL CREATE PROCEDURE statement defines the stored proc to the database and either points to the high-level language program or includes the SQL that becomes the stored proc. To get you started, review info in the Information Center for your release of OS/400. For V5R3, the beginning point might be:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafysproeg.htm

If you look there, you should be able to check the navigation panel on the left and determine how similar info can be found for your OS/400 release.

The area you will want to review in detail will be the link for 'Returning Result Sets from Stored Procedures'.

That may be all you need or more questions might come from trying to understand or from errors you run into. Post back as needed.

Tom
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question