Could you Explain COALESCE function in Oracle

Could you Explain COALESCE function in Oracle


WHERE ACC_LV_CODE_ST like COALESCE(?, ACC_LV_CODE_ST)
LVL 9
Manikandan ThiagarajanSenior consultantAsked:
Who is Participating?
 
for_yanConnect With a Mentor Commented:
This question mark is a bit confusing but in general coalesce() function is rather simple
as they explain in http://www.techonthenet.com/oracle/functions/coalesce.php

SELECT coalesce( address1, address2, address3 ) result
FROM suppliers;

will return:

    IF address1 is not null THEN
         result := address1;

    ELSIF address2 is not null THEN
        result := address2;

    ELSIF address3 is not null THEN
        result := address3;

    ELSE
        result := null;

    END IF;

question mark is a part of the prepared statement as I understand,
if you provide value - it will always become result
if it is given null value then ACC_LV_CODE_ST will become the result


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
coalesce will return the first value that is not null.

in your example, if ? (variable, user input ..) is null, it will result in:
WHERE ACC_LV_CODE_ST like ACC_LV_CODE_ST
which means: match all rows

if ? is passed (aka not null), it will be:
WHERE ACC_LV_CODE_ST like ?

 hope this clarifies
0
All Courses

From novice to tech pro — start learning today.