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
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'.