oracle apex - Create an SQL Select which counts from Sysdate to 2012

Question

I currently try to create an SQL Query which i wanna use in an Apex select list.

I just need to Count from This Year(Sysdate, in this format --> YYYY) to 2012.

The Result should be something like this:

A1
-----
2021
2020
2019
2018
2017
2016
2015
2014
2013
2012

My Attempt:

select a1 from
(
SELECT to_char(SYSDATE,'YYYY') a1 FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-12),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-24),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-36),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-48),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-60),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-72),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-84),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-96),'YYYY') FROM dual
UNION ALL
SELECT to_char(add_months(SYSDATE,-108),'YYYY') FROM dual
) a

but this is Hardcoded and i would need to change it year for year.

or this example but this is PLSQL and i need it in SQL

Declare v_counter Number;
begin    
    a1:= to_char(Sysdate, 'YYYY');
    dbms_output.put_line(a1);
    While a1 > 2014 loop
    a1 := a1-1;
    dbms_output.put_line(a1);
    end loop;
end;

question from:https://stackoverflow.com/questions/65829649/create-an-sql-select-which-counts-from-sysdate-to-2012

Answer 1

You first you can select consecutive 10 numbers from dual by below query:

select level from dual connect by level<=10;

Then I have just subtracted the numbers from current date's year to get all the consecutive years. In where clause I have extracted the year from sysddate and subtract 2012 from it to get numbers of year to be published. Use below query to achieve what you are looking for.

SELECT TO_CHAR(SYSDATE,'YYYY')-LEVEL+1 as A1 FROM
DUAL
CONNECT BY LEVEL <= (select extract(year from sysdate)-2012 from dual)+1
order by A1