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
