Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
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'
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
I am using a UNION ALL
with Select '1' from Dual
then
Fetch First 1 row only
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.
You asked this in an Oracle Topic Area so you will get Oracle solutions.
You used the DUAL table in your example which is also Oracle.
TSQL is SQL Server.
If you need SQL Server, please remove the Oracle Topic Area and add SQL Server.
Back to the question: I'm the opposite of what Alex is suggesting.
I prefer to not write code when I don't have to. If I can do it all with SQL, I will. A simple union all with a select from DUAL is acceptable to me.
What I'm confused about is where you say you will be using the value later. Later in straight SQL or in PL/SQL code?
You might be able to use NVL later in the code and remove the UNION work-around.
You used the DUAL table in your example which is also Oracle.
TSQL is SQL Server.
If you need SQL Server, please remove the Oracle Topic Area and add SQL Server.
Back to the question: I'm the opposite of what Alex is suggesting.
I prefer to not write code when I don't have to. If I can do it all with SQL, I will. A simple union all with a select from DUAL is acceptable to me.
What I'm confused about is where you say you will be using the value later. Later in straight SQL or in PL/SQL code?
You might be able to use NVL later in the code and remove the UNION work-around.
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:
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)
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
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)
@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: 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?!
This works in TSQL (as long as the subquery is valid) but I still don't know why TSQL was asked for
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.)
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)
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 |
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.)
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial