IBM Support

JR29985: SQL0158N ERROR OCCURS WHEN SUBSELECT WITH WITH STATEMENT IS USED WITH ORDER BY INPUT SEQUENCE CLAUSE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Given the following sql:
    
    declare global temporary table t(i int);
    
    with a (i) as (
    select i
    from new table (
    insert into session.t (i)
    values (1), (3), (2), (4)
    )
    order by input sequence
    )
    select i
    from a;
    
    The second sql statement returns error SQL0158N:
    The number of columns specified for "USERNAME.A" is not the same
    as the number of columns in the result table.
    
    Error reported in db2diag.log is :
    
    2008-07-04-16.18.08.377873+120 I1112C943          LEVEL: Error
    PID     : 274572               TID  : 1           PROC : db2bp
    INSTANCE: username               NODE : 000
    APPID   : *LOCAL.username.080704141754
    FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
    DATA #1 : Hexdump, 136 bytes
    0x2FF22470 : 5351 4C43 4120 2020 0000 0088 FFFF FF62    SQLCA
    .......b
    0x2FF22480 : 0008 5544 4238 4931 2E41 2020 2020 2020
    ..username.A
    0x2FF22490 : 2020 2020 2020 2020 2020 2020 2020 2020
    0x2FF224A0 : 2020 2020 2020 2020 2020 2020 2020 2020
    0x2FF224B0 : 2020 2020 2020 2020 2020 2020 2020 2020
    0x2FF224C0 : 2020 2020 2020 2020 5351 4C4E 5131 4541
    SQLNQ1EA
    0x2FF224D0 : 801A 006D 0000 0000 0000 0000 0000 0000
    ...m............
    0x2FF224E0 : FFFF FF60 0000 0000 2020 2020 2020 2020    ...`....
    0x2FF224F0 : 2020 2034 3238 3131                           42811
    
    
    SQL0158N The number of columns specified for "USERNAME.A" is not
    the same as the number of columns in the result table.
    SQLSTATE=42811
    

Local fix

  • 1. Suppress WITH statement:
    declare global temporary table t(i int)
    select i from new table ( insert into session.t (i) values (1),
    (3),(2), (4) ) order by input sequence;
    
    
    I
    -----------
              1
              3
              2
              4
    
      4 record(s) selected.
    
    
    2. Use "order by 1" or "order by i":
    
    with a (i) as (
    select i
    from new table (
    insert into session.t (i)
    values (1), (3), (2), (4)
    )
    order by i
    )
    select i
    from a;
    

Problem summary

  • USER AFFECTED: ALL
    PROBLEM DESCRIPTION:SQL0158N ERROR OCCURS WHEN SUBSELECT WITH
    WITH STATEMENT IS USED WITH ORDER BY INPUT SEQUENCE CLAUSE
    PROBLEM SUMMARY:
    ERROR DESCRIPTION:
    Given the following sql:
    
    declare global temporary table t(i int);
    
    with a (i) as (
    select i
    from new table (
    insert into session.t (i)
    values (1), (3), (2), (4)
    )
    order by input sequence
    )
    select i
    from a;
    
    The second sql statement returns error SQL0158N:
    The number of columns specified for "USERNAME.A" is not the same
    as the number of columns in the result table.
    
    Error reported in db2diag.log is :
    
    2008-07-04-16.18.08.377873+120 I1112C943          LEVEL: Error
    PID     : 274572               TID  : 1           PROC : db2bp
    INSTANCE: username               NODE : 000
    APPID   : *LOCAL.username.080704141754
    FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
    DATA #1 : Hexdump, 136 bytes
    0x2FF22470 : 5351 4C43 4120 2020 0000 0088 FFFF FF62    SQLCA
    .......b
    0x2FF22480 : 0008 5544 4238 4931 2E41 2020 2020 2020
    ..username.A
    0x2FF22490 : 2020 2020 2020 2020 2020 2020 2020 2020
    0x2FF224A0 : 2020 2020 2020 2020 2020 2020 2020 2020
    0x2FF224B0 : 2020 2020 2020 2020 2020 2020 2020 2020
    0x2FF224C0 : 2020 2020 2020 2020 5351 4C4E 5131 4541
    SQLNQ1EA
    0x2FF224D0 : 801A 006D 0000 0000 0000 0000 0000 0000
    ...m............
    0x2FF224E0 : FFFF FF60 0000 0000 2020 2020 2020 2020    ...`....
    0x2FF224F0 : 2020 2034 3238 3131                           42811
    
    
    SQL0158N The number of columns specified for "USERNAME.A" is not
    the same as the number of columns in the result table.
    SQLSTATE=42811
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.50, FixPak 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR29985

  • Reported component name

    DB2 UDB ESE WIN

  • Reported component ID

    5765F4101

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-07-23

  • Closed date

    2009-03-09

  • Last modified date

    2009-03-09

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

    JR29984

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

Fix information

  • Fixed component name

    DB2 UDB ESE WIN

  • Fixed component ID

    5765F4101

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
09 March 2009