By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,385 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Help with the WHILE Statement

P: 2
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!
Mar 19 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
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:
Expand|Select|Wrap|Line Numbers
  1.         with cte as
  2.         (
  3.             select DealerID,BSID
  4.             from Table_Name where DealerID = 1
  5.             union all
  6.             select o.DealerID,o.BSID
  7.             from Table_Name O, Cte C WHERE O.DealerID   = C.BSID 
  8.         )
  9.         SELECT top 1 DealerID,BSID FROM CTE
  10.         order by 2 desc
  11.  
  12.  
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
Mar 20 '08 #2

P: 2
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.
Mar 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.