Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: Case statement in Group by clause doesn't work in DB2 10.1 but works in 9.7

    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.

    Error is

    “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 "REGION" 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”

    SELECT (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,
    COALESCE(SUM(DS_TOTAL_COST), 0) AS ACTUAL_EXPENSE,
    FROM ASPECT.GW_DMG_SUMM
    WHERE DS_COUNTRY_CD = PARM_COUNTRY_CD
    AND DS_PERIOD_TYPE = PARM_PERIOD_TYPE
    GROUP by (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)

    It works fine at below version.
    "DB2" uses 32 bits
    Inform "DB2 v9.7.200.358"
    Product name: "DB2 Express-C"
    License type: "Unwarranted"
    Expiry date: "Permanent"
    Product identifier: "db2expc"

    It doesn't work on below version

    "DB2" uses "64" bits
    Informational tokens are "DB2 v10.1.0.872"
    Product name: "DB2 Enterprise Server Edition"
    License type: "CPU Option"
    Expiry date: "Permanent"
    Product identifier: "db2ese"
    Version information: "10.1"

    Any help is highly appreciated.

    thanks
    Roop

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) You specified a comma before "FROM". Remove the comma.
    COALESCE(SUM(DS_TOTAL_COST), 0) AS ACTUAL_EXPENSE,
    FROM ASPECT.GW_DMG_SUMM
    (2) Rename an alias "AS REGION" to other name to identify the name in the error message was a column name in CASE expression or the alias.
    SQL0119N An expression starting with "REGION" specified in ...

  3. #3
    Join Date
    Oct 2012
    Posts
    3
    Please find below the query with the error code:
    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,
    COALESCE(SUM(DS_TOTAL_COST), 0) AS ACTUAL_EXPENSE
    FROM ASPECT.GW_DMG_SUMM, ASPECT.DEALER_MASTER
    WHERE
    DS_COUNTRY_CD = COUNTRY_CD
    AND DS_DLR_CD = DEALER_CDE_VEGA
    AND REGION LIKE LOC_REGION_H
    AND DEALER_GRP LIKE LOC_DG_H
    GROUP BY DS_YEAR, DS_PERIOD_TYPE, DS_PERIOD,
    (CASE PARM_REGION_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN REGION ELSE REGION END),
    (CASE PARM_DG_NAME WHEN 'Z1' THEN ' ' WHEN 'ALL' THEN DEALER_GRP ELSE DEALER_GRP END)
    ;

    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_GRP" 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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You wrote the following statements in the first post.
    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.
    So, it may be a bug in DB2 10.1 enterprise version.

    How about this as an alternative?
    Code:
    SELECT DS_YEAR         AS YEAR
         , DS_PERIOD_TYPE  AS PERIOD_TYPE
         , DS_PERIOD       AS PERIOD
         , REGION_unit     AS REGION
         , DEALER_GRP_unit AS DEALER_GRP
         , COALESCE(SUM(DS_TOTAL_COST) , 0) AS ACTUAL_EXPENSE
     FROM  (SELECT DS_YEAR
                 , DS_PERIOD_TYPE
                 , DS_PERIOD
                 , CASE PARM_REGION_NAME
                   WHEN 'Z1'  THEN ' '
                   WHEN 'ALL' THEN REGION
                   ELSE            REGION
                   END  AS REGION_unit
                 , CASE PARM_DG_NAME
                   WHEN 'Z1'  THEN ' '
                   WHEN 'ALL' THEN DEALER_GRP
                   ELSE            DEALER_GRP
                   END  AS DEALER_GRP_unit
                 , DS_TOTAL_COST
             FROM  ASPECT.GW_DMG_SUMM
                 , ASPECT.DEALER_MASTER
             WHERE DS_COUNTRY_CD = COUNTRY_CD
               AND DS_DLR_CD     = DEALER_CDE_VEGA
               AND REGION     LIKE LOC_REGION_H
               AND DEALER_GRP LIKE LOC_DG_H
           )
     GROUP BY
           DS_YEAR
         , DS_PERIOD_TYPE
         , DS_PERIOD
         , REGION_unit
         , DEALER_GRP_unit

  5. #5
    Join Date
    Oct 2012
    Posts
    3
    Thanks a lot ! It solved our problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •