Solved

Oracle 10g - Modify select  CASE

Posted on 2013-06-10
2
386 Views
Last Modified: 2013-06-10
Experts,

I want to modify the query below.

Current behaviour:
SELECT PROBILL from TABLE A
IF PROBILL is NULL  SELECT MIN value from TABLEB

I have found that sometimes TABLEB is also NULL. If so, I want to return :Ordnum from the input.

SELECT CASE 
  WHEN PROBILL IS NULL
  THEN
    (Select MIN(myvalue) 
    From TABLEB 
    WHERE ORD_NUM = :Ordnum
    and COMP_CODE = :Comp)
  ELSE PROBILL
  END
FROM (SELECT MIN(WAY_BILL_NUM) PROBILL
  FROM TABLEA
  WHERE ORD_NUM = :Ordnum
  and COMP_CODE = :Comp)

Open in new window

0
Comment
Question by:JDCam
2 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 39235032
SELECT	COALESCE(T1.PROBILL,T2.PROBILL,:Ordnum)
FROM
	(SELECT	MIN(WAY_BILL_NUM) PROBILL
	FROM	TABLEA
	WHERE	ORD_NUM = :Ordnum
		AND COMP_CODE = :Comp
	) T1 CROSS JOIN
	(SELECT	MIN(myvalue) PROBILL
	FROM	TABLEB 
	WHERE	ORD_NUM = :Ordnum
	AND	COMP_CODE = :Comp
	) T2

Open in new window

0
 

Author Closing Comment

by:JDCam
ID: 39235058
Excellent .... Works Great
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

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

15 Experts available now in Live!

Get 1:1 Help Now