Select clause replacement from case to cleaner sql

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 2005 Forums

Transact-SQL (2005)

Select clause replacement from case to cleaner sql

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2010-04-05 : 13:48:31

Hi
I do select ColA,ColB,ColC from myTable

Know ColB is a string. If it is blank ie. '' then must return 0 else must return 1 as well as sort on ColA, ColB. Simple.

So my query would look like:
Select ColA,CASE ColB when '' then 0 else 1 END as ColB,ColC
ORDER BY ColA,CASE ColB when '' then 0 else 1 END

1. So isn't there a simpler way to do a boolean result of a field without using UDF. Surely there is an inbuilt function to do this instead of using a case or IF statement
2. The above code I basically have to reuse the case statement in the select clause and the order by clause. Is there a way to do this cleaner without putting it in a variable and concatenating the sql statement or without putting it in a nested select statement so the parent can just refer to the field to sort on.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-05 : 14:46:20

quote:
Originally posted by adlo
1. So isn't there a simpler way to do a boolean result of a field without using UDF. Surely there is an inbuilt function to do this instead of using a case or IF statement

Not as far as I know. Besides, this is exactly what CASE is designed to do.

quote:
2. The above code I basically have to reuse the case statement in the select clause and the order by clause. Is there a way to do this cleaner without putting it in a variable and concatenating the sql statement or without putting it in a nested select statement so the parent can just refer to the field to sort on.


SELECT ColA, ColB, ColC
FROM (
		SELECT ColA, CASE ColB when '' then 0 else 1 END as ColB, ColC
		FROM Somewhere ) z
ORDER BY ColA, ColB

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 00:19:08

For 1 if colB contains only numeric values and '' then you can use ABS(SIGN(Col)) instead of CASE WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adlo
Posting Yak Master

108 Posts

Posted - 2010-04-06 : 15:09:58

Thought there was easier way but visakh get the prize. :)

DBA in the making gets the prize for not understanding his own sig.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 15:20:18

quote:
Originally posted by adlo

Thought there was easier way but visakh get the prize. :)

So long as you realise that a non-numeric value will cause an error. It's generally a bad idea to use a string variable like this, assuming it will always contain a number.

quote:

DBA in the making gets the prize for not understanding his own sig.

I'm really not sure what to make of that. I'm pretty sure I do understand it, binary too.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources