Solved

Oracle 9i - Return

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

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 76

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 76

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 34

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now