Search code examples
sqloracleoracle-sqldeveloper

Union all with loop in oracle select statement

I'm working in an oracle DB that has 20 tables with the same structure but divided by years. So, it started in ft_expenses_2002 and goes until ft_expenses_2021 (the year when I'm writing this). I need to put all these tables' columns together before doing some maths and my first approach was to use UNIAN ALL statements. It worked but I'm wondering if it's possible to do something more elegant, like using a FOR LOOP. It would not only make the query far more elegant but would avoid future maintenances because every year a new table with the "_new_year" suffix will be created.

Solution

  • I've found here a really good and short way to solve my problem, it was:

    SELECT
  GROUP_CONCAT(
    CONCAT(
      'SELECT * FROM `',
      TABLE_NAME,
      '`') SEPARATOR ' UNION ALL ')
FROM
  `INFORMATION_SCHEMA`.`TABLES` 
WHERE
  `TABLE_NAME` LIKE 'ft_expenses_%'
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;