Re: Refactored queries needing validation of syntactic equivalence - Mailing list pgsql-sql

From Richard Huxton
Subject Re: Refactored queries needing validation of syntactic equivalence
Date
Msg-id 471475E8.4030505@archonet.com
Whole thread Raw
In response to Re: Refactored queries needing validation of syntactic equivalence  (Mike Adams <madams55075@comcast.net>)
Responses Re: Refactored queries needing validation of syntactic equivalence
List pgsql-sql
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.

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

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
--   Richard Huxton  Archonet Ltd


pgsql-sql by date:

Previous
From: Mike Adams
Date:
Subject: Re: Refactored queries needing validation of syntactic equivalence
Next
From: "Patrick De Zlio"
Date:
Subject: Inconsistent sql result