Oracle 10g - Default value is select is null

Experts,

I need to modify the below query so that it returns a default value of 'XX' if no result is otherwise returned.  Please help.

SELECT
D10.EDI_DATA_ID_VALUE
FROM E_RCPT_D10 D10
WHERE D10.EDI_DATA_ID_CODE = "2040.N9.02UM"
AND D10.RCPT_NUM = :rcpt
AND D10.COMP_CODE = :comp
AND D10.RCPT_LINE_NUM = (
  SELECT min(RCPT_LINE_NUM)
  FROM E_RCPT_D5 D5
  WHERE D5.COMP_CODE = D10.COMP_CODE
  AND D5.RCPT_NUM = D10.RCPT_NUM
  AND D5.RCPT_LEV1 = :item
)

Open in new window

LVL 1
JDCamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:
try nvl fn

SELECT nvl(d10.EDI_DATA_ID_VALUE,'xx') dvalue
FROM E_RCPT_D10 D10
WHERE D10.EDI_DATA_ID_CODE = "2040.N9.02UM"
AND D10.RCPT_NUM = :rcpt
AND D10.COMP_CODE = :comp
AND D10.RCPT_LINE_NUM = (
  SELECT min(RCPT_LINE_NUM)
  FROM E_RCPT_D5 D5
  WHERE D5.COMP_CODE = D10.COMP_CODE
  AND D5.RCPT_NUM = D10.RCPT_NUM
  AND D5.RCPT_LEV1 = :item
)
0
sventhanCommented:
nvl(d10.EDI_DATA_ID_VALUE,'XX') dvalue

This NVL fn above would return 'XX' if the edi_data_id_value field contained a null value. Otherwise, it would return the edi_data_id_value.
0
slightwv (䄆 Netminder) Commented:
>>default value of 'XX' if no result is otherwise

By no result do you mean "no rows found"?

If so, you will need to do a little smoke and mirrors.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

JDCamAuthor Commented:
nvl has no error, but does not return 'XX' as needed.
The original query has no rows found

Smoke and Mirrors?
0
JDCamAuthor Commented:
I am trying this, based on a previous solution, but still returns nothing

Select CASE
  WHEN UOM IS NULL
  THEN 'XX'
  ELSE UOM
  END
FROM (SELECT
D10.EDI_DATA_ID_VALUE UOM
FROM E_RCPT_D10 D10
WHERE D10.EDI_DATA_ID_CODE = '2040.N9.02UM'
AND D10.RCPT_NUM = :ord
AND D10.COMP_CODE = 'W1'
AND D10.RCPT_LINE_NUM = (
  SELECT min(RCPT_LINE_NUM)
  FROM E_RCPT_D5 D5
  WHERE D5.COMP_CODE = D10.COMP_CODE
  AND D5.RCPT_NUM = D10.RCPT_NUM
  AND D5.RCPT_LEV1 = :item
)
)

Open in new window

0
slightwv (䄆 Netminder) Commented:
You cannot use NULL of NVL to solve this.

Reason is that there are no rows returned so there is nothing to base the NULL on.

There are several examples of how to do this if you look around.  Even the one I always used to use is in there (the union with the not exists).

I'm seeing if I can come up with a cleaner way since I haven't tried for many years.

While I do, check out what is already out there:
https://forums.oracle.com/thread/671358?start=0&tstart=0
0
slightwv (䄆 Netminder) Commented:
From that thread, I am sort of leaning towards this approach:

drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');
insert into tab1 values('a');
commit;

select col1
from
(
select rownum myrn, col1 from tab1 where col1='b'
union all
select 0, 'XX' from dual
)
where myrn>0 or (rownum=1 and myrn=0)
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDCamAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.