IN clause param to subquery

Following query gives me latest date/time of the particular day.

select * 
from calendar c 
where c.date= to_date('2018/03/09','yyyy/mm/dd') 
 and c.date >= all(select date from calendar c2 where c.date = c2.date)

how can I use above query as subquery and pass in clause as a param to the following query?

select * from (

/*above query inside and iterate over in param*/

)abc where abc.date in (to_date('2018/03/09','yyyy/mm/dd') ,to_date('2018/03/10','yyyy/mm/dd') )

3 answers

  • answered 2018-10-11 19:40 Abdullah Ahsan

    select * 
    from calendar c 
    where c.date in (to_date('2018/03/09','yyyy/mm/dd') ,to_date('2018/03/10','yyyy/mm/dd') )
     and c.date >= all(select date from calendar c2 where c.date = c2.date)
    

    Try this.

  • answered 2018-10-11 19:41 Giovanni Esposito

    I do not know if I understood you correctly. Is this what you want to do?

    SELECT * FROM (
        SELECT * 
        FROM calendar c 
        WHERE c.d_date= to_date('2018/03/09','yyyy/mm/dd') 
                AND c.d_date >= all(SELECT d_date FROM calendar c2 WHERE c.d_date = c2.d_date)
    )abc WHERE abc.d_date IN (to_date('2018/03/09','yyyy/mm/dd') ,to_date('2018/03/10','yyyy/mm/dd') );
    

    I changed the name of the date column to d_date, because it is a reserved word.

  • answered 2018-10-11 19:58 d-man

    sorry i meant to parameterize subquery using outer query and was able to write some thing like which serves the purpose

    select * 
    from calendar c 
    where c.date >= all(select date from calendar c2 where c.date = c2.date) 
    and c.date in (to_date('2018/03/09','yyyy/mm/dd'),to_date('2018/03/10','yyyy/mm/dd') )