469,126 Members | 1,331 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Help with the WHILE Statement

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
2 945
deepuv04
227 Expert 100+
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
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.

Similar topics

4 posts views Thread by James E Koehler | last post: by
11 posts views Thread by Scott C. Reynolds | last post: by
7 posts views Thread by Steven Bethard | last post: by
2 posts views Thread by Greg Corradini | last post: by
2 posts views Thread by rookiejavadude | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.