Thank you Set :o)

>One dataset includes a date + time to switch on and a date + time to switch
>off. I would like to get the timestamp next time on and next time off. In
>the simplest case both times from one dataset are in the future. Now it can
>be, that the next time to switch on listed in one, the time to switch on in
>another dataset.
>
>---
>
>with TmpTable(tmp) as
>(select cast(d_ein || ' ' || z_ein as timestamp) as t_ein, cast(d_aus || '

'

>|| z_aus as timestamp) as t_aus from t_zeitschaltuhr
>where d_ein >= current_date or d_aus > current_date and id_zum = 50 and
>id_zue < 8 and aktiv = 1)
>
>select t_ein from TmpTable
>where t_ein > 'now'
>order by t_ein
>rows 1 /* next time to switch on */
>
>select t_aus from TmpTable
>where t_aus > 'now'
>order by t_aus
>rows 1 /* next time to switch off */
>
>---
>
>How can I read both selects at once?

with TmpTable(tmp) as
(select cast(d_ein || ' ' || z_ein as timestamp) as t_ein, cast(d_aus || ' '
|| z_aus as timestamp) as t_aus from t_zeitschaltuhr
where d_ein >= current_date
or (d_aus > current_date
and id_zum = 50
and id_zue < 8
and aktiv = 1)),
TmpEin(t_ein) as
(select t_ein from TmpTable
where t_ein > 'now'
order by t_ein
rows 1), /* next time to switch on */
TmpAus(t_aus) as
(select t_aus from TmpTable
where t_aus > 'now'
order by t_aus
rows 1) /* next time to switch off */

select te.t_ein, ta.t_aus
from TmpEin te
cross join TmpAus ta

In addition to what you asked, I've added parenthesis. I always prefer using
them when having both OR and AND within a WHERE or JOIN clause, since I've
been bitten too often by AND having a lower precedence than OR.

HTH,
Set





[Non-text portions of this message have been removed]