?
Solved

Oracle 9i - Return

Posted on 2013-12-11
7
Medium Priority
?
396 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
  • 3
  • 3
7 Comments
 
LVL 78

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 78

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 78

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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…
Suggested Courses

862 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