Solved

Oracle 9i - Return

Posted on 2013-12-11
7
385 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
 

Author Comment

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

Author Closing Comment

by:JDCam
ID: 39721045
resolved myself
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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