oracle union in a query
I have a cursor as below.
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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
c5(nvl(2,1)) is returned same object as c5(2)
and ... check this Select statement and see if it returns record(s)?
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
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?
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:
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.
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
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.
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
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
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.
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.
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:
I don't have your tables or sample data so I cannot test this but using your original cursor it should be close:
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')
/
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
)
;
You need to do so:
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
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)));
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
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.
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.
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;
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.
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;
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.
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!
Try it!
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?
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.
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.
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.
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.
That was assumed given the requirement way up top in #a42412914
Return first query. If that returns no rows, use the second query.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial