Oracle SQL For Loop Options

Question

I have a query where I have been using a user input (i.e. &&year) to enter years to be run in the query:

SELECT 
  t.type_code , 
  t.sub_type_code,
  COUNT(t.unique_number_id), 
  SUM(t.area_in_hectares)
FROM   
  mytable t
WHERE
       to_char(t.start_date, 'YYYY') <= '&&year' AND
       (
          to_char(t.event_date, 'YYYY') > '&&year' OR
          t.event_date IS NULL OR
          t.type_code = 'P'
       )
GROUP BY 
  t.type_code , 
  t.sub_type_code

I wanted to make this more efficient and given some of my other programming experience I had thought that, if I can loop over a range of years, I can make this query much more efficient to run. I am wondering if there is a way to iteratively inject the year from a list into the user variables instead of manually typing them in. I run the query annually and user run back almost 100 years and clearly it is not efficient to enter years manually.

I've been reading about for loops and while loops but I ma not clear if I can use those in Oracle SQL.

Thanks for any thoughts or suggestions.

If you know the range of years, then you may generate as much years as you want without a loop, but with recursive or hierarchical subquery: db<>fiddle — astentx
– astentx, Commented Jan 12, 2022 at 0:22

MT0 · Accepted Answer · 2022-01-12 00:08:13Z

Enter all the years in a single query using a sub-query factoring (WITH) clause and then JOIN it to your table. That way you do not have to have the overhead of multiple requests between the client and the database and the database does not need to parse multiple queries:

WITH years (year) AS (
  SELECT 1942 FROM DUAL UNION ALL
  SELECT 1970 FROM DUAL UNION ALL
  SELECT 2000 FROM DUAL UNION ALL
  SELECT 2022 FROM DUAL
)
SELECT y.year,
       t.type_code, 
       t.sub_type_code,
       COUNT(t.unique_number_id), 
       SUM(t.area_in_hectares)
FROM   mytable t
       INNER JOIN years y
       ON (
           t.start_date < TO_DATE( (year+1) || '-01-01', 'YYYY-MM-DD' )
       AND (
              t.event_date >= TO_DATE( (year+1) || '-01-01', 'YYYY-MM-DD' )
           OR t.event_date IS NULL
           OR t.type_code = 'P'
           )
       )
GROUP BY
       y.year,
       t.type_code,
       t.sub_type_code

You can also convert the year to a date rather than trying to convert a table column to a string. That would allow you to use an index on the date columns, if one exists.

Yusuf · Accepted Answer · 2022-01-12 01:09:57Z

1

you can do in that way:

BEGIN
FOR years IN (
             SELECT * FROM years
              ORDER BY year asc
             )
LOOP
      SELECT 
            t.type_code , 
            t.sub_type_code,
            COUNT(t.unique_number_id), 
            SUM(t.area_in_hectares)
        FROM mytable t
       WHERE to_char(t.start_date, 'YYYY') <= year.year AND
            (
             to_char(t.event_date, 'YYYY') > year.year OR
             t.event_date IS NULL OR
             t.type_code = 'P'
            )
    GROUP BY  t.type_code , t.sub_type_code
END LOOP;
END;

answered Jan 12, 2022 at 1:09

Yusuf

2121 silver badge5 bronze badges

Collectives™ on Stack Overflow

Oracle SQL For Loop Options

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related