asked on

Oracle 9i - Return

I am trying to query a list of reference numbers to see see if the exist in our database. If a record doesnt exist, I am trying to return a generic value to server as a placeholder. The idea is so that the output can be aligned with the provided input.

I am trying COALESCE as shown below, I get no error, but am not getting the generic '55' returned where there is no matching row

SELECT COALESCE(ORD_NUM, 55) from e_ord_H where comp_Code = 'W1' AND CUST_CODE = 'GMWEB' and ORD_CUST_ORD_NUM = '05-2014-1161768' UNION ALL
SELECT COALESCE(ORD_NUM, 55) from e_ord_H where comp_Code = 'W1' AND CUST_CODE = 'GMWEB' and ORD_CUST_ORD_NUM = '05-2014-1180700' UNION ALL
SELECT COALESCE(ORD_NUM, 55) from e_ord_H where comp_Code = 'W1' AND CUST_CODE = 'GMWEB' and ORD_CUST_ORD_NUM = '05-2014-1180731'

Open in new window

slightwv (䄆 Netminder)

COALESCE will return 55 when ORD_NUM is null.

Are you saying that you want a 55 to be returned when there are 'no rows found'? Null column values and 'no rows found' are different things.

JDCam

ASKER

Yes... no rows found

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

JDCam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>It looks like this is working using NVL

As long as you are happy with the results. Feel free to accept your post as the solution.

johnsone

To me, this looks better. I don't think it is any better than anything else posted, just different.

SELECT Nvl(a.ord_num, 55) 
FROM   e_ord_h a 
       right outer join (SELECT '05-2014-1161768' ord_num 
                         FROM   dual 
                         UNION ALL 
                         SELECT '05-2014-1180700' 
                         FROM   dual 
                         UNION ALL 
                         SELECT '05-2014-1180731' 
                         FROM   dual) b 
                     ON a.ord_cust_ord_num = b.ord_num;

Open in new window

JDCam

ASKER

resolved myself