SELECT with conditions - SQL Server Forums

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?

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources