473,581 Members | 2,233 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with the WHILE Statement

2 New Member
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 1042
deepuv04
227 Recognized Expert New Member
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
mvillanu
2 New Member
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

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

Similar topics

46
5120
by: Kingdom | last post by:
In my data base I have a list of componet types e.g. type A - I have 8 off - type B I have 12 off etc. I'm using Set objRS = objDC.Execute("Select DISTINCT Component_Type FROM Parts_Table") to populate a drop down but would like to use several drop downs restricting the contents of each drop down to the records pertaining to one
4
3262
by: James E Koehler | last post by:
I can't get the WHILE statement to work in MySQL. The version of MySQL that I am using is: Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32) running on Windows MX. Here is the relevant section from the manual: 20.1.9.7 WHILE Statement
5
3496
by: WindAndWaves | last post by:
Hi Team The function below searches all the tables in a database. However, if subsearch = true then it searches all the objects listed in a recordset (which are all table names). I thought to be really clever and use : #if subsearch ... #else ..... #end if
11
2185
by: Scott C. Reynolds | last post by:
In VB6 you could do a SELECT CASE that would evaluate each case for truth and execute those statements, such as: SELECT CASE True case x > y: dosomestuff() case x = 5: dosomestuff() case y > x: dosomestuff()
7
2684
by: Steven Bethard | last post by:
I've updated PEP 359 with a bunch of the recent suggestions. The patch is available at: http://bugs.python.org/1472459 and I've pasted the full text below. I've tried to be more explicit about the goals -- the make statement is mostly syntactic sugar for:: class <name> <tuple>: __metaclass__ = <callable>
2
1850
by: Greg Corradini | last post by:
Hello All, A few weeks ago, I wrote two scripts using mx.ODBC on an Access DB. Among other things, both scripts create new tables, perform a query and then populate the tables with data in a dictionary that I've uploaded from elsewhere. These scripts have run hundreds of times in the last few weeks with no problems. But recently they...
6
2345
by: redashley40 | last post by:
This is my first attempt in SQL and PreparedStatement I have add the PreparedStatement and I'm not to sure if I'm doing it correctly. When I do a test run on Choose 1 ,or 2 I get this error. Error - com.mysql.jdbc.Statement here is my code below public class DBAssign { /**
2
2889
by: rookiejavadude | last post by:
I'm have most of my java script done but can not figure out how to add a few buttons. I need to add a delete and add buttong to my existing java program. Not sure were to add it on how. Can anyone help? my script is below. thank you import java.awt.*; //import all java.awt import java.awt.event.*; //import all java.awt.event import...
0
4119
by: RCapps | last post by:
When running the below SQL Query I keep getting the following error: Server: Msg 4924, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table 'zContractDefault'. For some reason it is only returning the first 11 chars of the column name? Any help would be greatly appreciated... This...
2
1303
by: nleake | last post by:
Hey, I've been trying to use Python to help me with some DNA sequencing work. I've figured out how to use Biopython for the importing work; however, I need some python code to help me remove a smaller regulatory sequence (a short string) from a larger DNA sequence (a longer string) recursively so that when one is removed and the subsequent...
0
7868
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8149
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8304
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7899
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8175
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6553
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3827
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1403
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.