We help IT Professionals succeed at work.

Oracle 9i - Return

JDCam
JDCam asked
on
412 Views
Last Modified: 2013-12-16
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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
Yes... no rows found
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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

Author

Commented:
resolved myself

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.