Can someone tell me what is wrong with this query?

[Jehan Jaleel]

Hello everyone,
I am trying to run the following query in DB2...

SELECT distinct DS.DUP_SONG_RESOLUTION_ID as dupSongResId
FROM DB2INST1.SONG_TITLE TITLE, DUPSONG.DUP_SONG_RESOLUTION DS
WHERE TITLE.SONG_TITLE_NM like '%love%'
AND TITLE.TITLE_TYPE_CD='OT'
AND DS.STATUS_CODE <>'5'
AND DS.STATUS_CODE <>'6'
AND (TITLE.SONG_CD =DS.MASTER_SONG_CODE
or TITLE.SONG_CD =DS.DUP_SONG_CODE)
ORDER BY DS.DUP_SONG_CODE

and it gives the following error...

17:07:07 [SELECT - 0 row(s), 0.000 secs] [Error Code: -214, SQL State: 42822] An expression in the ORDER BY clause in the following position, or starting with "DS" in the "ORDER BY" clause is not valid. Reason code = "2".
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

Any suggestions on what is wrong with it?

Thanks in advance for any help.

[Paul Clapham]

My guess? You can't order by a column that you haven't selected. (Doing that might make sense in some contexts but not in a grouping query.)

[Scott Selikoff]

Tip for Query-based errors? Take them out of JDBC! Open up a nice GUI SQL client (preferably one with syntax highlighting) and fix the query there. It's a lot faster than rewriting/rebuilding/redeploying an existing application to solve what really isn't a JDBC issue.