Solved

No rows returned in subquery

Posted on 2003-11-24
4
958 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

688 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