Re: SQL question

Maybe something like this:

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') 
   or lqall > 0 and 0 = (SELECT count(*)
                   FROM bpcsf/ela
                  WHERE AORD = ecl.lord
                    and ALINE =ecl.lline
                    and ATYPE ='C'))
   and lord = hord
   and hinuse <> 'Y'


  ----- Original Message ----- 
  From: Guy Henza 
  To: midrange-l@xxxxxxxxxxxx 
  Sent: Tuesday, August 08, 2006 2:20 PM
  Subject: SQL question


  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


  -- 
  This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
  To post a message email: MIDRANGE-L@xxxxxxxxxxxx
  To subscribe, unsubscribe, or change list options,
  visit: http://lists.midrange.com/mailman/listinfo/midrange-l
  or email: MIDRANGE-L-request@xxxxxxxxxxxx
  Before posting, please take a moment to review the archives
  at http://archive.midrange.com/midrange-l.