Oracle 10g - Modify select CASE

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

LVL 1
JDCamAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
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
 
JDCamAuthor Commented:
Excellent .... Works Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.