Hi,

I need to sort a result set on a computed column. The computed column use to be coded like this:

case when myTable.orgId = 1 then myTable.orgName when myTable.orgId <> 1 and UPPER(Form.formLanguage) = 'F' then Organization.nameFrench when myTable.orgId <> 1 and UPPER(Form.formLanguage) = 'E' then Organization.nameEnglish end as organizationName

I upgraded from Hibernate 2.1.4 to 3.0.5 because I read in the change log that the CASE WHEN was implemented in version 3.0.4. Now, I tried to make that query work for a while but can't figure out how to do it. Hibernate always return the error "undefine alias case...".

Also, since I upgraded, all my HQL that have a "*=" or "=*" in the Where clause don't work... I have to use the org.hibernate.hql.classic.ClassicQueryTranslatorFactory avoid that problem.

I'm thinking, if I use the old SQL parser maybe the Case When will not work.

Thanks in advance for your help


Hibernate version:3.0.5

Name and version of the database you are using: Sybase ASE 12

Ok, I kept working at it and was able to get the CASE WHEN working. The problem was related to spaces between operators an operands like "this <> 1" had to be "this<>1".

Now, for the CASE WHEN to work I have to use the org.hibernate.hql.ast.ASTQueryTranslatorFactory. But that SQL parser does not understand "*=".


What can I use to replace this, in my WHERE clause? Do I have to use "left join" in the from clause?

Is there a way to make the "*=" to work when using the ASTQueryTranslatorFactory.

Thanks

huh?? I find this very, very hard to believe.



You should use ANSI-style outerjoins.

You should use ANSI-style outerjoins.[/quote]


I'm trying to translate my WHERE clause in to the ANSI equivalent FROM clause... Maybe you could help?

Here's what I'm trying to convert:

SELECT Address.lastName, Address.givenName, Cities.name, Provinces.name From Address Address, Cities Cities, Provinces Provinces
Where Address.cityCode *= Cities.code and Address.provinceCode *= Provinces.code

So, it's basically 1 main table with 2 code columns and I have to get the name for those codes if the codes exist in the code tables (Cities & Province).

Ex.result set:
Smith, Joe, Ottawa, Ontario.

Thanks