[sqr-users] How to return a Zero, instead of a NULL in a sub-query?

[marc pechaitis <fromthehills@gmail.com>]

You guys have been great in the past, and I am sure you'll be able to
help on this one....I am running this sql statement and the problem I
have is with the code identified between the
"**********start**********" and "**********end**********":

INSERT %Table(NU_SR0037_IWTAO) 
SELECT DISTINCT A.EMPLID 
, B.NAME 
, C.DEPTID 
, C.LOCATION 
, '' 
, ( 
SELECT SUM(A2.NU_SR_CLASS_WEIGHT) 
 FROM PS_NU_SR0037_TMP A2 
 WHERE A.EMPLID = A2.EMPLID) , ( 
SELECT SUM(A3.NU_SR_CLASS_WEIGHT) 
 FROM PS_NU_SR0037_TMP A3 
 WHERE A.EMPLID = A3.EMPLID 
 AND A3.STRM >= %Bind(STRM_FROM) 
 AND A3.STRM < %Bind(STRM)) , 
**********start**********
(SELECT SUM(A4.NU_SR_CLASS_WEIGHT) 
FROM PS_NU_SR0037_TMP A4 
WHERE A.EMPLID = A4.EMPLID 
AND A4.STRM >= %Bind(STRM) 
AND A4.STRM <= %Bind(STRM_TO)) 
**********end**********
, '' , 0 
FROM PS_NU_SR0037_TMP A, PS_PERSONAL_DATA B, PS_JOB C 
WHERE A.EMPLID = B.EMPLID 
AND A.EMPLID = C.EMPLID 
AND A.EMPL_RCD = C.EMPL_RCD 
AND C.EFFDT = ( 
SELECT MAX(C2.EFFDT) 
FROM PS_JOB C2 
WHERE C.EMPLID = C2.EMPLID 
AND C.EMPL_RCD = C2.EMPL_RCD 
AND C.EFFDT = C2.EFFDT) 
AND C.EFFSEQ = ( 
SELECT MAX(C3.EFFSEQ) 
FROM PS_JOB C3 
WHERE C.EMPLID = C3.EMPLID 
AND C.EMPL_RCD = C3.EMPL_RCD 
AND C.EFFDT = C3.EFFDT) 
GROUP BY A.EMPLID, B.NAME, C.DEPTID, C.LOCATION


If the identified sub-query returns no values, the main statement
tries to insert a NULL in its place.  This is causing me problems
because it is attempting to insert a NULL into a Nbr type field.  Is
there anyway to have it insert a Zero, instead of a NULL when the
sub-query returns nothing?


Thanks,

Marc

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users