oracle union in a query

I have a cursor as below.

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;   

Open in new window


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?
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
c5(nvl(2,1)) is returned same object as c5(2)

and ... check this Select statement and see if it returns record(s)?

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
     

Open in new window

Pavel GushchinProgrammerCommented:
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?
NerdsOfTechTechnology ScientistCommented:
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:
  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

Open in new window


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.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

anumosesAuthor Commented:
Yes want to return row from second select if first one returns null and vica versa
NerdsOfTechTechnology ScientistCommented:
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
anumosesAuthor Commented:
orditm_id_rcvd` and `in_recshp_id`

Orditm_id_rcvd is from built items table and in_recshp_id is from received shipments table
slightwv (䄆 Netminder) Commented:
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.
slightwv (䄆 Netminder) Commented:
Again, I WOULD NOT suggest doing it this way because you ALWAYS hit both tables and you ALWAYS hit the first table twice.

If the first query returns rows you shouldn't have to access the second table.  You can do that with TWO cursors but not ONE.

If you think you just have to do it with one cursor, here is my simplified test case that shows the logic:
/*
drop table tab1 purge;
create table tab1(col1 char(1));

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

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

insert into tab2 values('b');
commit;
*/


--only selects from tab1 data because only 'a' exists there
select col1 from tab1 where col1='a'
union all
select col1 from tab2 where col1='a' and not exists(select 1 from tab1 where col1='a')
/


--only selects from tab2 data because only 'b' exists there
select col1 from tab1 where col1='b'
union all
select col1 from tab2 where col1='b' and not exists(select 1 from tab1 where col1='b')
/

Open in new window


I don't have your tables or sample data so I cannot test this but using your original cursor it should be close:
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
   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 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
	)
;

Open in new window

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
Pavel GushchinProgrammerCommented:
You need to do so:

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 > 0) or (sel = 2 and num1 = 0))) or
           (v_key = 4 and ((sel = 2 and num2 > 0) or (sel = 1 and num2 = 0)));
           

Open in new window


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
NerdsOfTechTechnology ScientistCommented:
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).

 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

Open in new window

slightwv (䄆 Netminder) Commented:
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.
anumosesAuthor Commented:
still will work with one cursor with union all or have 2 separate cursors?
anumosesAuthor Commented:
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;   

Open in new window


 for zzz in c5 loop
----- If this is null then
for yyy  in c5A loop

Please let me know this as to exiting the 1st loop if no data found and open the 2nd cursor.
slightwv (䄆 Netminder) Commented:
Again:  You "can" do it with a single cursor but it will ALWAYS access one table twice and the other table once.  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.  In my opinion:  Two cursors is the most efficient method.

If you still want the single cursor, I already posted what should work.  At a minimum, I posted a test case showing it working that you should be able to adapt to your situation.  Remember, I cannot post 100% tested code since I don't have your tables or data.
anumosesAuthor Commented:
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;

Open in new window


will this work?
slightwv (䄆 Netminder) Commented:
Why are you nesting the cursors?

Loop through the first cursor and set a flag AFTER the exit statement.  If the flag has a value then the first cursor had rows so no need to loop through the second.  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?  The variable can only contain a single value.  If the cursor returns 100 rows, you still only ever have a SINGLE value held in v_inbound_part.
Pavel GushchinProgrammerCommented:
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!
anumosesAuthor Commented:
Pavel I understand, but when I open the cursor what is the value I have to pass?
NerdsOfTechTechnology ScientistCommented:
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 : #a42414348

(the code might need to be encased in a SELECT, for it to work)

The XP (execution plan) would be simplified this way, correct?
Pavel GushchinProgrammerCommented:
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.
slightwv (䄆 Netminder) Commented:
>>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.  You don't know which result you want at the time of execution.  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.

>>query is checked once using NOT EXISTS

You can use NOT EXISTS which is why I had already posted it in #a42413424.

You don't need the CASE again, because the KEY doesn't have any purpose.  

The requirements are clear:
Execute the first query and it it doesn't return any rows, execute the second.

The key/flag is meaningless.  Please stop trying to use it.
anumosesAuthor Commented:
thanks
NerdsOfTechTechnology ScientistCommented:
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.
slightwv (䄆 Netminder) Commented:
>> 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 #a42412914

Return first query.  If that returns no rows, use the second query.
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.