SQL & PL/SQL

Hi,

994122 wrote:

Hi all,

How to use NVL function in the Decode ?

SELECT Decode(Sign(sum(nvl(7),0) - nvl(sum(5),0)), -1, 0,(sum(nvl(7),0) - nvl(sum(5),0)) QTY
from dual

Thanks
 

Sorry, it's unclear what you want.

There's nothing special about using NVL in a DECODE expression; NVL works the same whether it's part of a DECODE expression or not.  In particular, it always takes exactly 2 arguments, like this

NVL (amt, 0)

In the code you posted, you're only passing 1 argument the first time you call NVL.

It's possible to use a literal as the 1st argument, but it's a waste of time and it's confusing.  That is, it's perfectly legal to say

NVL (7, 0)
 

but 7 will never be NULL. so you might as well just say

7
 

What are you trying to do here?

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements), so that the people who want to help you can re-create the problem and test their ideas.

Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

Always say which version of Oracle you're using (for example, 11.2.0.2.0).

See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

Whatever you're trying to do, consider using CASE instead of DECODE.  CASE can do anything that DECODE can do, and CASE can often do it much, much simpler.  If DECODE can do a job, but it needs functions like SIGN, then DECODE is probably the wrong tool for the job.

Look at the example

WITH T AS

(

select 'ABC' name,1000 sal  from dual

union all

select 'DEF' name,2000 sal  from dual

union all

select 'EFG' name,null sal  from dual

)

output

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

NAME     SAL

ABC      1000

DEF      2000

EFG       null

Now show the salary descripttion

if salary null , use NVL function to make null salary to 0 salary

WITH T AS

(

select 'ABC' name,1000 sal  from dual

union all

select 'DEF' name,2000 sal  from dual

union all

select 'EFG' name,null sal  from dual

)

select name,sal,decode(nvl(sal,0),0,'You have no salary','You have salary Salary') des from t;

output

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

NAME     SAL   DES

ABC    1000    You have salary Salary

DEF    2000    You have salary Salary

EFG    NULL    You have no salary

CASE

     WHEN ( SUM (quantity) - SUM (quantity_received) )<= 0 THEN 0

       ELSE ( SUM (quantity) - SUM (quantity_received) )

END Qty_Arrival

how to use NVL  in the CASE ?

Thanks to all..