INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Case statement in Group by clause in IBM DB2 LUW 10.1

Case statement in Group by clause in IBM DB2 LUW 10.1

Case statement in Group by clause in IBM DB2 LUW 10.1

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

RE: Case statement in Group by clause in IBM DB2 LUW 10.1

hi,

I think you want something like this...

CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Case statement in Group by clause in IBM DB2 LUW 10.1

(OP)
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.

RE: Case statement in Group by clause in IBM DB2 LUW 10.1

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

RE: Case statement in Group by clause in IBM DB2 LUW 10.1

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close