Help with Oracle SQL query

[TeNDoLLA]

Hi,

I have a query:

SELECT MIN(t.start_date) as s_date, MAX(t.end_date) as e_date, t.obj, o.target FROM sss.times t
JOIN sss.obj o ON o.obj = t.obj
JOIN sss.target tar ON tar.target = o.target
WHERE t.obj IN (SELECT obj FROM sss.obj WHERE obj_type IN (1,2,10)) 
GROUP BY t.obj, o.target ORDER BY s_date, t.obj

This gives me results that looks ok. Now the problem appears when I try to get all the rows from this result set where the current date is between start_date and end_date or equal to either one of them.

Here is what I have been trying to get work:

SELECT MIN(t.start_date) as s_date, MAX(t.end_date) as e_date, t.obj, o.target FROM sss.times t
JOIN sss.obj o ON o.obj = t.obj
JOIN sss.target tar ON tar.target = o.target
WHERE t.obj IN (SELECT obj FROM sss.obj WHERE obj_type IN (1,2,10)) 
AND DATE '2016-04-13' >= s_date AND DATE '2016-04-13' <= e_date
GROUP BY t.obj, o.target ORDER BY s_date, t.obj

Which gives me error:
ORA-00904: e_date “%s: invalid identifier”

Any ideas how to achieve what I am trying to do in Oracle SQL?

BR,
TeNDoLLA

[swampBoogie]

Restrictions on aggregated expressions should be in a having clause.

select min(t.start_date) as s_date,
       max(t.end_date) as e_date,
       t.obj,
       o.target
  from sss.times t
  join sss.obj o
    on o.obj = t.obj
  join sss.target tar
    on tar.target = o.target
 where t.obj in
      (select obj
         from sss.obj
        where obj_type in (1,2,10)) 
 group
    by t.obj,
       o.target
having date '2016-04-13' between min(t.start_date) and max(t.end_date)
 order
    by s_date,
       t.obj

In general, correlation names can not be used except in the order by clause.

[TeNDoLLA]

This seems to do the trick, thanks a lot!

BR,
TeNDoLLA