Re: Refactored queries needing validation of syntactic equivalence

Richard Huxton wrote:
> Mike Adams wrote:
>> So.....
>> The first query should pull all 'MOM' records that have one or more
>> corresponding, and possibly orphaned, unassigned receiving records
>> belonging to the same po_cd and item_cd.
>>
>> The second query should pull all unassigned, and possibly orphaned
>> receiving records that have one or more corresponding 'MOM' records once
>> again matching on po_cd and item_cd.
>>
>> Using the results of both queries to double check each other, I can
>> figure out which (if any) open records are, in fact, orphans and do an
>> "after the fact" assignment to the "SPLIT IN MOM" invoice to reduce our
>> accrual.
>>
>> Of course, our ERMS should take care of this automagically; but,
>> tragically, it seems "real" inventory cost flow was attached to the
>> system using duct tape, hot glue, and a couple of thumb tacks.
>>
>> So, given all the administriva above, have I actually refactored them
>> correctly?
> 
> Well, clearly you could have multiple possible matches, because apart 
> from anything else you could in theory have multiple entries with the 
> same item-code on the same purchase-order-code. In practice it will be 
> rare, but it could happen.
> 

Yep! and it's not rare: if we receive 20 serialized items, we *will* get 
20 entries of same "itm_cd,po_cd" as serialized items are individually 
posted in inventory (in accounting speak, they have a "specific item" 
costing basis, whereas "non serialized" items (parts etc) are (by us) 
considered to have a "FIFO" costing basis and can be lumped into "lots").

Yesterday I ran both the "legacy" and "refactor" versions of each query 
after the AP clerk (for once) let me know that her assistant had 
"joined" a number of receivings (did the reverse of a split for some 
reason).

The "orphans" query (select o.co_cd, ...) came back with the same result 
set for both the legacy and refactor versions. The "moms" query (select 
m.co_cd, ...) did not!

What I had for the "moms" result sets were (fake products replacing the 
real ones in the results below):

legacy              | refactor
--------------------+--------------------------------
2 hotplate          | 2 hotplate
6 scooper           | 2 hotplate                    | 6 scooper                    | 6 scooper                    | 6
scooper                   | 6 scooper                    | 6 scooper                    | 6 scooper
 

The "orphans" result sets were the same (faked products in results below):
       result set       -----------------       1 hotplate       1 hotplate       1 scooper       1 scooper       1
scooper      1 scooper       1 scooper       1 scooper
 

In truth those eight records returned by both "orphans" versions *were* 
actually orphaned by the *2* "moms" records that /do/ exist and were 
correctly reported by the legacy version... Oops! the refactored "moms" 
query is an unintentional (by me) cross product!

> However, since the purpose is to provide you with a list so you can make 
> manual changes there's no problem with that.
> 

Except for the unwanted cross productions!  Well, there isn't an 
available "natural" way to prevent that as the table /doesn't/ have a 
pkey or even a good candidate key.

What I did, and it did fix the result set to reflect reality, was change the   select o.co_cd, ... from ...
to   select distinct o.co_cd, ..., o.rowid from ...

rowid being Oracle's version of ctid and is the only "unique" item "in" 
the table ( oh the shame ).

> What I might be tempted to do is restrict the dates more - you had <= 
> '31 Oct 2007' I'd also apply >= '1 Aug 2007' (or whatever period is 
> reasonable). You can always run an unconstrained match once a month to 
> catch any that slip through the net, but presumably most will fall 
> within a 90-day period.
> 
> HTH

I may restrict the dates more, however the refactored queries both run 
in under 1000 ms, and given the rcv_mo table currently has >5 && <7 
years worth of historical data for them to plow through, and the plan is 
to only keep the data in the table for 7 yrs max...

Thank you for the help! I've appreciated it greatly!

Mike.