On 21.02.2008 00:25, joeNOSPAM@BEA.com wrote:
> Below is a complex query generated by a persistence system,
> and the relevant tables. This SQL is too complex for my skills,
> and it gets this failure:
>
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
>
> Is anyone good enough at SQL to see the obvious problem/fix? I can't
> even
> understand the idea that the order-by clause has a select... I tried
> adding
> t0.id to the order by, but no difference!
>
> thanks in advance!
> Joe Weinstein at BEA Systems
>
>
> CREATE TABLE PersistentMapHolder_testPCKeyStringValue
> (PERSISTENTMAPHOLDER_ID BIGINT, value VARCHAR(255),
> testPCKeyStringValue BIGINT)
>
> CREATE TABLE PersistentMapHolder (id BIGINT NOT NULL, PRIMARY KEY
> (id))
>
> SELECT DISTINCT t0.id,
> ( SELECT PersistentMapHolder_testPCKeyStringValue.value
> FROM PersistentMapHolder_testPCKeyStringValue
> WHERE
> PersistentMapHolder_testPCKeyStringValue.PERSISTEN TMAPHOLDER_ID =
> t0.id
> AND
> PersistentMapHolder_testPCKeyStringValue.testPCKey StringValue = 53)
> FROM PersistentMapHolder t0
> INNER JOIN PersistentMapHolder_testPCKeyStringValue t1
> ON t0.id = t1.PERSISTENTMAPHOLDER_ID
> WHERE (( SELECT PersistentMapHolder_testPCKeyStringValue.value
> FROM PersistentMapHolder_testPCKeyStringValue
> WHERE
> PersistentMapHolder_testPCKeyStringValue.PERSISTEN TMAPHOLDER_ID =
> t0.id
> AND
> PersistentMapHolder_testPCKeyStringValue.testPCKey StringValue = 53) IS
> NOT NULL)
> ORDER BY (
> SELECT PersistentMapHolder_testPCKeyStringValue.value
> FROM PersistentMapHolder_testPCKeyStringValue
> WHERE
> PersistentMapHolder_testPCKeyStringValue.PERSISTEN TMAPHOLDER_ID =
> t0.id
> AND
> PersistentMapHolder_testPCKeyStringValue.testPCKey StringValue = 53
> ) DESC

This is a nice tool to get properly formatted SQL:

http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm

Since you have just one column used for ordering:

(SELECT persistentmapholder_testpckeystringvalue.VALUE
FROM persistentmapholder_testpckeystringvalue
WHERE
persistentmapholder_testpckeystringvalue.persisten tmapholder_id = t0.id
AND
persistentmapholder_testpckeystringvalue.testpckey stringvalue = 53)

And this column does indeed appear in the SELECT list (if I'm not
completely blind). My guess would be that SQL Server does not detect
this situation. (Do you have a chance to test this against another
database, say Oracle? Just to get a "second opinion". :-))

I do wonder though why your persistence system does not generate a left
outer join here. Maybe you can change something in how you set up the
persistence system in order to get a different SQL statement.

Kind regards

robert