troubleshooting Question

oracle union in a query

Avatar of anumoses
anumosesFlag for United States of America asked on
DatabasesOracle DatabaseSQL
24 Comments1 Solution189 ViewsLast Modified:
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;   

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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 24 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 24 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros