Thanks Guido, Anjo, Wojingo for you help.
Removing both the order by or distinct both seem to work but now I understand why.
On 04/04/2006, at 8:24 PM, Guido Neitzer wrote: Maybe. Other DBMS, other problems ...
I had this problem with Frontbase about a year ago with one of the Wonder methods (ERXBatchingDisplayGroup?).
The class only fetched the ids but tried to apply an "order by" - which was a problem.
cug
On 04.04.2006, at 12:20 Uhr, Anjo Krank wrote:
I guess the problem is not the Order By, but rather the distinct. At least that was the problem with the Postgresql plugin (where we fixed it a long time ago)
Cheers, Anjo
Am 04.04.2006 um 12:15 schrieb Guido Neitzer:
On 04.04.2006, at 6:36 Uhr, Dave Elsner wrote:
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?
The sql (readable) is:
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
So, this is generally a bad idea. You have an "order by" clause on columns that aren't in the result set. As far as I know, this is against the sql standard. Not quite sure and most DBMS can handle that, but for me it's ugly.
I don't know why the plugin generated this, but imho it shouldn't.
cug
-- PharmaLine, Essen, GERMANY Software and Database Development
_______________________________________________ Do not post admin requests to the list. They will be ignored. Help/Unsubscribe/Update your Subscription:
-- PharmaLine, Essen, GERMANY Software and Database Development
|
