|
View previous topic :: View next topic
|
| Author |
Message |
jeroznishanth
New User
.jpg)
Joined: 21 Nov 2007
Posts: 8
Location: Bangalore
|
|
|
|
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 |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Basically you want the minimum best_pin which is less than your input ?
what have you tried so far ? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
jeroznishanth
New User
Joined: 21 Nov 2007
Posts: 8
Location: Bangalore
|
|
|
|
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 |
|
|
jeroznishanth
New User
Joined: 21 Nov 2007
Posts: 8
Location: Bangalore
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Jeroz,
Hi, You must be looking for CTE( Common Table Expression ) with recursive query.
Start here
Sushanth |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
