Oracle 10g - default value in sub select

JDCam
JDCam used Ask the Experts™
on
Experts,
In the below query, sometimes the sub-select will have no matching row returned.
When this happens the main query returns no result, even when the Else is called.

Ideally i use a default value of 1 when there is no matching row.
I tried a Coalese in the sub-select, and on its own that works. But the main query starts to complain about the table names

SELECT CASE
 WHEN :UOM = 'BX' 
 THEN
TO_CHAR (TO_NUMBER(:Qty)/TO_NUMBER(CA_QTY))
 ELSE :Qty
 END as QTY
FROM
(select Item_qty_Bkd_qty CA_QTY
from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = :item
and ITEM_QTY_BKD_LEV_NUM = 2)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark GeerlingsDatabase Administrator

Commented:
Try this (with an "nvl"):
SELECT CASE
 WHEN :UOM = 'BX' 
 THEN
TO_CHAR (TO_NUMBER(:Qty)/TO_NUMBER(CA_QTY))
 ELSE :Qty
 END as QTY
FROM
(select nvl(Item_qty_Bkd_qty,1) CA_QTY
from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = :item
and ITEM_QTY_BKD_LEV_NUM = 2)

Open in new window

Author

Commented:
No Good... just running the sub-query on its own I get null when there is no matching row.

Author

Commented:
I got it to work....

SELECT CASE
 WHEN :UOM = 'BX' 
 THEN
TO_CHAR (TO_NUMBER(:Qty)/TO_NUMBER(CA_QTY))
 ELSE :Qty
 END as QTY
FROM (
  SELECT 
  NVL((select Item_qty_Bkd_qty 
  from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = :item
and ITEM_QTY_BKD_LEV_NUM = 2),1)as CA_QTY FROM DUAL)

Open in new window

johnsoneSenior Oracle DBA

Commented:
Can this query:
select Item_qty_Bkd_qty 
  from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = :item
and ITEM_QTY_BKD_LEV_NUM = 2

Open in new window

Ever return more than one row?  If so, then your nested solution will not work.  You'll get a single row subquery returns more than one row error.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial