• Status: Solved
• Priority: Medium
• Security: Public
• Views: 320

# Decode and NVL

Hello,

Please assist with understanding the statement below. I understand for example NVL(PE.PAID,0) is stating if pe.paid is null place a zero.

where
(NVL(DECODE(NVL(PE.OKTOBILL,0),1,DECODE(TO_CHAR(PE.HICFIRST,'YYYY'),'0000', 0, NVL(PE.BILLED,0)),0),0) - NVL(PE.WRITTENOFF,0) - NVL(PE.PAID,0)) >0

Thank you for the help.
0
jverasql
3 Solutions

Commented:
decode goes like this:
decode(this_value,when_a,return_a,when_b,return_b,else_return_this)

Break it down and indent it to take each piece individually:
``````
(
NVL(
DECODE(
--if the nvl on oktobill = 1 then return the value of the decode
NVL(PE.OKTOBILL,0),1,
DECODE(
--If the YYYY = '0000' return 0
TO_CHAR(PE.HICFIRST,'YYYY'),'0000', 0,
--else return this
NVL(PE.BILLED,0)
),0
),0
)
-
NVL(PE.WRITTENOFF,0) - NVL(PE.PAID,0)
) >0
``````
0

Commented:
It will return PE.OKTOBILL 0 if it's data value is null or any other value apart from 1, If it is 1 then it will compare value of PE.HICFIRST in second decode used-  if year part i.e. to_Char(HICFIRST,'YYYY') is '0000' it will return 0, if not then it will compare PE.BILLED with PE.HICFIRST if PE.BILLED is null or value of both columns matches it will return 0.

The result of above value return is then compared in where clause: whether it is greater then 0 or not
0

freelancerCommented:
perhaps as case expressions will help?
``````WHERE (
NVL(
CASE
WHEN NVL(PE.OKTOBILL, 0) = 1 THEN
CASE
WHEN TO_CHAR(PE.HICFIRST, 'YYYY') = '0000' THEN 0
ELSE NVL(PE.BILLED, 0)
END
ELSE 0
END
, 0)
- NVL(PE.WRITTENOFF, 0) - NVL(PE.PAID, 0)
) > 0
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.