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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SELECT with conditions

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?
Go to Top of Page

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 longitude
FROM 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.state
WHERE A.disabled = 0
ORDER BY dateEntered
Go to Top of Page

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 longitude
FROM 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.state
WHERE A.disabled = 0
ORDER 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 Table
productID = unique identifier
dateEntered = date the incident was uploaded
aZip = the incident zip code
aCity = the incident city
aCounty = the incident county
aState = the incident state
disabled = if incident is disabled

ZipCodes = Zip Code Table
latitude = latitude associated with zip
longitude = longitude associated with zip
zip = zip code
city = city associated with zip
county = county associated with zip
state = state associated with zip
Go to Top of Page
   

- Advertisement -