IBM Support

IC82396: SQL0119N WITH PARAMETER IN GROUP BY WHEN CREATING A SQL PROCEDURE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A SQL Procedure that has an SQL statement that uses the
    combination of a GROUP BY and a parameter value will fail with a
    SQL0119N error:
    
    SQL0119N  An expression starting with "C1" 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=12.
    SQLSTATE=42803
    
    For example the following CREATE PROCEDURE statement will fail:
    
    CREATE TABLE testtbl (C1 VARCHAR(30), C2 DECFLOAT)@
    
    CREATE PROCEDURE testproc()
    LANGUAGE SQL
    BEGIN
      DECLARE myval VARCHAR(150);
      DECLARE isnull VARCHAR(150);
       SELECT COALESCE(C1,isnull)
       INTO myval
       FROM testtbl
       GROUP BY COALESCE(C1,isnull);
      RETURN 0;
    END
    @
    

Local fix

  • Move the local variable into the from-clause and replace both
    references of the variable with a new correlation name, for
    example:
    
    CREATE PROCEDURE testproc()
    LANGUAGE SQL
    BEGIN
      DECLARE myval VARCHAR(150);
      DECLARE isnull VARCHAR(150);
       SELECT COALESCE(C1,T.ISNULL)
       INTO myval
       FROM testtbl, TABLE( VALUES( isnull ) ) T(ISNULL)
       GROUP BY COALESCE(C1,T.ISNULL);
      RETURN 0;
    END
    @
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7 fix pack 7                        *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 version 9.7 fix pack 7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC82396

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-03-30

  • Closed date

    2012-12-11

  • Last modified date

    2012-12-11

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IT01895

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC82396

Modified date: 11 December 2012