Case and Logical comparison - 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

Transact-SQL (2000)

Case and Logical comparison

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-05-01 : 23:23:53

I dont know if there is any way to just ask a question without needing my whole "story", which I would glady give for some help. But I dont want to bombard everyone with all of it if the answer is simply "no your crazy". :)

I was wondering if I could do something like the following:

CASE WHEN SM.PrimaryStmtOrder1ENUM = 0 Then ''
WHEN SM.PrimaryStmtOrder1ENUM = 1 Then SH.BalanceTotal = 0
WHEN SM.PrimaryStmtOrder1ENUM = 2 Then SH.BalanceTotal < 0
End As PrimarySort1,
CASE WHEN SM.PrimaryStmtOrder2ENUM = 0 Then ''
WHEN SM.PrimaryStmtOrder2ENUM = 1 Then SH.BalanceTotal = 0
WHEN SM.PrimaryStmtOrder2ENUM = 2 Then SH.BalanceTotal < 0
End As PrimarySort2,

Basically my end result, is that I need to sort on the BalanceTotal value, but they have a first and then second sort. They can have zero balances first, and then credit balances (hence the two select case statements).

All case statements I have seen have just values for the Then. I have not seen anywhere that I could use a logical expression, so I did not know if it was possible.

Thanks again,
JAdauto

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-02 : 01:04:05

Not sure what you want to do. A case statement delivers a value - can be any type but all parts must deliver the same type.
This is an oreder by claue and you want the results in that order? Pretty sure this isn't what you want but may give you a hint.

CASE WHEN SM.PrimaryStmtOrder1ENUM = 0 Then 1
WHEN SM.PrimaryStmtOrder1ENUM = 1 and SH.BalanceTotal = 0 then 2
WHEN SM.PrimaryStmtOrder1ENUM = 2 and SH.BalanceTotal < 0 then 3
End As PrimarySort1,
CASE WHEN SM.PrimaryStmtOrder2ENUM = 0 Then 1
WHEN SM.PrimaryStmtOrder2ENUM = 1 and SH.BalanceTotal = 0 then 2
WHEN SM.PrimaryStmtOrder2ENUM = 2 and SH.BalanceTotal < 0 then 3
End As PrimarySort2,

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-05-02 : 01:14:14

I LOVE THIS SIGHT AND EVERYONE WHO HAS HELPED ME WHEN I HAVE BEEN IN A JAM!

This is exactly what I was needing.

Thank you so much,
JAdauto

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources