Case statement - 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

General SQL Server Forums

New to SQL Server Programming

Case statement

Author

Topic

j0shua
Starting Member

40 Posts

Posted - 2009-01-27 : 06:26:35

Hi is there a way for multiple statements when using case statement?
Here is my statement

ORDER BY CASE @sortedby

WHEN 'account' THEN [account]
WHEN 'code' THEN [code]
WHEN 'source' THEN [source]
WHEN 'reference'THEN [reference]

END

but i want to add something like this. So that it would arrange per reference then per source. Is this possible in sql thanks..
WHEN 'reference'THEN [reference],[source]

i always get an error.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 06:31:33

use like this & try
ORDER BY CASE @sortedby
WHEN 'reference'THEN 0
WHEN 'source' THEN 1
else 2
END

j0shua
Starting Member

40 Posts

Posted - 2009-01-27 : 06:47:18

what i mean is like this.. is there a work around for this?

ORDER BY CASE @sortedby

WHEN 'account' THEN [account], [source]
WHEN 'code' THEN [code], [source]
WHEN 'source' THEN [source]
WHEN 'reference'THEN [reference], [source]

END

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-27 : 06:56:48

If these 'account', 'code' ,'source' ,'reference' are columns or aliases to the columns it can be done

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 06:59:35

then u have to write the individual case statement for every record i think so.....

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-27 : 07:05:47

try this

ORDER BY CASE @sortedby

WHEN 'account' THEN [account]
WHEN 'code' THEN [code]
WHEN 'source' THEN [source]
WHEN 'reference'THEN [reference]
END,[source]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 08:47:50

quote:
Originally posted by j0shua

Hi is there a way for multiple statements when using case statement?
Here is my statement

ORDER BY CASE @sortedby

WHEN 'account' THEN [account]
WHEN 'code' THEN

WHEN 'source' THEN [source]
WHEN 'reference'THEN [reference]

END

but i want to add something like this. So that it would arrange per reference then per source. Is this possible in sql thanks..
WHEN 'reference'THEN [reference],[source]

i always get an error.

for that you need to split order by and use separate case something like
[code]
ORDER BY CASE @sortedby

WHEN 'account' THEN [account]
WHEN 'code' THEN [code]
WHEN 'reference'THEN [reference]
...END,
CASE @sortedby
WHEN 'account' THEN [source]
ELSE 1
end
...

j0shua
Starting Member

40 Posts

Posted - 2009-01-27 : 21:45:44

WOW thanks!! this is new to me!! lots of thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 01:55:55

welcome

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 02:21:03

hi visakh,
just clarification, raky code is better than ur code i think so.......
(performance)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 02:57:22

raky's code is not equivalent to what i posted. he's always including source field in order by wheres according to OP he needs to include it only for condition 'reference'

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-28 : 04:14:30

quote:
Originally posted by visakh16

raky's code is not equivalent to what i posted. he's always including source field in order by wheres according to OP he needs to include it only for condition 'reference'

Hi Visakh,

Have u seen the post made by j0shua at 01/27/2009 : 06:47:18 ??

Based on that i sent the case statment in order by clause....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources