>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
