Create list of Seasons and Years as Constant Values

Question

I want to generate a list of Seasons and Years, ie (FA2019, WI2019, SP2019, SU2019) for current year + 3 years ahead. This would then be fed into another query in the WHERE clause.

Right now I have 4 statements for each season that I just UNION them together. Is there a shorter way to do this?

SELECT 'FA' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

UNION

SELECT 'WI' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

UNION

SELECT 'SP' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

UNION

SELECT 'SU' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

Answer 1

You could use CROSS JOIN two CONNECT BY queries, like:

SELECT s.SS || y.YYYY AS SSYYYY
FROM 
(
    SELECT to_number(to_char(SYSDATE, 'YYYY')) + LEVEL - 1 AS YYYY 
    FROM dual 
    CONNECT BY LEVEL <= 4
) y
CROSS JOIN (
    SELECT DECODE(level, 1, 'FA', 2, 'WI', 3, 'SP', 4, 'SU') AS SS 
    FROM dual 
    CONNECT BY LEVEL <= 4
) s
ORDER BY y.YYYY, s.SS

Returns:

| SSYYYY |
| :----- |
| FA2019 |
| SP2019 |
| SU2019 |
| WI2019 |
| FA2020 |
| SP2020 |
| SU2020 |
| WI2020 |
| FA2021 |
| SP2021 |
| SU2021 |
| WI2021 |
| FA2022 |
| SP2022 |
| SU2022 |
| WI2022 |

Demo on DB Fiddle