Avatar of JDCam
JDCam
 asked on

Oracle 10g - Return default when no record found

Experts,
A simple query that returns a number stored as a string.
For some, there will be no matching row found
As this value will later be used in division, I want it to return 1 by default

Neither NVL or COALESCE are returning the 1. I suspect this is the difference between a null column and No matching record.
Not sure how to correct this.

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'

Open in new window

Oracle DatabaseSQL

Avatar of undefined
Last Comment
JDCam

8/22/2022 - Mon
JDCam

ASKER
I think I got it.... May not be the best, but it works

I am using a UNION ALL  
with Select '1' from Dual
then
Fetch First 1 row only
Alex [***Alex140181***]

Please avoid what JDCam has suggested!! Don't get me wrong, but that's bad practice! Just use exception handling: like NO_DATA_FOUND! Maybe in conjunction with a custom function/package that returns the selected value or '1' if none is found.
JDCam

ASKER
Please post your suggestion as a TSQL example
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

If no row is returned then coalesce or nvl have no row to supply the default value on. Hence you need a row. An alternative approach in SQL:
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

Open in new window

this assumes you only ever want a single value returned.

Not sure why you want TSQL, but in TSQL just remove "FROM dual" from what you see above.
(TSQL is used by MS SQL Server and some versions of Sybase)
Alex [***Alex140181***]

@PortletPaul: the "main" select will still raise NO_DATA_FOUND in case it does not return any row!

 @slightwv: Ok, it depends: if he uses this one as straight SQL, I'm at your side, but this is done within PL/SQL, IMHO, you should avoid that DUAL workaround. Where is that SQL being used/executed?!
slightwv (䄆 Netminder)

>>but this is done within PL/SQL, IMHO, you should avoid that DUAL workaround

Agreed!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

This works in TSQL (as long as the subquery is valid) but I still don't know why TSQL was asked for
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)

Open in new window

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 |

Open in new window

see this online demonstration

Even if the subquery has no data to return, this query returns 1 from dual.
(Note that the column ALT_INVT_REP_CODE has to be numeric.)
JDCam

ASKER
Apologies….  I should have said Straight SQL, not TSQL.  My point was to achieve this within query syntax and without the need to write custom Function/package.  

Using the value later... This query will be nested into a larger query and the value returned to be used in division with another value.

Based on SlightWV comments, I will stick with the  Union All, Dual approach.

Thanks all, and sorry for the confusion