Help formulating a query/criteria

josephtan2k · **Joined:** Sun Apr 05, 2009 8:22 am **Posts:** 2

I have a model/Object, aModel it has two columns/properties those two columns have the same type:

public class aModel
User origiUser
User substituteUser
...

basically a the model has two prperties of the same type, User (note that User model has property userID), and substituteUser is nullable.

now the problem is this, given a userID I would want to retrieve all aModels with the given userID. but you have to look at substituteUser first before checking origUser.

something like this.

select * from aModel where (if substitueUser is null use origUser.userID otherwise use substituteUser.userID) = :givenUserID

something like that.

I hope someone can help out..

Many thanks in advance.

mmerder · **Posted:** Sun Apr 05, 2009 5:23 pm

case-when is what you need in sql:

Code:

select * from Model where (case when substitueUser is null then origUser.userID else substituteUser.userID) = :givenUserID 

josephtan2k · **Joined:** Sun Apr 05, 2009 8:22 am **Posts:** 2

Thanks sir..
Thats very neat..
how about using this query with Criteria?

Thanks a lot sir..

mmerder · **Posted:** Tue Apr 07, 2009 11:36 am

AFAIK it's not possible in criteria...