<div>
c5(nvl(2,1)) is returned same object as c5(2) 
 
and ... check this Select statement and see if it returns record(s)? 
 

<pre><code class="code-10 nolinks" id="code-20-42412537-1">select orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id
 
</code></pre>
</div>

<div>
It's because nvl(1,2) is equal to 1 and nvl(2,1) is equal to 2. 
In first case you get null, but in second case you get a row. 
What do you want to get? Do you want to return row from second select if first one returns null?
</div>

<div>
1 is not equal to NULL therefore nvl(1,2) = 1; thus, zzz in c5(nvl(1,2)) loop is the same as saying c5(1) (which apparently results in an EMPTY resultset) and zzz in c5(nvl(2, 1)) loop is the same as c5(2) because nvl(2,1) = 2, since 2 is not equal to NULL. 
 
It sounds like the first query is returning no records: 

<pre><code class="code-1 nolinks" id="code-20-42412830-1"> select orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id
</code></pre>
 
which table does `orditm_id_rcvd` come from in the SELECT clause? Also, which table does`in_recshp_id` come from in the last line for both queries? I suggest that all fields be referenced using a table name.
</div>

<div>
Yes want to return row from second select if first one returns null and vica versa
</div>

<div>
Do you expect an empty recordset if BOTH queries result in empty recordsets themselves? 
 
Also, please let us know where those fields are from in my previous post (`orditm_id_rcvd` and `in_recshp_id`) 
 
You might be able to do this with UNION ALL and EXISTS (or NOT EXISTS) 
 
Thank you
</div>

<div>
orditm_id_rcvd` and `in_recshp_id` 
 
Orditm_id_rcvd is from built items table and in_recshp_id is from received shipments table
</div>

<div>
You might be able to do some SQL magic ant sort of get the UNION ALL to only EVER return from one of the queries but it would be pretty ugly and always accesses both tables and queries the first table twice. 
 
I would strongly suggest not trying to be fancy and use two cursor and only open the second one if the first one has no rows.
</div>

<div>
You need to do so: 
 

<pre><code class="code-10 nolinks" id="code-20-42413860-1">Cursor C5 (v_key integer) is
 select orditm_id_rcvd, pspec_id, quantity
 from
 (
 select z.*, sum(decode(sel,1,1,0)) over() as num1, sum(decode(sel,2,1,0)) over() as num2
 from
 ( 
 select 1 as sel, orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity 
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id
 union all
 select 2 as sel, orditm_id_rcvd,rcvitm.pspec_id,rcvitm.quantity 
 from dss.received_items rcvitm,
 dss.received_shipments recshp,
 dss.orders ord,
 dss.ordered_items orditm
 where ord.id = orditm.order_id 
 and orditm.id = rcvitm.orditm_id_rcvd
 and recshp.id = rcvitm.recshp_id
 and recshp.id = in_recshp_id
 ) z
 ) where (v_key = 1 and sel = 1) or 
 (v_key = 2 and sel = 2) or
 (v_key = 3 and ((sel = 1 and num1 &gt; 0) or (sel = 2 and num1 = 0))) or
 (v_key = 4 and ((sel = 2 and num2 &gt; 0) or (sel = 1 and num2 = 0)));
 
</code></pre>
 
Here v_key maybe: 
1 - return rows only from first subselect 
2 - return rows only from second subselect 
3 - return rows from first subselect if it contain rows, else return rows from second subselect 
4 - return rows from second subselect if it contain rows, else return rows from first subselect
</div>

<div>
Would a CASE subquery and WHERE NOT EXIST condition be more efficient? Note you may have to wrap the case inside a dummy SELECT shell. I'm hoping more Query Syntax Experts will help (added the topic to this question). 
 

<pre><code class="code-10 nolinks" id="code-20-42414348-1"> Cursor C5 (v_key integer) is
 CASE v_key 
 WHEN 2 THEN
 (
 select orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id
 NOT EXISTS(
 select orditm_id_rcvd,rcvitm.pspec_id,rcvitm.quantity 
 from dss.received_items rcvitm,
 dss.received_shipments recshp,
 dss.orders ord,
 dss.ordered_items orditm
 where ord.id = orditm.order_id 
 and orditm.id = rcvitm.orditm_id_rcvd
 and recshp.id = rcvitm.recshp_id
 and recshp.id = in_recshp_id
 )
 )
 ELSE
 (
 select orditm_id_rcvd,rcvitm.pspec_id,rcvitm.quantity 
 from dss.received_items rcvitm,
 dss.received_shipments recshp,
 dss.orders ord,
 dss.ordered_items orditm
 where ord.id = orditm.order_id 
 and orditm.id = rcvitm.orditm_id_rcvd
 and recshp.id = rcvitm.recshp_id
 and recshp.id = in_recshp_id
 NOT EXISTS(
 select orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id
 )
 )
 END
</code></pre>
</div>

<div>
I understand the question to mean they want the results of the second query ONLY when the first query doesn't return rows. 
 
The flag passed into the cursor was an attempt at trying to make that happen and it didn't work and it never will. 
 
I would forget all about the flag.
</div>

<div>
still will work with one cursor with union all or have 2 separate cursors?
</div>

<div>
<pre><code class="code-10 nolinks" id="code-20-42415451-1">Cursor C5 is
 select orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id
 Cursor C5A is 
 select orditm_id_rcvd,rcvitm.pspec_id,rcvitm.quantity 
 from dss.received_items rcvitm,
 dss.received_shipments recshp,
 dss.orders ord,
 dss.ordered_items orditm
 where ord.id = orditm.order_id 
 and orditm.id = rcvitm.orditm_id_rcvd
 and recshp.id = rcvitm.recshp_id
 and recshp.id = in_recshp_id; 
</code></pre>
 
&nbsp;for zzz in c5 loop 
----- If this is null then 
for yyy &nbsp;in c5A loop 
 
Please let me know this as to exiting the 1st loop if no data found and open the 2nd cursor.
</div>

<div>
Again: &nbsp;You &quot;can&quot; do it with a single cursor but it will ALWAYS access one table twice and the other table once. &nbsp;So, even if the first cursor returns rows, you still access the other table and the first table twice. 
 
It is a very inefficient method to do this. &nbsp;In my opinion: &nbsp;Two cursors is the most efficient method. 
 
If you still want the single cursor, I already posted what should work. &nbsp;At a minimum, I posted a test case showing it working that you should be able to adapt to your situation. &nbsp;Remember, I cannot post 100% tested code since I don't have your tables or data.
</div>

<div>
<pre><code class="code-10 nolinks" id="code-20-42415956-1">Cursor C5 is
 select orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id;

 Cursor C5A is
 select orditm_id_rcvd,rcvitm.pspec_id,rcvitm.quantity 
 from dss.received_items rcvitm,
 dss.received_shipments recshp,
 dss.orders ord,
 dss.ordered_items orditm
 where ord.id = orditm.order_id 
 and orditm.id = rcvitm.orditm_id_rcvd
 and recshp.id = rcvitm.recshp_id
 and recshp.id = in_recshp_id;
begin
 
for zzz in C5 loop
 select part into v_inbound_part 
	 from dss.part_specs
 where id = zzz.pspec_id;
 for yyy in C5A loop
 select part into v_inbound_part 
	 from dss.part_specs
 where id = yyy.pspec_id;
 exit C5 when zzz.pspec_id is null;
 exit C5A when yyy.pspec_id is null;
 end loop;
end loop;
end;
</code></pre>
 
will this work?
</div>

<div>
Why are you nesting the cursors? 
 
Loop through the first cursor and set a flag AFTER the exit statement. &nbsp;If the flag has a value then the first cursor had rows so no need to loop through the second. &nbsp;If the flag is null, then the first cursor had no rows so you now need to loop through the second. 
 
NOW, if all you are doing is selecting into a variable inside the loop, then why are you looping at all? &nbsp;The variable can only contain a single value. &nbsp;If the cursor returns 100 rows, you still only ever have a SINGLE value held in v_inbound_part.
</div>

<div>
Why are you doing it in wrong way? I have posted before the excellent variant, which ONCE select from EVERY subselect and has what are you asking! It returns data from second subselect, if first returns null! 
Try it!
</div>

<div>
Pavel I understand, but when I open the cursor what is the value I have to pass?
</div>

<div>
Couldn't you CASE the cursor to run through one of two sets where the 'preferred' query is checked once using NOT EXISTS, then return the other query if no records exist in the 'preferred' query)? 
 
See pseudo code in : <a href="#a42414348" rel="ugc">#a42414348</a> 
 
(the code might need to be encased in a SELECT, for it to work) 
 
The XP (execution plan) would be simplified this way, correct?
</div>

<div>
As I said before, you need to pass 3, if you wish to check the first subselect and if there is null, then return rows from second subselect. 
If you wish to check first the second subselect and then first, you need to pass 4 as a parameter to the cursor.
</div>

<div>
&gt;&gt;which ONCE select from EVERY subselect and has what are you asking! 
 
I don't think that will work the way the question is stated. &nbsp;You don't know which result you want at the time of execution. &nbsp;Therefore, you cannot pass in the correct 'key'. 
 
Your method would require calling the code multiple times with each time accessing both tables and doing a window function. 
 
Personally, I consider that the wrong way. 
 
&gt;&gt;query is checked once using NOT EXISTS 
 
You can use NOT EXISTS which is why I had already posted it in <a href="#a42413424" rel="ugc">#a42413424</a>. 
 
You don't need the CASE again, because the KEY doesn't have any purpose. &nbsp; 
 
The requirements are clear: 
Execute the first query and it it doesn't return any rows, execute the second. 
 
The key/flag is meaningless. &nbsp;Please stop trying to use it.
</div>

<div>
I think the Flag/CASE only comes into play if you are 'preferentially' checking a subquery for existing records, over the other. Otherwise, without the flag, you ALWAYS check one subquery before the other (non-preferential). I wanted to point this out, just so the author knows that if data exists in both subqueries, the second, non-preferred subquery will always be ignored it sounds like, without Flag/CASE.
</div>

<div>
&gt;&gt; the second, non-preferred subquery will always be ignored it sounds like, without Flag/CASE. 
 
That was assumed given the requirement way up top in <a href="#a42412914" rel="ugc">#a42412914</a> 
 
Return first query. &nbsp;If that returns no rows, use the second query.
</div>

<div>
<div class="content wysiwyg-content">
I have a cursor as below. 
 

<pre><code class="code-10 nolinks" id="code-10-29075040-1">Cursor C5 (v_key integer) is
 select orditm_id_rcvd,bltitm.pspec_id,bltitm.quantity
 from dss.built_items bltitm,
 dss.orders ord,
 dss.ordered_items orditm,
 dss.received_shipments recshp
 where ord.id = orditm.order_id 
 and orditm.id = bltitm.orditm_id_rcvd
 and recshp.id = bltitm.recshp_id
 and recshp.id = in_recshp_id
 and v_key=1
 union all
 select orditm_id_rcvd,rcvitm.pspec_id,rcvitm.quantity 
 from dss.received_items rcvitm,
 dss.received_shipments recshp,
 dss.orders ord,
 dss.ordered_items orditm
 where ord.id = orditm.order_id 
 and orditm.id = rcvitm.orditm_id_rcvd
 and recshp.id = rcvitm.recshp_id
 and recshp.id = in_recshp_id
 and v_key=2; 
</code></pre>
 
In the above cursor the 1st select returns null but the second one returns a row. 
 
When I open the cursor like below 
 
&nbsp;for zzz in c5(1) loop 
 
returns nothing. But when I use &nbsp;for zzz in c5(2) loop 
will return the data. 
 
When I used &nbsp;for zzz in c5(nvl(1,2)) loop 
 
did not work. 
 
&nbsp;But when I used 
 
&nbsp;for zzz in c5(nvl(2,1)) loop 
 
it worked. 
 
Any suggestions?
</div>
</div>

I have a cursor as below.

(CODE)

In the above cursor the 1st select returns null but the second one returns a row.

When I open the cursor like below

 for zzz in c5(1) loop

returns nothing. But when I use  for zzz in c5(2) loop
will return the data.

When I used  for zzz in c5(nvl(1,2)) loop 

did not work.

 But when I used 

 for zzz in c5(nvl(2,1)) loop 

it worked. 

Any suggestions?

oracle union in a query

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.