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?
Pinned topic SQL Error with DISTINCT and TRIM functions in Query with ORDER BY clause.
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-02-15T16:12:04Z at 2013-02-15T16:12:04Z by SystemAdmin
KayKanekowski 20000043C916 Posts
Re: SQL Error with DISTINCT and TRIM functions in Query with ORDER BY clause.2013-02-15T06:36:13ZThis is the accepted answer. This is the accepted answer.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
SystemAdmin 110000D4XK17917 Posts
Re: SQL Error with DISTINCT and TRIM functions in Query with ORDER BY clause.2013-02-15T16:12:04ZThis is the accepted answer. This is the accepted answer.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.