Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Replacing NOT IN statement problem for FB 3.0



Permlink Replies: 2 - Last Post: Jul 17, 2017 11:40 AM Last Post By: Jeff Overcash (...
Fred Fonar

Posts: 137
Registered: 3/16/00
Replacing NOT IN statement problem for FB 3.0
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 14, 2017 9:08 AM
I have procedure with NOT IN statements (3) which need to be replaced by NOT EXISTS and change double quotes to single quotes:
BEGIN
FOR
select op.order_no, k.item_no, count(k.Part_no), sum(k.item_qty*op.total_qty), 0
from kits k join Orders_product op on k.part_no=op.part_no
  join orders o on o.orderno=op.order_no
where o.status="OPEN"
group by op.order_no, k.item_no
union all
select op.order_no, op.part_no, count(op.part_no), sum(op.total_qty), 0
from Orders_product op join orders o on o.orderno=op.order_no
1) where o.status="OPEN" and op.part_no not in (select part_no from kits)
group by op.order_no, op.part_no
union all
select i.orderno, ik.item_no, count(sp.qty_ship), 0, sum(ik.item_qty*sp.qty_ship)
from invoices i join suborders_product sp on i.orderno=sp.orderno and (i.suborderno=sp.suborderno) 
join invoices_kit ik on ik.kit_no=sp.part_no and ik.invoiceno=i.invoiceno
join orders o on o.orderno=i.orderno
where o.status="OPEN"
group by i.orderno, ik.item_no
union all
select i.orderno, sp.part_no, count(sp.qty_ship), 0, sum(sp.qty_ship)
from invoices i join suborders_product sp on i.orderno=sp.orderno and (i.suborderno=sp.suborderno) 
join orders o on o.orderno=i.orderno
2) where o.status="OPEN" and sp.part_no not in (select kit_no from invoices_kit ik2 where ik2.invoiceno=i.invoiceno)
group by i.orderno, sp.part_no
union all
select i.orderno, ik.item_no, count(op.total_qty_ship), 0, sum(ik.item_qty*op.total_qty_ship)
from invoices i join orders_product op on
 i.orderno=op.order_no and i.suborderno is null
join invoices_kit ik on ik.kit_no=op.part_no and ik.invoiceno=i.invoiceno 
join orders o on o.orderno=op.order_no
where o.status="OPEN"
group by i.orderno, ik.item_no
union all
select i.orderno, op.part_no,  count(op.total_qty_ship), 0, sum(op.total_qty_ship)
from invoices i join orders_product op on
 i.orderno=op.order_no and i.suborderno is null join orders o on o.orderno=op.order_no
3) where o.status="OPEN" and op.part_no not in (select kit_no from invoices_kit ik2 where ik2.invoiceno=i.invoiceno)
group by i.orderno, op.part_no
INTO :ORDERNO, :PART_NO, :CNT, :QTY_ORDERED, :QTY_SENT
DO SUSPEND;
END


I tried change the code this way but system hang-up.
1) where o.status='OPEN' and not exists(select 1 from kits k inner join orders_product on op.part_no = k.part_no)
2) where o.status='OPEN' and not exists(select 1 from invoices_kit ik2 inner join suborders_product sp on sp.part_no = ik2.kit_no inner join invoices on ik2.invoiceno=i.invoiceno)
3) where o.status='OPEN' and not exists(select 1 from invoices_kit ik2 inner join orders_product op on op.part_no = ik2.kit_no inner join invoices on ik2.invoiceno=i.invoiceno) 
Fred Fonar

Posts: 137
Registered: 3/16/00
Re: Replacing NOT IN statement problem for FB 3.0
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 14, 2017 1:48 PM   in response to: Fred Fonar in response to: Fred Fonar
Fred Fonar wrote:
I have procedure with NOT IN statements (3) which need to be replaced by NOT EXISTS and change double quotes to single quotes:
BEGIN
FOR
select op.order_no, k.item_no, count(k.Part_no), sum(k.item_qty*op.total_qty), 0
from kits k join Orders_product op on k.part_no=op.part_no
  join orders o on o.orderno=op.order_no
where o.status="OPEN"
group by op.order_no, k.item_no
union all
select op.order_no, op.part_no, count(op.part_no), sum(op.total_qty), 0
from Orders_product op join orders o on o.orderno=op.order_no
1) where o.status="OPEN" and op.part_no not in (select part_no from kits)
group by op.order_no, op.part_no
union all
select i.orderno, ik.item_no, count(sp.qty_ship), 0, sum(ik.item_qty*sp.qty_ship)
from invoices i join suborders_product sp on i.orderno=sp.orderno and (i.suborderno=sp.suborderno) 
join invoices_kit ik on ik.kit_no=sp.part_no and ik.invoiceno=i.invoiceno
join orders o on o.orderno=i.orderno
where o.status="OPEN"
group by i.orderno, ik.item_no
union all
select i.orderno, sp.part_no, count(sp.qty_ship), 0, sum(sp.qty_ship)
from invoices i join suborders_product sp on i.orderno=sp.orderno and (i.suborderno=sp.suborderno) 
join orders o on o.orderno=i.orderno
2) where o.status="OPEN" and sp.part_no not in (select kit_no from invoices_kit ik2 where ik2.invoiceno=i.invoiceno)
group by i.orderno, sp.part_no
union all
select i.orderno, ik.item_no, count(op.total_qty_ship), 0, sum(ik.item_qty*op.total_qty_ship)
from invoices i join orders_product op on
 i.orderno=op.order_no and i.suborderno is null
join invoices_kit ik on ik.kit_no=op.part_no and ik.invoiceno=i.invoiceno 
join orders o on o.orderno=op.order_no
where o.status="OPEN"
group by i.orderno, ik.item_no
union all
select i.orderno, op.part_no,  count(op.total_qty_ship), 0, sum(op.total_qty_ship)
from invoices i join orders_product op on
 i.orderno=op.order_no and i.suborderno is null join orders o on o.orderno=op.order_no
3) where o.status="OPEN" and op.part_no not in (select kit_no from invoices_kit ik2 where ik2.invoiceno=i.invoiceno)
group by i.orderno, op.part_no
INTO :ORDERNO, :PART_NO, :CNT, :QTY_ORDERED, :QTY_SENT
DO SUSPEND;
END


I tried change the code this way but system hang-up.
1) where o.status='OPEN' and not exists(select 1 from kits k inner join orders_product on op.part_no = k.part_no)
2) where o.status='OPEN' and not exists(select 1 from invoices_kit ik2 inner join suborders_product sp on sp.part_no = ik2.kit_no inner join invoices on ik2.invoiceno=i.invoiceno)
3) where o.status='OPEN' and not exists(select 1 from invoices_kit ik2 inner join orders_product op on op.part_no = ik2.kit_no inner join invoices on ik2.invoiceno=i.invoiceno) 

I have tested 2 variants of replacement of 1):
- where o.status='OPEN' and not exists(select 1 from kits k left join ORDERS_PRODUCT on op.part_no = k.part_no)
It's very fast - 8 sec but produced 134 rows instead of 187 rows

- where o.status='OPEN' and not exists(select 1 from kits k where op.part_no = k.part_no)
Slow - 1 min 5 sec and correctly produced 187 rows

How can I fix first variant to match results?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Replacing NOT IN statement problem for FB 3.0
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 17, 2017 11:40 AM   in response to: Fred Fonar in response to: Fred Fonar
Fred Fonar wrote:
Fred Fonar wrote:
I have procedure with NOT IN statements (3) which need to be replaced by NOT EXISTS and change double quotes to single quotes:
BEGIN
FOR
select op.order_no, k.item_no, count(k.Part_no), sum(k.item_qty*op.total_qty), 0
from kits k join Orders_product op on k.part_no=op.part_no
  join orders o on o.orderno=op.order_no
where o.status="OPEN"
group by op.order_no, k.item_no
union all
select op.order_no, op.part_no, count(op.part_no), sum(op.total_qty), 0
from Orders_product op join orders o on o.orderno=op.order_no
1) where o.status="OPEN" and op.part_no not in (select part_no from kits)
group by op.order_no, op.part_no
union all
select i.orderno, ik.item_no, count(sp.qty_ship), 0, sum(ik.item_qty*sp.qty_ship)
from invoices i join suborders_product sp on i.orderno=sp.orderno and (i.suborderno=sp.suborderno) 
join invoices_kit ik on ik.kit_no=sp.part_no and ik.invoiceno=i.invoiceno
join orders o on o.orderno=i.orderno
where o.status="OPEN"
group by i.orderno, ik.item_no
union all
select i.orderno, sp.part_no, count(sp.qty_ship), 0, sum(sp.qty_ship)
from invoices i join suborders_product sp on i.orderno=sp.orderno and (i.suborderno=sp.suborderno) 
join orders o on o.orderno=i.orderno
2) where o.status="OPEN" and sp.part_no not in (select kit_no from invoices_kit ik2 where ik2.invoiceno=i.invoiceno)
group by i.orderno, sp.part_no
union all
select i.orderno, ik.item_no, count(op.total_qty_ship), 0, sum(ik.item_qty*op.total_qty_ship)
from invoices i join orders_product op on
 i.orderno=op.order_no and i.suborderno is null
join invoices_kit ik on ik.kit_no=op.part_no and ik.invoiceno=i.invoiceno 
join orders o on o.orderno=op.order_no
where o.status="OPEN"
group by i.orderno, ik.item_no
union all
select i.orderno, op.part_no,  count(op.total_qty_ship), 0, sum(op.total_qty_ship)
from invoices i join orders_product op on
 i.orderno=op.order_no and i.suborderno is null join orders o on o.orderno=op.order_no
3) where o.status="OPEN" and op.part_no not in (select kit_no from invoices_kit ik2 where ik2.invoiceno=i.invoiceno)
group by i.orderno, op.part_no
INTO :ORDERNO, :PART_NO, :CNT, :QTY_ORDERED, :QTY_SENT
DO SUSPEND;
END


I tried change the code this way but system hang-up.
1) where o.status='OPEN' and not exists(select 1 from kits k inner join orders_product on op.part_no = k.part_no)
2) where o.status='OPEN' and not exists(select 1 from invoices_kit ik2 inner join suborders_product sp on sp.part_no = ik2.kit_no inner join invoices on ik2.invoiceno=i.invoiceno)
3) where o.status='OPEN' and not exists(select 1 from invoices_kit ik2 inner join orders_product op on op.part_no = ik2.kit_no inner join invoices on ik2.invoiceno=i.invoiceno) 

I have tested 2 variants of replacement of 1):
- where o.status='OPEN' and not exists(select 1 from kits k left join ORDERS_PRODUCT on op.part_no = k.part_no)
It's very fast - 8 sec but produced 134 rows instead of 187 rows

- where o.status='OPEN' and not exists(select 1 from kits k where op.part_no = k.part_no)
Slow - 1 min 5 sec and correctly produced 187 rows

How can I fix first variant to match results?

You do not want to be doing left join, you want just join. left join is an
outer join which is not what you are wanting. None of that should be needed,

You need to start with the plan's from the old version and new and see what the
optimizer has changed. Without that you are just shooting in the dark hoping ot
hit something.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02