SQLTeam.com | Weblogs | Forums

How to check multiple conditions column based

sql2012

#1

Hi Team,

I have a table from which I need to find out multiple rejection condition.

I have done following but not satisfied and looking for some good alternative.
declare @tbl table(id int identity , check1 float , check2 float , check3 float)

insert into @tbl (check1, check2,check3)
values(10,15,20),(5,9,8),(2,3,10)

select id , case
when check1<5 and check2<10 and check3 <15 then 'check1 is less than 5 and check2 is less than 10 and check3 is less than 15'
when check1<5 and check2<10 then 'check1 is less than 5 and check2 is less than 10'
when check1<5 and check3<15 then 'check1 is less than 5 and check3 is less than 15'
when check2<10 and check3<15 then 'check2 is less than 10 and check3 is less than 15'
when check1<5 then 'check1 is less than 5 '
when check2<10 then 'check2 is less than 10 '
when check3<15 then 'check3 is less than 15 '
else 'ok'
end
from @tbl


#2

I'd say a CASE expression is a good fit for this problem.


#3

I prefer the approach below. At any rate, just for the fun of it, if nothing else:

insert into @tbl (check1, check2,check3)
values(10,15,20),(5,9,8),(2,3,10),(5,9,20),(10,20,0),(1,20,30)

select id, 
     isnull(stuff(nullif(check1_message + check2_message + check3_message, ''), 1, 5, ''), 'ok')
     as message
from @tbl
cross apply (
    select 
        case when check1 <  5 then ' and check1 is less than 5'  else '' end as check1_message,
        case when check2 < 10 then ' and check2 is less than 10' else '' end as check2_message,
        case when check3 < 15 then ' and check3 is less than 15' else '' end as check3_message
) as assign_alias_names

#4

Perfecto
.Thanks.