Can you order by a calculated field in hql

[rob.bentley]

Hi. Can anyone shed some light on whether the following is possible
please? I have a User class mapped to a table with the following
mapping. I've only included the relevant fields:

<class name="User" table="tblUsers">
...
<property name="Status" column="StatusID"
type="Mdl.Rcm.GroupPermissioning.Models.StatusType,
Mdl.Rcm.GroupPermissioning.Models"/>
<property name="ExpireDate" column="DateExpire" />
...
</class>

Mdl.Rcm.GroupPermissioning.Models.StatusType enum looks like this:
public enum StatusType
{
Active = 0,
Disabled = 1,
Deleted = 2,
}

The table structure is in a legacy database and is therefore hard to
modify. A user is considered to be active in the system only if the
ExpireDate is greater than the current system date and the Status is
Active. My requirement is to show all active (not expired and Status
== Active) users ordered by username and then all other inactive
(expired or not Status != Active) ordered by user name. I am having
trouble expressing this using hql or the Criteria api. Can anyone tell
me whether this is even possible or not?

I have been trying queries like the following one with no success. I'm
not even sure "case" is supported in hql?
from Mdl.Rcm.GroupPermissioning.Models.User user
case when
(user.Status <> Mdl.Rcm.GroupPermissioning.Models.Status.Active or
user.ExpireDate < sysdate)
then 0
else 1
end as user.InActive
order by user.InActive

Any help would be much appreciated.
Thanks

[Fabio Maulo]

perhaps the "case" clause should be used in "order by" clause instead the "from" ?

2010/2/23 rob.bentley <rob.b...@ukgateway.net>

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo