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
