i would like to loop over a table column of type date that returns the date which must be equal or greater than todays date. For example if there are 6 matches i would like to concatenate these 6 dates as follows
20220812-20220814-20220815-20220818-20220906-20220916
dates separated with a dash sign (-)
how would the loop look like?
thanks!
As OldProgrammer commented, you don't need a loop. If you're on 11g or higher (I hope you are; 11g is old, lower versions are ancient), use listagg function.
For example:
SQL> select hiredate from emp order by hiredate;
HIREDATE
----------
<snip>
03.12.1981
23.01.1982 --> I want dates after 01.01.1982
09.12.1982
12.01.1983
14 rows selected.
SQL> select listagg(to_char(hiredate, 'yyyymmdd'), '-') within group (order by hiredate) result
2 from emp
3 where hiredate >= date '1982-01-01';
RESULT
--------------------------------------------------------------------------------
19820123-19821209-19830112
SQL>
In your case, you'd modify condition to where hiredate >= trunc(sysdate).
If it must be a loop, then
SQL> set serveroutput on
SQL> declare
2 l_result varchar2(50);
3 begin
4 for cur_r in (select to_char(hiredate, 'yyyymmdd') hiredate
5 from emp
6 where hiredate >= date '1982-01-01'
7 order by hiredate
8 )
9 loop
10 l_result := l_result ||'-'|| cur_r.hiredate;
11 end loop;
12 l_result := ltrim(l_result, '-');
13 dbms_output.put_line(l_result);
14 end;
15 /
19820123-19821209-19830112
PL/SQL procedure successfully completed.
SQL>