RE: SQL question -- MIDRANGE-L

Rob,

Cool, is this new in v5r4?  I would have sworn that coalesce didn't work
this way.

But I don't see any changes in the SQL reference concerning this change
in behavior.

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 rob@xxxxxxxxx
Sent: Tuesday, August 08, 2006 3:08 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL question

When there are no hits you are getting a null and SQL handles nulls 
correctly.  You need to replace the null with a default 
value.  Given this 
sample data:

SELECT * FROM QTEMP/ECL
....+....1....+....2....+....3....+....4...
   LORD   LLINE                 LQALL   LID
      1       1               5.00000   CL 
      1       2               8.00000   CL 
      1       3               4.00000   CL 

SELECT * FROM QTEMP/ELA
....+....1....+....2....+....3....+....4....+
ATYPE     AORD   ALINE                  AALL 
  C          1       1               2.00000 
  C          1       1               3.00000 
  C          1       2               3.00000 
  C          1       2               7.00000 

SELECT * FROM QTEMP/ECL 
WHERE LID='CL' 
  AND LQALL<>(SELECT SUM(AALL) 
                FROM QTEMP/ELA 
               WHERE AORD=ECL.LORD 
                 AND ALINE=ECL.LLINE
                 AND ATYPE='C') 
....+....1....+....2....+....3....+....4...
   LORD   LLINE                 LQALL   LID
      1       2               8.00000   CL 


SELECT * FROM QTEMP/ECL 
WHERE LID='CL' 
  AND LQALL<>(SELECT COALESCE(SUM(AALL),0)
                FROM QTEMP/ELA 
               WHERE AORD=ECL.LORD 
                 AND ALINE=ECL.LLINE 
                 AND ATYPE='C') 
....+....1....+....2....+....3....+....4...
   LORD   LLINE                 LQALL   LID
      1       2               8.00000   CL 
      1       3               4.00000   CL 

Notice the difference?


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Guy Henza" <guyhenza@xxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
08/08/2006 02:20 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

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.


-- 
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.