Case statement with multiple conditions

Obviously the below Case expression does not work. I have conditions on two
fields. If it is true then proceed.
CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
GETDATE()
ELSE
tbl.end_dt
END
Please Help,
Culammy bad,
Solution:
CASE WHEN su.deleted_flag = 1
THEN
CASE WHEN su.end_dt IS NULL THEN
CONVERT(VARCHAR(11), GETDATE() - 1, 101)
ELSE su.end_dt
END
ELSE
su.end_dt
END
"culam" wrote:

> Obviously the below Case expression does not work. I have conditions on t
wo
> fields. If it is true then proceed.
>
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam|||> Obviously the below Case expression does not work. I have conditions
> on two fields. If it is true then proceed.
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam
Apart from = NULL, this should work like you expect it to.
Lasse Vgsther Karlsen
http://usinglvkblog.blogspot.com/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x2A42A1C2

Case statement in Order by clause

Hi

I need a case statement with in the order clause,

There are fields like below

Feild A FieldB LevelA LevelB

xxxx xxxx

xxxx xxxx

xxxx xxxx 3 2

xxxxx xxxx 2 1

xxxxxx xxxxx 1 1

I need to sort LevelA and LevelB first leaving out the null or blank values in the top rows

Feild A FieldB LevelA LevelB

xxxx xxxx 1 1

xxxx xxxx 2 1

xxxx xxxx 3 2

xxxxx xxxx

xxxxxx xxxxx

Thanks

like this:

Code Snippet

create table #t (FieldA varchar(10), FieldB varchar(10), LevelA int, LevelB int)

insert into #t

select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', 3, 2

union all select 'xxxxx', 'xxxx', 2, 1

union all select 'xxxxxx', 'xxxxx', 1, 1

union all select 'xxxxxx', 'xxxxx', 1, 2

union all select 'xxxxxx', 'xxxxx', 1, 3

select *

from #t

order by case when LevelA is not null then 1 else 9 end,

LevelA,

case when LevelB is not null then 1 else 9 end,

LevelB

|||

You can use a CASE expression like below:

order by coalesce(LevelA, power(2., 31)-1), coalesce(LevelB, power(2., 31)-1)

|||Umachandar,

Some readers might why you said "CASE expression," so I'll remark that COALESCE(a,b) is basically the same as CASE WHEN a IS NULL THEN b ELSE a END.

Sowree,

I think the suggestion below will work, and here I've added the requirement that blank values appear last. (If or are number types, they can't be blank, and this code will incorrectly put values of 0 last, because '' is implicitly converted to 0 in the comparison.)

ORDER BY
CASE WHEN a IS NULL OR a = '' THEN 1 ELSE 0 END,
a,
CASE WHEN b IS NULL OR b = '' THEN 1 ELSE 0 END,
b

Your question isn't entirely clear, so you may have to adapt the suggestions to your exact need.

Steve Kass
Drew University
http://www.stevekass.com