RE: SQL question -- MIDRANGE-L

This is a interesting problem as the usual first thought, to make use of
the coalesce function like so, doesn't work:

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

The reason is that the SELECT is returning no rows as opposed to a null
field.

You need to think about what you are asking.

Here's one way:

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


Another way is to change the subselect so that it always returns a row.
SELECT *
  FROM bpcsf/ecl,bpcsf/ech
WHERE lid ='CL'
   and lqall <> (SELECT coalesce(sum(LQALL),0)
                   FROM sysibm/sysdummy1 left outer join bpcsf/ela
                          on 1 = 1
                  WHERE AORD = ecl.lord
                    and ALINE =ecl.lline
                    and ATYPE ='C')
   and lord = hord
   and hinuse <> 'Y'


The one row dummy table provided by IBM comes in handy in more than just
this problem.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Guy Henza
Sent: Tuesday, August 08, 2006 2:21 PM
To: midrange-l@xxxxxxxxxxxx
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.