CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Stanley Mungai
Stanley Mungai

Reputation: 4150

Select with NVL2 Not working Oracle

I am selecting values from the database using Functions. I have three different functions in which if the first does not return anything I check for the second and third functions for values using NVL2. The problem is when I execute independent functions they return values but not in the NVL2 function My query is:

select  NVL2(CRBP.FPDATE(LAM.ACID), 
             CRBP.FPDATE1(LAM.ACID),
             CRBP.ODSANCDATE(GAM.ACID)) from 
 tbaadm.lam, TBAADM.GAM
where gam.acid = lam.acid
  and gam.acid = 'VM12990'

This does not return any value.

But when I execute :

select CRBP.FPDATE (LAM.ACID) from tbaadm.lam where lam.acid = 'VM12990';

This Already returns a value. Shouldn't this be the value returned by my first query considering that it is the first in the check. What is the Problem with My first Query??

Upvotes: 0

Views: 1169

Answers (2)

ShoeLace
ShoeLace

Reputation: 3576

NVL2(a,b,c) is a "if a is null then a else b" type function. so the value of a is never returned.

it sounds like you want COALESCE which will return the first not null value from the set of parameters

Upvotes: 0

pablomatico
pablomatico

Reputation: 2242

If CRBP.FPDATE returns a value the query should return the value returned by the function CRBP.FPDATE1 and if CRBP.FPDATE returns NULL then the query should return CRBP.ODSANCDATE's vlaue.

Does CRBP.FPDATE1 return anything?

Upvotes: 4

Related Questions