Hi all.

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

See if the query below will work. You may need to add DISTINCT, hard to tell
if you can have duplicates as one of the tables does not have a primary key.

SELECT T1.id,
T2.value
FROM PersistentMapHolder T1
INNER JOIN PersistentMapHolder_testPCKeyStringValue AS T2
ON T1.id = T2.persistentmapholder_id
WHERE T2.testPCKeyStringValue = 53
ORDER BY T2.value DESC

HTH,

Plamen Ratchev
http://www.SQLStudio.com

I think you may just have won the longest table name award.

You say the query was generated by the system. It certainly looks
like the clumsy queries I see when something is "generated". Since
you are not responsible I won't try to fix all the things that are
wrong with it. Here is the absolute minimum to avoid the error you
are getting. There are two approaches, one simpler, the other more
proper.

1) Change the ORDER BY so that it just says ORDER BY 2 DESC, instead
of including the (SELECT ....). In this case 2 means the second
column. Use of this feature is frowned upon, but it is the smallest
change that will fix the problem.

2) Assign an alias for the second column, and order by the alias name.
There are two formats for assigning an alias name. In your case they
look like:

AliasName = (SELECT....)

or

(SELECT .....) AS AliasName

In either case the ORDER BY becomes ORDER BY AliasName DESC.

Of course you should use a meaningful name in place of AliasName.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Feb 2008 15:25:47 -0800 (PST), "joeNOSPAM@BEA.com"
<joe.weinstein@gmail.com> wrote:

>Hi all.
>
>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.PERSISTE NTMAPHOLDER_ID =
>t0.id
> AND
>PersistentMapHolder_testPCKeyStringValue.testPCKe yStringValue = 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.PERSISTE NTMAPHOLDER_ID =
>t0.id
> AND
>PersistentMapHolder_testPCKeyStringValue.testPCKe yStringValue = 53) IS
>NOT NULL)
> ORDER BY (
> SELECT PersistentMapHolder_testPCKeyStringValue.value
> FROM PersistentMapHolder_testPCKeyStringValue
> WHERE
>PersistentMapHolder_testPCKeyStringValue.PERSISTE NTMAPHOLDER_ID =
>t0.id
> AND
>PersistentMapHolder_testPCKeyStringValue.testPCKe yStringValue = 53
> ) DESC

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