Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 60AA19F9F31 for ; Sat, 20 Oct 2007 10:59:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 54051-06 for ; Sat, 20 Oct 2007 10:59:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from alnrmhc16.comcast.net (alnrmhc16.comcast.net [204.127.225.96]) by postgresql.org (Postfix) with ESMTP id 15ED09F9F2F for ; Sat, 20 Oct 2007 10:59:54 -0300 (ADT) Received: from [127.0.0.1] (c-71-63-209-98.hsd1.mn.comcast.net[71.63.209.98]) by comcast.net (alnrmhc16) with ESMTP id <20071020054607b16006sp3oe>; Sat, 20 Oct 2007 05:46:07 +0000 Message-ID: <471995E7.2050902@comcast.net> Date: Sat, 20 Oct 2007 00:45:11 -0500 From: Mike Adams User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.6) Gecko/20070728 Thunderbird/2.0.0.6 Mnenhy/0.7.5.666 MIME-Version: 1.0 To: Richard Huxton CC: pgsql-sql Subject: Re: Refactored queries needing validation of syntactic equivalence References: <470D5F81.5090403@comcast.net> <470DCFC7.3090901@archonet.com> <47110414.6060907@comcast.net> <471475E8.4030505@archonet.com> In-Reply-To: <471475E8.4030505@archonet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/85 X-Sequence-Number: 29457 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.