At 07:39 AM 15/02/2008, Aage Johansen wrote:

>Helen Borrie wrote:
> >> ...
> >> How can I make my query working under Dialect 3?
> >
> > select * from USER_SESSION
> > WHERE (cast ('now' as timestamp) - LastPageTime) > (1/60);
> >
> > or
> >
> > select * from USER_SESSION
> > WHERE (CURRENT_TIMESTAMP - LastPageTime) > (1/60);
> >
>
>
>Note that (1/60) will evaluate to 0.

Oops, yes, there are trees in the forest!

Explanation: in dialect 1, integer/integer returns a double precision number whereas in dialect 3, it returns an integer.

select * from USER_SESSION
WHERE (cast ('now' as timestamp) - LastPageTime) > (1.0000/60);

Apply as much scale (within the limits) as you need to get a meaningful answer. This is not as obvious as it might seem, so make sure your expression isn't going to resolve to an (undetected) different result from what you expect.

(Timestamp - <anything valid>) gives a decimal(18,9) result in days, not minutes or seconds.

Do you really want to test for an interval greater than a sixtieth of a day?

./heLen