PL-SQL block the result of the first query as a parameter for the second query-CodePudding

How can i get the result of the first query as a parameter for the second query.
 the goal is to get the smaller date and apply it to the second query.

 SELECT
      MAX(datе)
  FROM
      datе_table
  WHERE
     datе <= TO_DATE('2022-07-31', 'YYYY-MM-DD')




   select name 
   from table_name 
   where datе =  MAX(datе)

he result of the first query must be applied in the where clause of the second query

CodePudding user response：

Instead of just max(date), use the whole 1st query:

select name
from table_name
where date = (select max(date) 
              from date_table
              where date <= date '2022-07-31'
             )

(of course, column name can't be just date; that's reserved for datatype name)

If you plan to re-use maximum date in PL/SQL code, then store it into a (local) variable, e.g.

declare
  l_max_date date;
  l_fun1_retval number;
  l_fun2_retval varchar2(20);
  l_val         number;
begin
  select max(date) into l_max_date
  from date_table
  where date <= date '2022-07-31';

  l_fun1_retval := function1 (l_max_date);
  l_fun2_retval := function2 (l_max_date);

  select count(*) into l_val
  from table_3
  where date_value = l_max_date;
end;