IF statement within a Where?

[gooddawggy]

I have some simple SQL that I need to dynamically modify the where within the SQL.

Essentially I am trying to get:

Select*
from Stuff
where state = 'AZ'
and case when :input = 'Phx' then Area_code = '502'
else area_code = '480'

have 2 sets of criteria that I need to be used depending on the input. One is default, the other sets a diff criteria.

Many thanks in advance to all and sundry.

Get me a shrubbery.

[slimdave]

Something like ...

Select*
from Stuff
where state = 'AZ'
and ((:input = 'Phx' AND Area_code = '502')
Or (:input != 'Phx' area_code = '480'))

perhaps.

[gooddawggy]

Hmm...Well this is the actual SQL..will this do?

select entity,
resource_category,
collector,
charge_unit,
home_unit,
gl_journal_category,
charge_number,
subsystem_tran_code as tran_code,
reference_one,
reference_two,
reference_three,
interface_batch_id as journal_id,
description,
quantity,
unit_of_measure,
document_date,
amount,
month_number
from cr_accounts_payable_sv
where entity='PVNGS'
and ((:REF_NUM = 'ALL' AND resource_category not in ('CID','OHD','790','914','918','750','751', '916'))
Or (:REF_NUM != 'ALL' resource_category in '820', '837','838','840','CL','CLO'))and month_number between :STARTMONTH and :ENDMONTH
and substr(collector,0,2) in (:WO_NUM)



Hmm...I tried this snippet with some hard coded values and get a missing right parenthesis no matter how may right parenthesis' I put in

[slimdave]

Check parens ...

...
and ((:REF_NUM = 'ALL' AND resource_category not in ('CID','OHD','790','914','918','750','751', '916'))
Or (:REF_NUM != 'ALL' resource_category in ('820', '837','838','840','CL','CLO')))
...

[gooddawggy]

Thanks muchly. Got it going. was missing an "AND" in there, and it went like cake.

-Shrubbery attained. A nice one, but not too expensive.

[Rob Vollman]

Kind of late on this one, but how about DECODE?

Area_Code = DECODE (:input, 'Phx', 502, 480)

Cheers,
Robert

http://thinkoracle.blogspot.com/2005/06/decode.html