BTW, EJB-QL doesn't have an UPPER function in the 2.1 spec.
-dain
On Tuesday, January 28, 2003, at 12:51 PM, Hunter Hillegas wrote:
Is that a JBoss restriction or a SQL restriction?
PostgreSQL allows it, but I don't know if they are following the SQL spec on
that one.
Can you guys think of another way to get my desired results?
Basically I want the distinct operation to not be case sensitive. I have a
list of strings... Some upper case, some lower case and I want them all to
sort alpha, without regard to case.
Thanks in advance,
Hunter
From: Alex Loubyansky <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Date: Tue, 28 Jan 2003 20:06:43 +0200
To: Hunter Hillegas <[EMAIL PROTECTED]>
Subject: Re: [JBoss-user] Getting Distinct to Use UCASE() or UPPER() in
JBossQL or DeclaredSQL??
Hunter,
you can't use functions in the SELECT, they are allowed only in
the WHERE clause.
alex
Tuesday, January 28, 2003, 7:47:06 PM, you wrote:
HH> I am trying to get the following query to the DB:
HH> SELECT DISTINCT UPPER(vendor) FROM sample_request_line_item WHERE vendor
IS
HH> NOT NULL ORDER BY vendor;
HH> EJBQL has no UPPER or UCASE, so it is out.
HH> I tried JBossQL as such:
HH> SELECT DISTINCT UCASE(s.vendor) FROM SampleRequestLineItemBean s WHERE
HH> s.vendor IS NOT NULL ORDER BY s.vendor
HH> That resulted in a parse error:
HH> Caused by: org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered
HH> "UCASE" at line 1, column 17.
HH> Was expecting one of:
HH> "OBJECT" ...
HH> <NUMERIC_VALUED_PATH> ...
HH> <STRING_VALUED_PATH> ...
HH> <DATETIME_VALUED_PATH> ...
HH> <BOOLEAN_VALUED_PATH> ...
HH> <ENTITY_VALUED_PATH> ...
HH> <VALUE_CLASS_VALUED_PATH> ...
HH> Ok.... So I am now looking at declaredSQL from Dain's docs...
HH> But to specify distinct there, you just insert a tag. Also, the select
tags
HH> just contain the EJB name and would look like this:
HH> <declared-sql>
HH> <select>
HH> </distinct>
HH> <ejb-name>SampleRequestLineItemBean</ejb-name>
HH> <alias>lineItem</alias>
HH> </select>
HH> ...
HH> </declared-sql>
HH> It doesn’t look like I can insert the logic there...
HH> Any ideas? The raw SQL runs on the database (PostgreSQL) just fine...
HH> JBoss 3.2.0 RC1 on MacOS X 10.2.x.
HH> Thanks,
HH> 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
