Solved

No rows returned in subquery

Posted on 2003-11-24
4
954 Views
Last Modified: 2010-05-18
I have the following query in a PL/SQL block:

select nvl(include_in_dif, 1)
   into m_include_in_dif
  from pcc
 where pcc in (select nvl(pcc, 'NONE')
                 from gdls_z_expanded_boms
                where bomtype = 'MRP'
                  and partnum = 'DIFREPORT-001');

The Sub query returns no rows so the whole query returns nothing. I would like m_include_in_dif to default to '1' if no rows are returned. The above query does not do this of course.

Can I do this in one query. Any ideas ... ?

Romans
0
Comment
Question by:Romans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:FBIAGENT
ID: 9813141
have you try this?

select nvl(include_in_dif, 1)
   into m_include_in_dif
  from pcc
 where pcc in (select max(nvl(pcc, 'NONE'))
                 from gdls_z_expanded_boms
                where bomtype = 'MRP'
                  and partnum = 'DIFREPORT-001')
;

0
 

Author Comment

by:Romans
ID: 9813646
This ones not work FBIAGENT .... thanks for the input

Romans
0
 
LVL 5

Accepted Solution

by:
FBIAGENT earned 50 total points
ID: 9813822
this should work

have you try this?

select nvl(max(include_in_dif), 1)
   into m_include_in_dif
  from pcc
 where pcc in (select nvl(max(pcc), 'NONE'))
                 from gdls_z_expanded_boms
                where bomtype = 'MRP'
                  and partnum = 'DIFREPORT-001')
;
0
 

Author Comment

by:Romans
ID: 9814685
This one works ....

Thanks FBIAGENT

Romans
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
constraint check 2 56
Oracle dataguard 5 59
Row_number in SQL 6 45
Fill Null values 5 36
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question