HH> Is that a JBoss restriction or a SQL restriction? It's EJB-QL's restriction.
HH> PostgreSQL allows it, but I don't know if they are following the SQL spec on HH> that one. HH> Can you guys think of another way to get my desired results? Yes, we'll include it in the plan if it's not there yet. alex HH> Basically I want the distinct operation to not be case sensitive. I have a HH> list of strings... Some upper case, some lower case and I want them all to HH> sort alpha, without regard to case. HH> Thanks in advance, HH> 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
