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

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
Asked:
jverasql
3 Solutions
 
slightwv (䄆 Netminder) 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

Open in new window

0
 
Pooja Katiyar VermaCommented:
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
 
PortletPaulfreelancerCommented:
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

Open in new window

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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