?
Solved

Problem adding sumed fields from separate nested select statements

Posted on 2009-02-22
20
Medium Priority
?
279 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:atprato
  • 9
  • 6
  • 4
  • +1
20 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23705204
>> it does not work<<
This is meaningless.  Give us an error message.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23705210
Or if no error, what are the results and what are the desired results.
0
 
LVL 1

Expert Comment

by:j8eyd
ID: 23705215

What error are you getting?
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:atprato
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

by:Sharath
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)

Open in new window

0
 
LVL 75

Expert Comment

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

Author Comment

by:atprato
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

by:Anthony Perkins
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

by:atprato
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

by:Sharath
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

by:atprato
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

by:Sharath
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)) 

Open in new window

0
 

Author Comment

by:atprato
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

by:Sharath
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))

Open in new window

0
 

Author Comment

by:atprato
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

by:atprato
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

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

Open in new window

0
 

Author Comment

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

Author Closing Comment

by:atprato
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

by:Sharath
ID: 23707910
you are welcome!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question