IC90093: 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:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 101 fix pack 3                        *
    ****************************************************************
    

Problem conclusion

  • Fix was included in DB2 version 101 fix pack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC90093

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-02-07

  • Closed date

    2013-10-07

  • Last modified date

    2013-10-07

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

    IC90090

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

  • RA10 PSY

       UP



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

10.1

Reference #:

IC90093

Modified date:

2013-10-07

Translate my page

Machine Translation

Content navigation