My request ..
For this query ..
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
I get this error.
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target
list
This seems like a bug. Why do I need date_of_request in the select
section? SQL Spec?
The response ..
No, it isn't. Consider
SELECT DISTINCT x FROM tab ORDER BY y;
Assuming there are multiple values of y for any given x, how would you
expect the result to be sorted? It's ill-defined.
regards, tom lane
On Fri, 2003-02-07 at 16:11, Hunter Hillegas wrote:
> 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
