Column Alias in Case Statemnt
-
I'm writing a query for results from a view to get a list of addresses. To calculate the distance from the provided zip code and the customer, I use a user defined function. I want to dynamically choose the ordering with a case statement, but every time I call a column alias in the case statement it says it is an invalid column name. Is it possible to use a case statement on a column alias?
Here is an example of what i'm trying to do.
declare @zip varchar(5)
declare @sortby char(1)
select
userid,
name,
address,
city,
state,
zipcode,
udfGetDistance(@zip, zipcode) as "CustDistance"
from
customers
where "CustDistance" < 20
order by
case
when @sortby = '1' then
"CustDistance"
when @sortby is null then
UserID
end
-
I know that theoretically, I could use dynamic SQL to create the ordering portion. I was just wondering if I could do it another way.
-
You'll need to put the UDF in as the result of the case statement rather than the column alias name...
Also know that you can press SHIFT+ENTER rather than ENTER to get your code formatted without the ugly spaces between your code
declare @zip varchar(5)
declare @sortby char(1)
select
userid,
name,
address,
city,
state,
zipcode,
udfGetDistance(@zip, zipcode) as "CustDistance"
from
customers
where "CustDistance" < 20
order by
case
when @sortby = '1' then
udfGetDistance(@zip, zipcode) as "CustDistance"
when @sortby is null then
UserID
end
-
Another way is to use the position number, which for the UDF in this case is 7. Be aware that this method is not recommended, because if you change the SELECT list, your sort will be wrong if not adjusted accordingly.
declare @zip varchar(5)
declare @sortby char(1)
select userid
, name
, address
, city
, state
, zipcode
, udfGetDistance(@zip, zipcode) as "CustDistance"
from customers
where "CustDistance" < 20
order by
case
when @sortby = '1' then "CustDistance"
when @sortby is null then 7 -- UserID
end
-
Thanks guys, I appriciate the help.
-
I'd be careful with the UDF - you may end up forcing a cursor implicitly, because it will be called for each output row. So if you scan a table of zip codes, you'll end up doing many times. And it may repeat by having it in the order by clause because the case statement.
So I really would check this is the best solution...
-
Thanks for the help. I think i'm going to use dynamic sql in my stored procedure to generate the where clause based on the variables provided. You're right, when I use it in the where clause, it would have to call the UDF again. Since my UDF calculates the zip distance by referencing my zip codes table, when I call it again in the where clause it takes forever to execute. The only reason I'm using the UDF to find the distance is because that is the only solution I found online. If there a more efficient way to calculate zip distance?
-
Could you post the code of your UDF? Is it a simple procedure using a lookup table and some division? For a small app a while ago that worked with bus stops and walking between them, etc we generated a large table with the distances pre-calculated. Whilst the generation of the table took a little while, it saved us calculating things on the fly and allowed very efficient select statements with an index on the distance column. Perhaps that is your best bet in this case?
-
I have a zipcode database that's been cleaned up a bit to fit my needs. It contains 42118 zipcodes. A table of distances between zip code pairs will require C(42118,2) = 42118!/(42116!*2!) = 886,941,903 rows.
Assuming you use
CREATE TABLE zipDistance
(
fromZip int -- assume leading zeros if zip < 5 digits
, toZip int
, distance smallint -- rounded to nearest mile
, PRIMARY KEY CLUSTERED (fromZip, toZip)
)
as your table definition, you would need about 9 GB just for the data, not including any overhead or index space. But it would speed up your queries.
If you chose the precalculated table method, consider using a separate database just for this table. It will make for more efficient backups, since you wouldn't need to keep backing up the same data over and over.
-
Hmm 9GB - impressive amount of space
-
Wow...9 gigs burns a lot. I think i'll stick to the UDF for now. The distances are only of limited use. Because not everybody lives in the center of their zipcode, and i'm pretty sure that less of them will be crow's and can fly a straight line to their destination.
-
we may still be able to eliminate the need for your UDF if you post its code?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply