Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Loop Queries for DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jeroznishanth

New User


Joined: 21 Nov 2007
Posts: 8
Location: Bangalore

PostPosted: Wed Sep 16, 2009 2:34 pm    Post subject: Loop Queries for DB2
Reply with quote

Hi,

I have an req of DB2-SQL querry.

I have a table which will have

Code:

best_PIN           other_PIN
    2                        3
    3                        4
    4                        5
    5                        6
                   



best pin is the parent and other pin is the child.
I want the higherrchy. I like If I give 6 as input.

I want output which show the top...

since 6->5 -> 4 -> 3 -> 2

my output will be

Code:

best_pin
2

Is there any way to write it in a while loop??

Thanks in advance
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Sep 16, 2009 3:02 pm    Post subject:
Reply with quote

Basically you want the minimum best_pin which is less than your input ?
what have you tried so far ?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Wed Sep 16, 2009 3:05 pm    Post subject: Reply to: Loop Queries for DB2
Reply with quote

the data posted is misleading

the logic is the same as that of a BOM where used
( from a child up to the first level father )

what language will You be using ?
Back to top
View user's profile Send private message
jeroznishanth

New User


Joined: 21 Nov 2007
Posts: 8
Location: Bangalore

PostPosted: Wed Sep 16, 2009 3:07 pm    Post subject:
Reply with quote

Yes.

like my input is 6 whose best fit is 5 whose best fit is 4 .... whose best fit is 2. For 2 there is not best fit. that is the ultimate best_fit for 6.

like that.

Right now I am trying to wirte a loop. its not working.
Back to top
View user's profile Send private message
jeroznishanth

New User


Joined: 21 Nov 2007
Posts: 8
Location: Bangalore

PostPosted: Wed Sep 16, 2009 3:20 pm    Post subject:
Reply with quote

i tried this code

Code:
   --#SET TERMINATOR @
BEGIN ATOMIC
DECLARE BP INT;
DECLARE OP INT;
SET BP=4;
   while ( BP != NULL) DO
   SET OP = BP;
   SET BP = ( SELECT SYS_BEST_PIN FROM INDIA6.PIN_MERGE WHERE SYS_OTHER_PIN = BP)
   END WHILE;
   
   SELECT SYS_BEST_PIN FROM INDIA6.PIN_MERGE WHERE SYS_OTHER_PIN = OP;
   END@


got the error msg

Code:
B21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END" was found following "YS_OTHER_PIN = BP) 
".  Expected tokens may include:  "<delim_semicolon>".  LINE NUMBER=10. 
SQLSTATE=42601

SQL0104N  An unexpected token "END" was found following "YS_OTHER_PIN = BP)
   ".  Expected tokens may include:  "<delim_semicolon>                            ".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>".  The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid. 

 As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>".  This list assumes
the statement is correct to that point. 

 The statement cannot be processed. 

User Response:

Examine and correct the statement in the area of the specified
token. 

 sqlcode :  -104

 sqlstate :  42601



Can some one help me in this?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Wed Sep 16, 2009 3:22 pm    Post subject: Reply to: Loop Queries for DB2
Reply with quote

Quote:
Yes.

yes to what post ??

use differnt values and differnt names ...
to somebody it might look like You were looking for the minimum value

( You show a father child relationship, and casually they are in sequence and the value being sough is the minimum )
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Sep 21, 2009 12:44 am    Post subject:
Reply with quote

Jeroz,

Hi, You must be looking for CTE( Common Table Expression ) with recursive query.

Start here

Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 22, 2009 5:30 pm    Post subject:
Reply with quote

Something like this, but be aware of the risks of recursive programming

Code:
WITH RECURS_TAB (lvl,best_pin, other_pin) as
(SELECT 1, P.BEST_PIN, P.other_pin   FROM INDIA6.PIN_MERGE  P
where p.best_pin = :Hostvar
union all
select R.lvl + 1 ,R.best_pin, P.other_pin from INDIA6.PIN_MERGE P
     , RECURS_TAB R where R.other_pin = P.best_pin
     and r.lvl < 99 -- Just to avoid infinite loop)


select * from recurs_tab R
where r.lvl = (select max(r2.lvl) from recurs_tab R2)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts save SAY var loop. italo_pm CLIST & REXX 3 Sun Sep 04, 2016 3:06 am
No new posts print out the correct info in LOOP? jackzhang75 CLIST & REXX 7 Wed Dec 23, 2015 10:39 pm
No new posts Using different values for Skeleton d... venksiv CLIST & REXX 8 Thu Sep 03, 2015 5:28 pm
No new posts Running queries against OLTP Database A_programmers DB2 3 Tue Jul 14, 2015 4:51 pm
No new posts Issue with Do Loop. santosh_g CLIST & REXX 5 Tue May 12, 2015 9:24 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us