Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
rmf879
Starting Member
2 Posts |
Posted - 2008-08-19 : 18:34:36
|
I am having trouble creating an SQL SELECT statement with conditions. I'm not even sure if it can be done. This is what I am looking for. I have a table where users insert incidents. They are required to put in the state and the county, but the city and zip are optional for necessary reasons. I also have a table that contains all of the zip codes with the cities, counties, states, latitudes, and longitudes that are associated with each zip code. We are creating a mapping system and I need to pull zip codes based on the incident information. I need to pull the date/time the incident was uploaded and the unique incident ID from the incident table. I also need to pull the latitude and longitude associated with that incident. I have tried a few variations of things and have not had any success. I basically want a SELECT statement that pulls the uniqueID and the date/time and then checks the incident table to see if the zip code is not null and if not pull the latitude and longitude based on the zip, if it is null I want to check the city and see if it is not null and pull the latitude and longitude based on the city and state, and if they are both null then I want the final selection to pull the latitude and longitude based on the county and stated.Here is the last string that I tried:SQLRI = "SELECT productID, dateEntered, aZip, aCity, aCounty, aState, (CASE WHEN aZip <> '' THEN (SELECT latitude AS latitude, longitude AS longitude FROM ZipCodes WHERE zip = 'aZip') ELSE WHEN aZip IS NULL OR aZip = '' THEN (SELECT latitude AS latitude, longitude AS longitude FROM ZipCodes WHERE city = 'aCity' AND state = 'aState') ELSE WHEN aZip IS NULL AND aCity IS NULL THEN (SELECT latitude AS latitude, longitude AS longitude FROM ZipCodes WHERE county = 'aCounty' AND state = 'aState') END) FROM AProTab WHERE disabled = 0 ORDER BY dateEntered"Set dbri = conn.Execute(SQLRI)Anyone have any suggestions on how to make this work or if this is even possible?It is running SQL Server 2000 for a website built in ASP.Ronnie |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-20 : 00:31:20
|
can you please provide table structures? |
 |
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2008-08-20 : 08:47:32
|
Try something like:SELECT A.productID ,A.dateEntered ,A.aZip ,A.aCity ,A.aCounty ,A.aState ,COALESCE(Z1.latitude, Z2.latitude, Z3.latitude) AS latitude ,COALESCE(Z1.longitude, Z2.longitude, Z3.longitude) AS longitudeFROM AProTab A LEFT JOIN ZipCodes Z1 ON A.aZip = Z1.zip LEFT JOIN ZipCode Z2 ON A.aCity = Z2.city AND A.aState = Z2.state LEFT JOIN ZipCode Z3 ON A.aCounty = Z3.county AND A.aState = Z3.stateWHERE A.disabled = 0ORDER BY dateEntered |
 |
|
rmf879
Starting Member
2 Posts |
Posted - 2008-08-20 : 19:06:13
|
quote: Originally posted by Ifor Try something like:SELECT A.productID ,A.dateEntered ,A.aZip ,A.aCity ,A.aCounty ,A.aState ,COALESCE(Z1.latitude, Z2.latitude, Z3.latitude) AS latitude ,COALESCE(Z1.longitude, Z2.longitude, Z3.longitude) AS longitudeFROM AProTab A LEFT JOIN ZipCodes Z1 ON A.aZip = Z1.zip LEFT JOIN ZipCode Z2 ON A.aCity = Z2.city AND A.aState = Z2.state LEFT JOIN ZipCode Z3 ON A.aCounty = Z3.county AND A.aState = Z3.stateWHERE A.disabled = 0ORDER BY dateEntered
I tried that code and this is what I got:Response object error 'ASP 0251 : 80004005' Response Buffer Limit Exceeded /newlayout/index.asp, line 0 Execution of the ASP page caused the Response Buffer to exceed its configured limit. Below is the table structure as requested:AProTab = Incidents TableproductID = unique identifierdateEntered = date the incident was uploadedaZip = the incident zip codeaCity = the incident cityaCounty = the incident countyaState = the incident statedisabled = if incident is disabledZipCodes = Zip Code Tablelatitude = latitude associated with ziplongitude = longitude associated with zipzip = zip codecity = city associated with zipcounty = county associated with zipstate = state associated with zip |
 |
|
|
|
|
|
|