Syntax for Optional 'ALL' Parameter in Oracle SQL

[MJRBIM]

I am trying to pass an Optional 'ALL' Parameter to Oracle in the SQL statement below...

USER.STATUS can be either 0 or 1 in the Source-Data (Inactive or Active).

However, the _STATUS Parameter can be either 0, 1 or 2 (ALL).

I tried an IF/THEN/ELSE statement in the SQL below - but it doesn't work as is.

Gives and "ORA-00920: invalid relational operator" error...
-------------------

SELECT
i.LAST_NAME SURNAME,
i.FIRST_NAME GIVEN_NAME,
DECODE(u.STATUS, 1, 'Active', 0, 'Inactive') STATUS,
u.STATUS STATUS_CODE

FROM INDIVIDUAL i
INNER JOIN USER u ON u.INDIVIDUAL_ID = i.ID

WHERE (IF _STATUS IS NOT NULL AND _STATUS < 2
THEN u.STATUS = _STATUS
ELSE u.STATUS = u.STATUS)

-------------------

Any suggestions or advice would be appreciated.

Thanks in advance...!


Senior Business Intelligence Consultant
Toronto, Canada

SeagateInfo 7 -> Crystal Enterprise -> BOE-XI (R2) / (R3.1) | Crystal ( 7 -> 2008) design | WebI design | Best practices for Java / .Net / Web Services

 

[SantaMufasa]

MJRBIM,

Having been an "Oracle-ite" since 1988, I've never seen this syntax construct in Oracle. If you can refer me to a link where you have seen this used successfully in Oracle, I'll be able to help you.

In the meantime, if you can offer a (non-technical/non-syntactical) functional narrative of what you want the code to do, we can probably construct something that does what you want.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.