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
WHEN :UOM = 'BX'
END as QTY
(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)