Case statement in Group by clause in IBM DB2 LUW 10.1

[roopsingh]

Hi,

I have below scenario. I am just trying to avoid multiple queries by using case statement in group by clause in a Stored Procedure. I am using host variables in the case statement to decide what field to group by on. This code works fine in DB2 9.7 LUW free version. As soon as I run it on DB2 10.1 enterprise version. It gives SQL code -119. When we checked further , it says it's a problem with the host variables we are using in Group by call.
Any help would be appreciated a lot.
SELECT DS_YEAR AS YEAR, DS_PERIOD_TYPE AS PERIOD_TYPE, DS_PERIOD AS PERIOD,
(CASE PARM_REGION_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN REGION ELSE REGION END) AS REGION,
(CASE PARM_DG_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN DEALER_GRP ELSE DEALER_GRP END) AS DEALER_GRP,
(CASE PARM_DLR_CD_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN DEALER_CDE_VEGA ELSE DEALER_CDE_VEGA END) AS DEALER_CODE,
(CASE PARM_DLR_CD_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN DEALER_NAME_ENG ELSE DEALER_NAME_ENG END) AS DEALER_NAME,
' ' AS BAUM,
' ' AS MODEL,
' ' AS CG,
' ' AS CG_DESCR,
' ' AS DC,
' ' AS DAMAGE_DESCR,
' ' AS TIS,
COALESCE(SUM(DS_TOTAL_COST), 0) AS ACTUAL_EXPENSE,
COALESCE(SUM(DS_NO_OF_DAMAGES), 0) AS NO_OF_DAMAGES,
DECIMAL(COALESCE(SUM(DS_TOTAL_COST), 0) / SUM(DS_NO_OF_DAMAGES), 17, 2) AS CPD,
0 AS BUDGET

FROM ASPECT.GW_DMG_SUMM, ASPECT.DEALER_MASTER
WHERE
DS_COUNTRY_CD = COUNTRY_CD
AND DS_DLR_CD = DEALER_CDE_VEGA
AND DS_COUNTRY_CD = PARM_COUNTRY_CD
AND DS_PERIOD_TYPE = PARM_PERIOD_TYPE
AND DS_GW_TYPE = PARM_GW_TYPE
AND ((DS_YEAR = LOC_MAX_YEAR AND DS_PERIOD <= LOC_MAX_PERIOD)
OR (DS_YEAR = LOC_MIN_YEAR AND DS_PERIOD >= LOC_MIN_PERIOD)
OR (DS_YEAR BETWEEN (LOC_MIN_YEAR + 1) AND (LOC_MAX_YEAR - 1)))
AND REGION LIKE LOC_REGION_H
AND DEALER_GRP LIKE LOC_DG_H
AND DS_DLR_CD LIKE LOC_DLR_H
GROUP BY DS_YEAR, DS_PERIOD_TYPE, DS_PERIOD,
(CASE PARM_DG_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN DEALER_GRP ELSE DEALER_GRP END) ,
(CASE PARM_REGION_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN REGION ELSE REGION END),
(CASE PARM_DLR_CD_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN DEALER_CDE_VEGA ELSE DEALER_CDE_VEGA END),
(CASE PARM_DLR_CD_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN DEALER_NAME_ENG ELSE DEALER_NAME_ENG END)
Roop

 

[SkipVought]

hi,

I think you want something like this...

(
 CASE WHEN PARM_DG_NAME ='Z1' THEN 
         ' ' 
      WHEN PARM_DG_NAME ='ALL' THEN 
         DEALER_GRP 
      ELSE 
         DEALER_GRP 
 END
)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[roopsingh]

Yes. you are right. but it's not accepting host variable in group by clause. It works fine in DB2 9.7 LUW free version. But it's not working in DB2 10.1 Enterprise version.

 

[fredericofonseca]

try and define them as local variables instead and assign these to the value of the host variables at the top of your proc.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[roopsingh]

Hi Frederico,

I tried your suggestion by putting hard coded value in local variables as well as parameter variables to local variable. It didn't work.

thanks
Roop

 

[fredericofonseca]

can you put the exact error given including all verbose data, not just the code, -119 seems to be related to a "having clause" and there is none on the sql given

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[roopsingh]

Hi,

Please find below the error I an getting :

“DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0119N An expression starting with "DEALER_NAME_ENG" specified in a SELECT
clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
column function and no GROUP BY clause is specified. LINE NUMBER=47.
SQLSTATE=42803”

Thanks,
Roop