select
COALESCE(ALT_INVT_REP_CODE,'1'),
NVL(ALT_INVT_REP_CODE,1)
from M_Item_D3
where Comp_Code = 'A1' and Cust_code = 'ARCINT' and ALT_INVT_REP_TP_CODE = 'UNIT'
and Item_code = 'XMC613'
SELECT
COALESCE(
(SELECT
ALT_INVT_REP_CODE
FROM M_Item_D3
WHERE Comp_Code = 'A1'
AND Cust_code = 'ARCINT'
AND ALT_INVT_REP_TP_CODE = 'UNIT'
AND Item_code = 'XMC613'
AND rownum = 1
)
, 1)
FROM dual
this assumes you only ever want a single value returned.SELECT
COALESCE(
(SELECT TOP(1)
ALT_INVT_REP_CODE
FROM M_Item_D3
WHERE Comp_Code = 'A1'
AND Cust_code = 'ARCINT'
AND ALT_INVT_REP_TP_CODE = 'UNIT'
AND Item_code = 'XMC613'
)
, 1)
This one DOES work in Oracle 11g
create table M_Item_D3 (
Comp_Code varchar2(10)
, cost_code varchar2(10)
, cust_code varchar2(10)
, ALT_INVT_REP_TP_CODE varchar2(10)
, ALT_INVT_REP_CODE int
, item_code varchar2(10)
)
;
SELECT
COALESCE(
(SELECT
ALT_INVT_REP_CODE
FROM M_Item_D3
WHERE Comp_Code = 'A1'
AND Cust_code = 'ARCINT'
AND ALT_INVT_REP_TP_CODE = 'UNIT'
AND Item_code = 'XMC613'
and rownum = 1
)
, 1) as x
from dual
;
| X |
|----|
| 1 |
see this online demonstration
I am using a UNION ALL
with Select '1' from Dual
then
Fetch First 1 row only