I'll ask Jeremy if he wants to work this out in in 3.2. Otherwise you'll have to wait for 4.0.
-dain
On Friday, February 7, 2003, at 09:03 PM, Rod Macpherson wrote:
PostgreSQL does not allow an aggregated field reference to appear in the
where clause.
SELECT DISTINCT t0_s.rec_num ... WHERE ... t0_s.rec_num=t1 ...
Guess DISTINCT by itself is considered an aggregate rather than a
pre-aggregate filter. Possible solutions: a) fix PostgreSQL so that it does
not treat DISTINCT as a bona fide aggregate in an of itself; b) fix
PostgreSQL so that it does not impose the "aggregated column cannot appear
in where clause" limitation; c) remote DISTINCT from your query since
rec_num ottabee unique anyway.
----- Original Message -----
From: "Hunter Hillegas" <[EMAIL PROTECTED]>
To: "JBoss User" <[EMAIL PROTECTED]>
Sent: Friday, February 07, 2003 1:11 PM
Subject: [JBoss-user] JBoss Generating Some Bad SQL
I am running my dev environment on JBoss 3.2 RC1...
I am using JBossQL to override a query.
My JBossQL statement is as follows:
SELECT DISTINCT OBJECT(s) FROM SampleRequestBean s, IN (s.lineItems) li
WHERE li.fulfillmentStatus = 'Shipped' AND li.followupDate <= ?1 AND
(li.customerProspectFeedback IS NULL OR li.customerProspectFeedback = '')
ORDER BY s.dateOfRequest DESC
This generates this SQL:
SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s,
sample_request_line_item t1_li WHERE (t1_li.fulfillment_status = 'Shipped'
AND t1_li.followup_date <= '2003-02-04 20:00:00.000000000+00' AND
(t1_li.customer_prospect_feedback IS NULL OR
t1_li.customer_prospect_feedback = '')) AND
(t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC
My database (PostgreSQL 7.2 and 7.3) complains as such:
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target
list
So... Any suggestion on how I can tweak this to get the correct SQL out of
JBossCMP? Gracias!
Hunter
-------------------------------------------------------
This SF.NET email is sponsored by:
SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See!
http://www.vasoftware.com
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
------------------------------------------------------- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
------------------------------------------------------- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
