Help with the WHILE Statement

[mvillanu]

Hi All,

I'm having trouble trying to figure out how to use the WHILE statement and I was wondering if anyone can help me. Here is what I would like to do. I have a table with dealer ID and BSID, the dealer ID is our active dealer and the BSID is when the dealer is purchased by another dealer. Once this happens than the BSID becomes the new Dealer ID on a separate line. What I would like to do is test what should be the most current dealer ID because any dealer could be purchase multiple times! E.G.:

Table

DealerID BSID
01 02 * dealer 01 was bought out by dealer 02 (dealer 02 is the new dealer #)
02 03 *dealer #03 is new dealer ID
03 04

So ultimately the dealer number I want to reach is dealer # 04. Does this make any sense? Your help is much appreciated!

[deepuv04]

Hi All,

I'm having trouble trying to figure out how to use the WHILE statement and I was wondering if anyone can help me. Here is what I would like to do. I have a table with dealer ID and BSID, the dealer ID is our active dealer and the BSID is when the dealer is purchased by another dealer. Once this happens than the BSID becomes the new Dealer ID on a separate line. What I would like to do is test what should be the most current dealer ID because any dealer could be purchase multiple times! E.G.:

Table

DealerID BSID
01 02 * dealer 01 was bought out by dealer 02 (dealer 02 is the new dealer #)
02 03 *dealer #03 is new dealer ID
03 04

So ultimately the dealer number I want to reach is dealer # 04. Does this make any sense? Your help is much appreciated!

Hi All,

I'm having trouble trying to figure out how to use the WHILE statement and I was wondering if anyone can help me. Here is what I would like to do. I have a table with dealer ID and BSID, the dealer ID is our active dealer and the BSID is when the dealer is purchased by another dealer. Once this happens than the BSID becomes the new Dealer ID on a separate line. What I would like to do is test what should be the most current dealer ID because any dealer could be purchase multiple times! E.G.:

Table

DealerID BSID
01 02 * dealer 01 was bought out by dealer 02 (dealer 02 is the new dealer #)
02 03 *dealer #03 is new dealer ID
03 04

So ultimately the dealer number I want to reach is dealer # 04. Does this make any sense? Your help is much appreciated!

Hi,
You can use recursive query instead of while loop

The following query is from the example give above:
[code=sql]
with cte as
(
select DealerID,BSID
from Table_Name where DealerID = 1
union all
select o.DealerID,o.BS ID
from Table_Name O, Cte C WHERE O.DealerID = C.BSID
)
SELECT top 1 DealerID,BSID FROM CTE
order by 2 desc

[/code]

can you clear one thing, can a dealer is purchased by the old dealer i mean


Table

DealerID BSID
01 02 * dealer 01 was bought out by dealer 02 (dealer 02 is the new dealer #)
02 03 *dealer #03 is new dealer ID
03 04
04 02 ( * can this possible...? )

Thanks

[mvillanu]

Thanks for the reply! To answer your question, No. If an old dealer is purchased, their dealer Id will never be reactivated, they will simply be given a new number if they come back into play. I think your query will work for me, but it's taken me a little bit longer to understand what it is really doing.