Solved

# Problem adding sumed fields from separate nested select statements

Posted on 2009-02-22
Medium Priority
279 Views
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)
``````
0
Question by:atprato
• 9
• 6
• 4
• +1

LVL 75

Expert Comment

ID: 23705204
>> it does not work<<
This is meaningless.  Give us an error message.
0

LVL 75

Expert Comment

ID: 23705210
Or if no error, what are the results and what are the desired results.
0

LVL 1

Expert Comment

ID: 23705215

What error are you getting?
0

Author Comment

ID: 23705552
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

LVL 41

Expert Comment

ID: 23706098
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)
``````
0

LVL 75

Expert Comment

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

Author Comment

ID: 23706596
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

LVL 75

Expert Comment

ID: 23706636
>>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

Author Comment

ID: 23706771
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

LVL 41

Expert Comment

ID: 23707163

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

Author Comment

ID: 23707278
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

LVL 41

Expert Comment

ID: 23707297
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))
``````
0

Author Comment

ID: 23707393
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

LVL 41

Expert Comment

ID: 23707407
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))
``````
0

Author Comment

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

Author Comment

ID: 23707730
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

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 23707769
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
``````
0

Author Comment

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

Author Closing Comment

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

LVL 41

Expert Comment

ID: 23707910
you are welcome!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as welâ€¦
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month16 days, 17 hours left to enroll