Dave Elsner wrote:
Hi,
After moving an existing application to Oracle 9i. I have encountered
some problems with the SQL being generated from WebObjects, it
generates the following SQL:
SELECT DISTINCT t0.academicGeneral, t0.accessLevel, t0.activeNameID,
t0.brandNewRecord, RTRIM(t0.building), t0.countryID, t0.degreeID,
t0.email, RTRIM(t0.fax), t0.fileName, t0.filePath, t0.groupID,
t0.hasFile, t0.personCode, RTRIM(t0.phone), RTRIM(t0.room), t0.pkID,
RTRIM(t0.serialNumber), RTRIM(t0.staffNumber), RTRIM(t0.title),
t0.tobeShown, t0.type, t0.updatedNightly, RTRIM(t0.URL), RTRIM
(t0.wumpusID) FROM PERSON t0, PSN_NAME T1 WHERE (t0.type = 3 AND
t0.accessLevel <> -1) AND t0.activeNameID = T1.pkID ORDER BY
T1.lastName ASC, T1.firstName ASC
This SQL produces an error saying:
Next exception:SQL State:42000 -- error code: 1791 -- msg: ORA-01791:
not a SELECTed expression
[snip]
If I remove the DISTINCT or the ORDER BY then it works fine. Is there
any reason the above SQL should not work and if so why is WO generating
incorrect SQL?
Google turned this up for the oracle error.
http://ora-01791.ora-code.com/
ORA-01791: not a SELECTed expression
Cause: There is an incorrect ORDER BY item. The query is a SELECT
DISTINCT query with an ORDER BY clause. In this context, all ORDER BY
items must be constants, SELECT list expressions, or expressions whose
operands are constants or SELECT list expressions.
Action: Remove the inappropriate ORDER BY item from the SELECT list and
retry the statement.
Some "discussion" about the issue here..
http://www.mcse.ms/archive26-2005-8-1798095.html
cheers,
-shaun
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
