Selecting from a non-persister source (like dual)

jennifer_lee · **Joined:** Fri Dec 19, 2003 9:55 pm **Posts:** 6

Hi,

First, some info about my environment:
Hibernate version: 2.1.4
Database: Oracle 9.2i

I'm using Oracle, so the following question is a bit oracle tinted, but should apply to other databases as well, only with some slight syntax differences.

1)
I'm trying to execute a statement in hql. The sql equivalent would be "SELECT sysdate FROM dual". I'm having a hard time figuring out how this would look like in hql.

The "sysdate" part of the query could be replaced by {fn NOW()}, giving:

Code:

select {fn NOW()} from dual

However, I'm not sure how to select stuff from a non-persister source like "dual" would be. The above of course does not work.

2) Also further, is it possible to use functions like oracle's "decode" in hql?

Thanks for any comments or suggestions,

Jen

anthony · **Posted:** Fri Aug 27, 2004 5:05 am

yes you can do this via aggregate functions or formula attributes in the mapping files

jennifer_lee · **Joined:** Fri Dec 19, 2003 9:55 pm **Posts:** 6

Doing this in the mapping files would not be an alternative for me. However, do you know of any examples or references for using aggregate functions to accompish this? I'm sorely in need of something concrete here ;-)

Jen

anthony · **Posted:** Fri Aug 27, 2004 5:17 am

this doesn't make much sense, if you need a call to a non-oo function, get the jdbc connexion from the session and do it in jdbc.

It's easier no?

jennifer_lee · **Joined:** Fri Dec 19, 2003 9:55 pm **Posts:** 6

OK, I see...

Apropos selecting the current date from the database, I have another question:

Would it be possible to fetch calcultated values using hql. Eg:

Code:

SELECT ({fn now()} - timetable.timecolumn) FROM Table timetable

(consider the above as pseudo-code)

... returning the time between now and a given timestamp column in the table "timetable" ?

tia...

gavin · **Posted:** Fri Aug 27, 2004 5:35 am

Hibernate doesn't currently support expressions in the SELECT clause. Hopefully we can address that limitation once we get the new query parser up and running.