Re: SQL question

[Al Mac <macwheel99@xxxxxxxxxxx>]

   There are a number of bugs in BPCS, which you can ask about on BPCS-L,
   that can lead to such things as allocation and other pieces of data not in
   sync with each other, for which there is a menu of options to fix stuff
   out of sync, and appropriate times to run the fixes.

   Depending on your version of BPCS and what applications are running, the
   notion that there ought to be a logical relationship between ECL and ELA
   may be an illusion, because ECL is only one of many files that can feed
   into ELA.. 

   ELA is allocations, which could come from a variety of types of orders,
   BOM specifics, and coding that says which types of items should be ignored
   by which BPCS sub-systems.
   ECL is customer order line detail.
   Other files for other kinds of orders can feed into ELA.

   Also in addition to 'CL' for value of ECL record ide, you may need to also
   condition on the status of the order.

     I have this SQL statement where I'm trying to balance the allocation
     quantities between the ECL  file and the ELA file.  They are a one to
     many
     relationship.  The problem is when the ECL.LQALL has a value > 0 and
     there
     are no ELA records this SQL statement doesn't catch the out of balance
     condition.  How can I incorporate that condition into this SQL
     statement?

     SELECT *
       FROM bpcsf/ecl,bpcsf/ech
     WHERE lid ='CL'
        and lqall <> (SELECT sum(LQALL)
                        FROM bpcsf/ela
                       WHERE AORD = ecl.lord
                         and ALINE =ecl.lline
                         and ATYPE ='C')
        and lord = hord
        and hinuse <> 'Y'

     Thank you for your assistance.

     Regards,

     Guy Henza
     616 N Michigan Ave
     Howell MI 48843
     517.548.3563
     guyhenza@xxxxxxxxxxx

   -
   Al Macintyre
   http://en.wikipedia.org/wiki/User:AlMac
   http://www.ryze.com/go/Al9Mac
   BPCS/400 Computer Janitor ... see
   http://radio.weblogs.com/0107846/stories/2002/11/08/bpcsDocSources.html