Problem adding sumed fields from separate nested select statements

The below code works fine.  But when I try to perform math funtions + * / - on the last 6 columns it does not work.  Maybe because they are sumed fields?  The column I want to add is:
.....,T2.RREM*(T3.RDT/(T3.RDT + T4.SDE + T5.ADS + T6.URE + T7.GDE + T8.ALLIED))
or
.....,T2.RREM*(T3.RDT/(T3.RDT + T4.SDE + T5.ADS + T6.URE + T7.GDE + T8.ALLIED+.01)) to avoid dividing by zero (maybe there is another way around that separate issue?)





SELECT T2.RCUST, T2.CNME, T2.CTYPE, T2.CCON, T2.CPHON, T2.CXFAX, T2.CXEML, T2.SNAME, T2.CRDOL, T2.CXSTPF, T2.CSTE, T2.CLPDT, T2.CLPAM, T2.RINVC, T2.RAMT, T2.RIDTE, T2.RREM, T2.RDDTE, T2.CTERM, T2.ARCPO, T2.RREF, T2.AXDSPF, T3.RDT, T4.SDE, T5.ADS, T6.URE, T7.GDE, T8.ALLIED
 
FROM (((((((SELECT * FROM RCX, RCM, SSM, RARL01 LEFT JOIN (SELECT * FROM RAXL01 WHERE AXDSPF<>'') AS T1 ON RINVC = T1.AXIVNM WHERE RCUST=CXCUSN AND RCUST=CCUST AND RREM<>0 AND CTYPE<>'INTC' AND CTYPE<>'INTB' AND SSAL = CSAL) AS T2 LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS RDT FROM SSD, ICX WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC='RDT' GROUP BY SDINV) AS T3 ON T2.RINVC = T3.SDINV) LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS SDE FROM SSD, ICX WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC='SDE' GROUP BY SDINV) AS T4 ON T2.RINVC = T4.SDINV) LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS ADS FROM SSD, ICX WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC='ADS' GROUP BY SDINV) AS T5 ON T2.RINVC = T5.SDINV) LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS URE FROM SSD, ICX WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC IN ('LHD','TME') GROUP BY SDINV) AS T6 ON T2.RINVC = T6.SDINV) LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS GDE FROM SSD, ICX WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC ='GDE' GROUP BY SDINV) AS T7 ON T2.RINVC = T7.SDINV) LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS ALLIED FROM SSD, ICX WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC IN ('CTO','HCN','RCA','LCC','AIP','AII','AIF','CTS','ASR') GROUP BY SDINV) AS T8 ON T2.RINVC = T8.SDINV)

Open in new window

atpratoAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
check this

SELECT ...,
(CASE WHEN T2.RREM IS NOT NULL THEN T2.RREM ELSE 0 END)*((CASE WHEN T3.RDT IS NOT NULL THEN T3.RDT ELSE 0 END)/((CASE WHEN T3.RDT IS NOT NULL THEN T3.RDT ELSE 0 END) + (CASE WHEN T4.SDE IS NOT NULL THEN T4.SDE ELSE 0 END) + (CASE WHEN T5.ADS IS NOT NULL THEN T5.ADS ELSE 0 END) + (CASE WHEN T6.URE IS NOT NULL THEN T6.URE ELSE 0 END) + (CASE WHEN T7.GDE IS NOT NULL THEN T7.GDE ELSE 0 END) + (CASE WHEN T8.ALLIED IS NOT NULL THEN T8.ALLIED ELSE 0 END)+.01)) AS Computed_Column

Open in new window

0
 
Anthony PerkinsCommented:
>> it does not work<<
This is meaningless.  Give us an error message.
0
 
Anthony PerkinsCommented:
Or if no error, what are the results and what are the desired results.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
j8eydCommented:

What error are you getting?
0
 
atpratoAuthor Commented:
There is no error, the result column I added is blank.  The result should be some fraction of RREM.  I'm actually suprised the first version of the result column does not error since it would be dividing by zero for some records.
0
 
SharathData EngineerCommented:
Is this a round of problem? Are you getting blank value or zero for your computed column. try like this.
SELECT T2.RCUST, T2.CNME, T2.CTYPE, T2.CCON, T2.CPHON, T2.CXFAX, T2.CXEML, T2.SNAME, T2.CRDOL, T2.CXSTPF, T2.CSTE, T2.CLPDT, T2.CLPAM, 
       T2.RINVC, T2.RAMT, T2.RIDTE, T2.RREM, T2.RDDTE, T2.CTERM, T2.ARCPO, T2.RREF, T2.AXDSPF, T3.RDT, T4.SDE, T5.ADS, T6.URE, 
       T7.GDE, T8.ALLIED,
       VAL(FORMAT(T2.RREM*(T3.RDT/(T3.RDT + T4.SDE + T5.ADS + T6.URE + T7.GDE + T8.ALLIED+.01)))) AS Computed_Column
  FROM (((((((SELECT * FROM RCX, RCM, SSM, RARL01 
  LEFT JOIN (SELECT * FROM RAXL01 WHERE AXDSPF<>'') AS T1 
    ON RINVC = T1.AXIVNM 
 WHERE RCUST=CXCUSN AND RCUST=CCUST AND RREM<>0 AND CTYPE<>'INTC' AND CTYPE<>'INTB' AND SSAL = CSAL) AS T2 
 LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS RDT FROM SSD, ICX 
             WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC='RDT' 
             GROUP BY SDINV) AS T3 
   ON T2.RINVC = T3.SDINV) 
 LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS SDE FROM SSD, ICX 
             WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC='SDE' 
             GROUP BY SDINV) AS T4 
   ON T2.RINVC = T4.SDINV) 
 LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS ADS FROM SSD, ICX 
             WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC='ADS' 
             GROUP BY SDINV) AS T5 
   ON T2.RINVC = T5.SDINV) 
 LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS URE FROM SSD, ICX 
             WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC IN ('LHD','TME') 
             GROUP BY SDINV) AS T6 
   ON T2.RINVC = T6.SDINV) 
 LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS GDE FROM SSD, ICX 
             WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS AND CXPPLC ='GDE' 
             GROUP BY SDINV) AS T7 ON T2.RINVC = T7.SDINV) 
 LEFT JOIN (SELECT SDINV, SUM(SDSVAL) AS ALLIED FROM SSD, ICX 
             WHERE SDRCID='SI' AND SUBSTRING(SDHPRF,1,2)<>'98' AND SDICLS = CXCLAS 
               AND CXPPLC IN ('CTO','HCN','RCA','LCC','AIP','AII','AIF','CTS','ASR') 
             GROUP BY SDINV) AS T8 ON T2.RINVC = T8.SDINV)

Open in new window

0
 
Anthony PerkinsCommented:
Perhaps you have Null values.  But unfortunately without seeing the actual result all we can do is hazard guesses.
0
 
atpratoAuthor Commented:
acperkins, T2 has records that are not in T3-T8, so I guess T3-T8 would be null for those cases.  Does that give you any ideas?  Also, the fields I am calling from T3-T8 are sumed fields, does that affect anything?

Sharath, sorry, when I add ,VAL(FORMAT(T2.RREM*(T3.RDT/(T3.RDT + T4.SDE + T5.ADS + T6.URE + T7.GDE + T8.ALLIED+.01)))) AS Computed_Column I get an OBDC error. I am using VBA to push the sql to AS400.  You didn't change anything else did you?

acperkins,
0
 
Anthony PerkinsCommented:
>>T2 has records that are not in T3-T8, so I guess T3-T8 would be null for those cases. <<
That is correct.  They would be Null and the result of dividing by a NULL value is Null.  You can use ISNULL(YourColumn, 0) to ensure that it is 0
0
 
atpratoAuthor Commented:
OK, to keep it very simple, I replaced .....,T3.RDT,..... in the original query that works with .....,ISNULL(T3.RDT,0) AS TEST_NULL,.....  I got another ODBC error.  It seems like whenever I put these sumed fields into a function they fail?  When I have just T3.RDT in the working query it results NULL or a value depending on if the record was in T2.  There must be some reason I can't put these fields in a function......
0
 
SharathData EngineerCommented:

Can you just add this column to your query and check what values you are getting?
SELECT ......,
(T3.RDT + T4.SDE + T5.ADS + T6.URE + T7.GDE + T8.ALLIED+.01) AS Computed_1
0
 
atpratoAuthor Commented:
I did it, the entire excel column comes up blank(not zero).  But there is no record where all 6 have a value.  So I tried

(T4.SDE + T6.URE) AS Computed_1
This worked for records where SDE and URE had a value.  But was blank where neither had a value or one has a value.  I think this confirms our Null theory, but I don't know how to get around it because these fields error out when I put them in a function....
0
 
SharathData EngineerCommented:
try this
ISNULL(T2.RREM,0)*(ISNULL(T3.RDT,0)/(ISNULL(T3.RDT,0) + ISNULL(T4.SDE,0) + ISNULL(T5.ADS,0) + ISNULL(T6.URE,0) + ISNULL(T7.GDE,0) + ISNULL(T8.ALLIED,0)+.01)) 

Open in new window

0
 
atpratoAuthor Commented:
I get an ODBC error, but as I said above, even if I just do:
ISNULL(T3.RDT,0) AS TEST_NULL
I get an ODBC error.  Yet with just T3.RDT, I get a column with some values and some Null.  This is really strange....
0
 
SharathData EngineerCommented:
try this
IIF(T2.RREM IS NOT NULL,T2.RREM,0)*(IIF(T3.RDT IS NOT NULL,T3.RDT,0)/(IIF(T3.RDT IS NOT NULL,T3.RDT,0) + IIF(T4.SDE IS NOT NULL,T4.SDE,0) + IIF(T5.ADS IS NOT NULL,T5.ADS,0) + IIF(T6.URE IS NOT NULL,T6.URE,0) + IIF(T7.GDE IS NOT NULL,T7.GDE,0) + IIF(T8.ALLIED IS NOT NULL,T8.ALLIED,0)+.01))

Open in new window

0
 
atpratoAuthor Commented:
I get a sql syntax error.  That is a step in the right direction, any idea what the error is?
0
 
atpratoAuthor Commented:
oK IIF did not work but I got CASE to work in the same way to return 0s for null values.  So now my issues is, how do I add case statements together in the select statement.
0
 
atpratoAuthor Commented:
I had to remove a set of () but that did the trick, thanks!!!!!!!!!
0
 
atpratoAuthor Commented:
Just had to remove one set of () to get the desired math, but the sql solution was perfect!
0
 
SharathData EngineerCommented:
you are welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.