IC90090: QUERY CONTAINING AN ORDER BY CLAUSE REFERENCING AN AGGREGATE FUNCTION MIGHT PRODUCE A SQL0901N

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, DB2 might produce SQL0901N error message
    if the following conditions are true:
    1. The query contains a group by clause
    2. The output column list contains an expression
    3. An order by clause is defined in the same subselect as the
    expression
    4. The order by clause contains a aggregation function of the
    expression in 3.
    
    eg.
    SELECT
      (  CASE   WHEN flag = 1   THEN    ID2  ELSE ''   END) AS ID
    FROM
      (
      SELECT
      ID as ID2,
      1 as flag
      FROM    temp
      ) a
    GROUP BY
      (  CASE   WHEN flag = 1   THEN  ID2  ELSE ''   END)
    ORDER BY
      min(ID) desc;
    
    Error message:
    SQL0901N  The SQL statement failed because of a non-severe
    system error. Subsequent SQL statements can be processed.
    (Reason "QNC in column function references complex expression".)
    SQLSTATE=58004
    
    db2diag.log messages:
    
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x8031000F=-2144272369=SQLNN_E_AMBIG
              "Conflicting or ambiguous elements in the command or
    an
    internal object"
    DATA #1 : String, 62 bytes
    An unexpected error was detected during statement compilation.
    DATA #2 : Boolean, 1 bytes
    true
    DATA #3 : Boolean, 1 bytes
    false
    DATA #4 : Boolean, 1 bytes
    false
    DATA #5 : Boolean, 1 bytes
    false
    DATA #6 : Hex integer, 4 bytes
    0x00000000
    DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 52
     sqlerrmc: QNC in column function references complex expression
     sqlerrp : SQLNQ002
     sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3)
    0x00000000
               (4) 0x00000000      (5) 0xFFFFFEDE      (6)
    0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
     sqlstate:
     DATA #8 : Hex integer, 4 bytes
    0x00000040
    DATA #9 : String, 244 bytes
    Compiler error stack for rc = -2144272369:
    sqlnn_cmpl[300]
    sqlnp_main[250]
    sqlnp_parser[510]
    sqlnp_smactn[100]
    sqlnq_sem_function_call[100]
    sqlnq_proc_agf[290]
    

Local fix

  • Add the aggregate expression in the order by clause into the
    select list.
    eg. rewritten query:
    select ID
    from (
    SELECT
      (  CASE  WHEN flag = 1  THEN    ID  ELSE ''  END) AS ID ,
      min ((  CASE  WHEN flag = 1  THEN    ID  ELSE ''  END)) as
    min_ID
    FROM
      (
      SELECT
        ID as ID2,
        1 AS flag
      FROM
        CAPO_IDGCBA.RDE_MONITOR_VIEW rqi
      ) a
      GROUP BY
         ( CASE  WHEN flag = 1  THEN  ID  ELSE ''  END)
     )
    ORDER BY
      min_ID desc;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 UDB Users                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v9.7 Fixpack 9                                *
    ****************************************************************
    

Problem conclusion

  • Problem first fixed in v9.7 Fixpack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC90090

  • 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

    2013-02-07

  • Closed date

    2013-12-30

  • Last modified date

    2013-12-30

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

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

    IC90093

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSY

       UP



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC90090

Modified date:

2013-12-30

Translate my page

Machine Translation

Content navigation