[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle 9i - Return

Posted on 2013-12-11
7
Medium Priority
?
395 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

0
Comment
Question by:JDCam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39711632
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.
0
 
LVL 1

Author Comment

by:JDCam
ID: 39711646
Yes... no rows found
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 39711678
I cannot test on 9i but try something like this:
drop table tab1 purge;
create table tab1(col1 number);
insert into tab1 values(1);
commit;

select col1 from (
	select min(myOrder) over() max_order, myOrder, col1 from
	(
		select 1 myOrder, col1 from tab1 t1 where col1=2
		union all
		select 55, 55 from dual
	)
)
where myOrder=max_order
/

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Accepted Solution

by:
JDCam earned 0 total points
ID: 39711715
Thanks, but I dont follow your example.
It looks like this is working using NVL

SELECT NVL((SELECT ORD_NUM from E_ORD_H where comp_Code = 'W1' AND CUST_CODE = 'GMWEB' and ORD_CUST_ORD_NUM = '05-2014-1161768'), 55) ord FROM DUAL UNION ALL
SELECT NVL((SELECT ORD_NUM from E_ORD_H where comp_Code = 'W1' AND CUST_CODE = 'GMWEB' and ORD_CUST_ORD_NUM = '05-2014-1180700'), 55) ord FROM DUAL UNION ALL
SELECT NVL((SELECT ORD_NUM from E_ORD_H where comp_Code = 'W1' AND CUST_CODE = 'GMWEB' and ORD_CUST_ORD_NUM = '05-2014-1180731'), 55) ord FROM DUAL 

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39711729
>>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.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39711813
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

0
 
LVL 1

Author Closing Comment

by:JDCam
ID: 39721045
resolved myself
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question