Sql query dosen't give correct output

Question

SELECT MAX(date)
                    FROM abc
                   WHERE 
                      p_id=p_p_id
                     AND nvl(c_number,0)=nvl(p_c_number,0) 
                     AND nvl(m_number,0)=nvl(p_m_number,0)
                     AND nvl(s_number,0)=nvl(p_s_number,0);

In the above query p_p_id,p_c_number,p_m_number & p_s_number is passed to this query ,but this query gives the wrong output in certain condition: in table abc c_number ,m_number,s_number can be null ,zero or any other value i want to match c_number if null with p_c_number if null but the problem with the above query is ,in case if c_number is null the zero is assigned to c_number and if p_c_number is already zero then it matches null with zero value please help ..i am using oracle as rdbms

...so use some combination of is null? (e.g. x is null and y is null -- of course, perhaps Oracle has some trickery to do the above. The COALESCE or IFNULL can even be used -- COALESCE(a,b) IS NULL is true only when a and b are null -- but just KISS. — user166390
AND ((c_number is null and p_c_number is null ) or (c_number=p_c_number) )...i know this solution but if something different is possible — gaurav
@guarav It would be possible to use NVL/NZ with a different sentinel value -- the sentinel value just has to be guaranteed to never be used for valid data. -1 might be appropriate in some cases. I would just rewrite the query though. — user166390

Phil Phil · Accepted Answer · 2011-08-10T05:16:44

Try replacing each nvl condition with the following

AND (c_number = p_c_number OR (c_number IS NULL AND p_c_number IS NULL))

or as pst indicates

AND (c_number = p_c_number OR COALESCE(c_number, p_c_number) IS NULL)

Sql query dosen't give correct output

1 Answers