Topic
2 replies Latest Post - ‏2013-02-15T16:12:04Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic SQL Error with DISTINCT and TRIM functions in Query with ORDER BY clause.

‏2013-02-14T21:27:00Z |
I have query A that fails with SQLCODE: -214, SQLSTATE: 42822
A. SELECT DISTINCT TRIM(STATE_CODE) FROM X.STATECOUNTRYCODES ORDER BY STATE_CODE

If I change the above query as version B, shown below, it works.
B. SELECT DISTINCT TRIM(STATE_CODE) FROM X.STATECOUNTRYCODES ORDER BY 1

The version C work fine too.
C. SELECT TRIM(STATE_CODE) FROM X.STATECOUNTRYCODES ORDER BY STATE_CODE

Why does Query A fail. Does DB2 loose the Column Name reference or is this a bug?

Thanks
Ashwin G
Updated on 2013-02-15T16:12:04Z at 2013-02-15T16:12:04Z by SystemAdmin
  • KayKanekowski
    KayKanekowski
    16 Posts
    ACCEPTED ANSWER

    Re: SQL Error with DISTINCT and TRIM functions in Query with ORDER BY clause.

    ‏2013-02-15T06:36:13Z  in response to SystemAdmin
    Hi Ashwin,
    the good news Oracle works in the same way. So i expect it works as designed.

    I suppose version C works because it is plain select on all rows of your table.
    The result set in the background contains all rows with all columns. But you did not show them, you show only trim(column). And you have a direct connection from the displayed values to the row and the non-displayed columns.

    If you do a distinct you have a complete different result set and no connection to any row. So there is no reference to the non-displayed columns.
    You only have this set of the displayed values. So order by 1 works on this result set, but no way to reference state_code.

    As a workaround you can do this
    SELECT DISTINCT TRIM(STATE_CODE) as STATE_CODE FROM X.STATECOUNTRYCODES ORDER BY STATE_CODE

    hth
    Kay
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: SQL Error with DISTINCT and TRIM functions in Query with ORDER BY clause.

    ‏2013-02-15T16:12:04Z  in response to SystemAdmin
    The explanation seems good. Prior to posting, I had qualified the expression using a Column Alias and it worked, like you suggest in the work around. I did not mention that in my earlier post.

    Thanks
    Ashwin