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