How to use Oracle's decode function in where clause

Question

I have a query with few filter criteria, one of them is get to the query as integer parameter. I want to use this filter only if this integer is > 0 I can't use NVL as it will never be null. How can I use DECODE in such case?

SELECT (columns list)
        FROM 
        AGREEMENT A
        WHERE 
        A.ACCOUNT = 545 
        AND A.GRP_ID = NVL(?,A.GRP_ID)

The parameter ? I get is an Integer

score 1 · Accepted Answer · answered Jun 08 '15 at 12:24

You can use a case:

SELECT (columns list)
    FROM 
    AGREEMENT A
    WHERE 
    A.ACCOUNT = 545 
    AND A.GRP_ID = CASE ? WHEN 0 THEN A.GRP_ID ELSE ? END

And Decode works in a similar fashion, although I think it's less readable.

SELECT (columns list)
    FROM 
    AGREEMENT A
    WHERE 
    A.ACCOUNT = 545 
    AND A.GRP_ID = DECODE(?, 0, A.GRP_ID, ?)

But given the use case, making the parameter NULL would be a little better. After all, 0 is a value which you want to treat as a different value, while NULL semantically makes more sense for specifying 'no filter'.

score 0 · Answer 2 · answered Jun 08 '15 at 12:26

Don't use decode(). It really is very old-fashioned. You can use case or just put the right logic as in:

where a.account = 545 and
      (? = 0 or a.grp_id = ?)

Of course, this requires using the parameter twice. However, that would also be necessary with decode().

How to use Oracle's decode function in where clause

2 Answers2