oracle union in a query

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
     

Select all Open in new window

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?

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

Select all 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.

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

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

orditm_id_rcvd` and `in_recshp_id`

Orditm_id_rcvd is from built items table and in_recshp_id is from received shipments table

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.

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)));
           

Select all 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

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

Select all Open in new window

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.

still will work with one cursor with union all or have 2 separate cursors?

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;   

Select all 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.

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.

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;

Select all Open in new window

will this work?

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.

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!

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

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?

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.

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

thanks

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.

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