Problem adding sumed fields from separate nested select statements

>> it does not work<<
This is meaningless.  Give us an error message.

Or if no error, what are the results and what are the desired results.

What error are you getting?

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.

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)

Select allOpen in new window

Perhaps you have Null values.  But unfortunately without seeing the actual result all we can do is hazard guesses.

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,

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

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

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

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

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

Select allOpen in new window

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

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

Select allOpen in new window

I get a sql syntax error.  That is a step in the right direction, any idea what the error is?

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.

I had to remove a set of () but that did the trick, thanks!!!!!!!!!

Just had to remove one set of () to get the desired math, but the sql solution was perfect!

you are welcome!