db2 error with sum()

Question

I am using the IBM tool 'JRS' to exctract data from 'RTC' with DB2. I have the folowing code, wich it works just fine:

SELECT  

    CASE WHEN (T3.REQUEST_TYPE = 'Corretiva' OR T3.REQUEST_TYPE = 'Corretiva Interna' )
    THEN (MAX(T1.ACTUAL_WORK) - MIN(T1.ACTUAL_WORK))/3600
    ELSE 0 
    END AS CORRETIVAS_TIME,
    (MAX(T1.ACTUAL_WORK) - MIN(T1.ACTUAL_WORK))/3600 AS TOTAL_TIME

FROM RICALM.VW_RQST_HISTORY T1 -- HISTORICO DA TAREFA
INNER JOIN RICALM.VW_RQST_HISTORY T0 -- HISTORICO ANTERIOR DA TAREFA
ON T0.REQUEST_HISTORY_ID = T1.PREV_REQUEST_HISTORY_ID
INNER JOIN RIDW.VW_REQUEST T2 -- TAREFA
ON T2.REQUEST_ID = T1.REQUEST_ID 
  INNER JOIN RIDW.VW_REQUEST_RELATIONAL_LINK LT1
  ON T2.REQUEST_ID = LT1.REQUEST1_ID AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'
    INNER JOIN RIDW.VW_REQUEST T3 -- CORRETIVA
    ON LT1.REQUEST2_ID = T3.REQUEST_ID AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'

WHERE (  YEAR(CURRENT_TIMESTAMP)*12 + MONTH(CURRENT_TIMESTAMP) = YEAR(T1.REC_DATETIME)*12 + MONTH(T1.REC_DATETIME)
) 
AND T1.ACTUAL_WORK <> T0.ACTUAL_WORK
AND  T2.REQUEST_TYPE = 'Tarefa'
AND (T3.REQUEST_CATEGORY_NAME = 'SIENGE/Manutenção Contínua/MC-COMCRC' OR
T3.REQUEST_CATEGORY_NAME = 'SIENGE/Manutenção Programada/MP-COMCRC') 
AND 
(T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0 AND T3.ISSOFTDELETED = 0)

GROUP BY T1.REFERENCE_ID,T3.REQUEST_TYPE

Resulting on the folowing table:

corretivas_time
total_time
0   0
0   6
0   0
0   6
0   0
1   1
4   4

Now I want to get the sum of each column to compare eachother, so I make this folowing selection:

SELECT  

    SUM(CASE WHEN (T3.REQUEST_TYPE = 'Corretiva' OR T3.REQUEST_TYPE = 'Corretiva Interna' )
    THEN (MAX(T1.ACTUAL_WORK) - MIN(T1.ACTUAL_WORK))/3600
    ELSE 0 
    END) AS CORRETIVAS_TIME,
    SUM((MAX(T1.ACTUAL_WORK) - MIN(T1.ACTUAL_WORK))/3600) AS TOTAL_TIME

And the rest is the same...

I get this error:

CRRGW5628E An com.ibm.db2.jcc.am.SqlSyntaxErrorException error occurred when validating the input SQL string, caused by DB2 SQL Error: SQLCODE=-112, SQLSTATE=42607, SQLERRMC=SUM, DRIVER=4.14.121.

I also tried this code:

SELECT SUM(SELECT  

    CASE WHEN (T3.REQUEST_TYPE = 'Corretiva' OR T3.REQUEST_TYPE = 'Corretiva Interna' )
    THEN (MAX(T1.ACTUAL_WORK) - MIN(T1.ACTUAL_WORK))/3600
    ELSE 0 
    END AS CORRETIVAS_TIME

With the rest being the same.

I got this error:

CRRGW5628E An com.foundationdb.sql.parser.SQLParserException error occurred when validating the input SQL string, caused by Encountered "" at line 2, column 12. Was expecting one of: .

mustaccio · Answer 1 · 2015-08-05T13:31:26.917

I'm almost sure you want something like this instead:

SELECT SUM(corretivas_time), SUM(total_time) FROM (
 SELECT  
    CASE WHEN (T3.REQUEST_TYPE = 'Corretiva' OR T3.REQUEST_TYPE = 'Corretiva Interna' )
    THEN (MAX(T1.ACTUAL_WORK) - MIN(T1.ACTUAL_WORK))/3600
    ELSE 0 
    END AS CORRETIVAS_TIME,
    (MAX(T1.ACTUAL_WORK) - MIN(T1.ACTUAL_WORK))/3600 AS TOTAL_TIME

 FROM RICALM.VW_RQST_HISTORY T1 -- HISTORICO DA TAREFA
 INNER JOIN RICALM.VW_RQST_HISTORY T0 -- HISTORICO ANTERIOR DA TAREFA
 ON T0.REQUEST_HISTORY_ID = T1.PREV_REQUEST_HISTORY_ID
 INNER JOIN RIDW.VW_REQUEST T2 -- TAREFA
 ON T2.REQUEST_ID = T1.REQUEST_ID 
  INNER JOIN RIDW.VW_REQUEST_RELATIONAL_LINK LT1
  ON T2.REQUEST_ID = LT1.REQUEST1_ID AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'
    INNER JOIN RIDW.VW_REQUEST T3 -- CORRETIVA
    ON LT1.REQUEST2_ID = T3.REQUEST_ID AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'

 WHERE (  YEAR(CURRENT_TIMESTAMP)*12 + MONTH(CURRENT_TIMESTAMP) = YEAR(T1.REC_DATETIME)*12 + MONTH(T1.REC_DATETIME)
 ) 
 AND T1.ACTUAL_WORK <> T0.ACTUAL_WORK
 AND  T2.REQUEST_TYPE = 'Tarefa'
 AND (T3.REQUEST_CATEGORY_NAME = 'SIENGE/Manutenção Contínua/MC-COMCRC' OR
 T3.REQUEST_CATEGORY_NAME = 'SIENGE/Manutenção Programada/MP-COMCRC') 
 AND 
 (T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0 AND T3.ISSOFTDELETED = 0)

 GROUP BY T1.REFERENCE_ID,T3.REQUEST_TYPE
) as t

This gave me the following error: CRRGW5628E An com.foundationdb.sql.parser.SQLParserException error occurred when validating the input SQL string, caused by Encountered "" at line 33, column 2. Was expecting one of: "as" ... .. — LUIZ, Aug 05 '15 at 12:04
That `com.foundationdb.sql.parser` is a piece of work, isn't it. Try adding a correlation clause at the end -- see the edited query. — mustaccio, Aug 05 '15 at 13:31

db2 error with sum()

1 Answers1