IF/CASE for WHERE clauses - 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

IF/CASE for WHERE clauses

Author

Topic

kaukana77
Starting Member

2 Posts

Posted - 2007-11-28 : 10:04:54

I am trying to wrap my WHERE clause with an IF or a CASE but cannot seem to get it to work. This is what I am trying:

WHERE
CASE
WHEN EVENT_TYPE='d' THEN
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000
ELSE
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000 AND act_type.act_type_sc <> 'CLOSURE'
END
ORDER BY B.incident_id DESC, act_reg.act_reg_id DESC

Basically I want to run a different WHERE clause based on a value (EVENT_TYPE). The error message I am getting is:
Incorrect syntax near '='.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-28 : 10:21:46

I think this is due to EVENT_TYPE not being deterministic. If you have two rows, one type 'd' and one not, how will the compiler know which to use?

You need to re-think your process.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 10:26:54

have you tried an OR like this?

WHERE (EVENT_TYPE='d' OR act_type.act_type_sc <> 'CLOSURE')
AND (link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND (link_rsn.link_rsn_sc = 'CHANGE') AND (B.incident_id > 10000000)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-28 : 10:27:45

i think of it more like the 'case' is trying to return you a value (as in the result of an expression), what you're tryoing to return is a few statements instead. given that the only difference is really only act_type_sc i'd look at something more like this....

(not tested obviously, but try it as a starting point)


WHERE 
    link_inc.incident_id = 10000005 
and B.incident_id <> 10000005 
and link_rsn.link_rsn_sc = 'CHANGE'
and B.incident_id > 10000000
and act_type.act_type_sc <> case when EVENT_TYPE <> 'd' then 'CLOSURE' else null end

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 10:51:54

elan the case you used seems to test act_type.act_type_sc <> null, which is dependant on the ANSI_NULLS setting. that may be correct, but kaukana make sure to test. i thought the intent was if EVENT_TYPE='d' don't bother checking act_type.act_type OR if EVENT_TYPE<>'d' act_type.act_type must <> 'CLOSURE'

kaukana77
Starting Member

2 Posts

Posted - 2007-11-28 : 15:42:17

Thanks to everyone for responding - this is what worked best for me:

WHERE (EVENT_TYPE='d' OR act_type.act_type_sc <> 'CLOSURE') AND ((link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources