Recursive SQL

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
itgiAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
itgiAuthor Commented:
Can you give me some approach
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
ocgstylesCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ocgstyles,

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


Kent
0
 
srielauCommented:
Actually it is, and Keith's query should work on DB2 for LUW.
0
 
itgiAuthor Commented:
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
 
sachinwadhwaCommented:
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
 
itgiAuthor Commented:
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
 
srielauCommented:
Which platform ar eyou on? Only DB2 for LUW supports recursion.
0
 
ocgstylesCommented:
change this line:
where c.USERID01 = s.USERID01

to:
where c.USERID01 = s.USERID02

- Keith
0
 
itgiAuthor Commented:
My Data Base is DB2 on iSeries server. If it dont support to Recursive Query what should be different approach
0
 
srielauCommented:
The comment from KDO.
That is "do it on foot" using a stored procedure.
0
 
itgiAuthor Commented:
any sample or code please
0
 
sachinwadhwaCommented:
which db2 version ?
0
 
itgiAuthor Commented:
V5R3MO DB2 FOR iSeries
0
 
sachinwadhwaCommented:
This is the version of iSeries, we need version of DB2

run db2level command post the output.
0
 
tliottaCommented:
Note that DB2 on iSeries version _is_ the version of the OS. There is no db2level command.

Tom
0
 
tliottaCommented:
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
 
itgiAuthor Commented:
what should be different approach on same version
0
 
tliottaCommented:
itqi:

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

Tom
0
 
itgiAuthor Commented:
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
 
tliottaCommented:
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
 
itgiAuthor Commented:
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
 
tliottaConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.