Avatar of anumoses
Flag for United States of America

asked on 

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?
DatabasesOracle DatabaseSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon