How do I create data (not a temp table) using a loop and Oracle 12c?

Question

I'm using a PaaS (Asset Works) to create a report that needs looping. I have a bus table and need to create a spreadsheet that lists the bus replacements. Some buses need to be replaced every 10 years, others every 12 years. My report needs to list all the replacements over the next 50 years.

I've found the looping structure for Counter in 1..MaxCounter loop and see how to execute a query and put the values INTO another table. The problem is this isn't my Oracle db. I can't create a table, temp or perm. So my query needs to hold the values for use in my report.

Any suggestions?

Thanks in advance! Bruce

Declare
Counter number;
MaxCounter number := 50;
StartYear number := 2021;

BEGIN

for Counter in 1..MaxCounter loop
    Select * From eq_main where year(Delivery_Date) = startyear + maxcounter;
    -- Save this rowset somewhere...

end Loop;
commit;
end;

Use a sub-query factoring WITH clause (also called a CTE) for your query. Also, do not use loops; just select all 50 years using SELECT * FROM eq_main WHERE delivery_date >= DATE '2022-01-01' AND delivery_date < DATE '2071-01-01'. — MT0
– MT0, Commented Oct 30, 2023 at 19:42
Please edit your question and explain the logic that you want to implement (not the XY-problem of how to do something using loops as you probably do not want to use loops but we cannot give you an alternative because we do not know what you are actually trying to achieve, only the solution that you think you want). — MT0
– MT0, Commented Oct 30, 2023 at 19:54
I have groups of buses that need to be replaced at different intervals. Group A buses need to be replaced every 5 years. Group B buses need to be replaced every 12 years. For every year starting in 2023, I need to know how many of each bus I need to replace and the $ amount it will require. I need this report for the next 50 years. — Bruce Como
– Bruce Como, Commented Oct 31, 2023 at 20:13
I'm using looping because records do not exist beyond 2023; I'm actually forecasting. — Bruce Como
– Bruce Como, Commented Oct 31, 2023 at 20:26
Please edit your question and provide a minimal reproducible example with the CREATE TABLE and INSERT statements for some sample data that is representative of the problem; an English (not code) explanation of the logic you are trying to implement; and the expected output for that sample data. You can use a row generator in SQL to generate rows for the next 50 years so you do not need to use PL/SQL or loops; just explain what you want to calculate and let us give you suggestions for how that should be implemented rather than trying to force a solution using loops. — MT0
– MT0, Commented Oct 31, 2023 at 21:35

d r · Accepted Answer · 2023-11-07 09:49:16Z

If I got it right you need the data for 50 years in advance about bus replacements per year and bus group - according to validity periods in years per bus group.
Some dummy sample data to work with:

WITH    --  S a m p l e   D a t a :
    tbl (ID, DELIVERY_DATE, BUS_GROUP, YEARS_VALID, AMOUNT) AS
        (   Select 1, DATE '2021-01-01', 'B', 10, 68000 From Dual Union All
            Select 2, DATE '2021-02-01', 'C', 12, 97000 From Dual Union All
            Select 3, DATE '2021-03-02', 'C', 12, 97000 From Dual Union All
            Select 4, DATE '2021-04-01', 'B', 10, 68000 From Dual Union All
            Select 5, DATE '2021-05-01', 'C', 12, 97000 From Dual Union All
            Select 6, DATE '2022-02-01', 'B', 10, 68000 From Dual Union All
            Select 7, DATE '2022-03-02', 'A',  5, 43000 From Dual Union All 
            Select 8, DATE '2022-09-02', 'A',  5, 43000 From Dual Union All
            Select 9, DATE '2023-01-02', 'B', 10, 68000 From Dual  
        ),

Create 2 CTEs - one for list of next 50 years and another ( joining your data to the first ) with the years when any particular group period requires the replacement :

  years_50 AS
    (   Select To_Number(To_Char(SYSDATE, 'yyyy')) + (LEVEL - 1) "A_YEAR"
        From Dual
        Connect By LEVEL <= 50
    ),
  replacements AS
    ( Select      y.A_YEAR,
                  t.BUS_GROUP, t.AMOUNT
      From        years_50 y
      Inner Join  tbl t ON( y.A_YEAR >= To_Number(To_Char(t.DELIVERY_DATE, 'yyyy')) + YEARS_VALID And 
                            Mod((y.A_YEAR - To_Number(To_Char(t.DELIVERY_DATE, 'yyyy'))), YEARS_VALID) = 0)
    )

Now we can group, count and calculate totals of the data from replacements cte to get how many busses per group should be replaced:

Option 1.

--  M a i n   S Q L :
SELECT    A_YEAR, BUS_GROUP, 
          Count(BUS_GROUP) "QUANTITY", 
          AMOUNT "PRICE", 
          Count(BUS_GROUP) * AMOUNT "TOTAL"
FROM      replacements
GROUP BY  A_YEAR, BUS_GROUP, AMOUNT
ORDER BY  A_YEAR, BUS_GROUP

/*  R e s u l t : 
    A_YEAR BUS_GROUP   QUANTITY      PRICE      TOTAL
---------- --------- ---------- ---------- ----------
      2027 A                  2      43000      86000
      2031 B                  2      68000     136000
      2032 A                  2      43000      86000
      2032 B                  1      68000      68000
      2033 B                  1      68000      68000
      2033 C                  3      97000     291000
      2037 A                  2      43000      86000
      2041 B                  2      68000     136000
      2042 A                  2      43000      86000
      2042 B                  1      68000      68000
      2043 B                  1      68000      68000
      2045 C                  3      97000     291000
      2047 A                  2      43000      86000
      2051 B                  2      68000     136000
      2052 A                  2      43000      86000
      2052 B                  1      68000      68000
      2053 B                  1      68000      68000
      2057 A                  2      43000      86000
      2057 C                  3      97000     291000
      2061 B                  2      68000     136000
      2062 A                  2      43000      86000
      2062 B                  1      68000      68000
      2063 B                  1      68000      68000
      2067 A                  2      43000      86000
      2069 C                  3      97000     291000
      2071 B                  2      68000     136000
      2072 A                  2      43000      86000
      2072 B                  1      68000      68000 */

If you want all years to be listed, even if there are no replacements, then just change Inner Join in replacements CTE with Left Join.

Option 2. - PIVOT

--  M a i n   S Q L :
SELECT *
FROM    ( SELECT    A_YEAR, BUS_GROUP, 
                    Count(BUS_GROUP) "QUANTITY", 
                    AMOUNT "PRICE", 
                    Count(BUS_GROUP) * AMOUNT "TOTAL"
          FROM      replacements
          GROUP BY  A_YEAR, BUS_GROUP, AMOUNT
        )
PIVOT   ( Max(QUANTITY) "QUANTITY", Max(TOTAL) "TOTAL" For BUS_GROUP IN('A' "GROUP_A", 'B' "GROUP_B", 'C' "GROUP_C") )
ORDER BY  A_YEAR

/*
    A_YEAR      PRICE GROUP_A_QUANTITY GROUP_A_TOTAL GROUP_B_QUANTITY GROUP_B_TOTAL GROUP_C_QUANTITY GROUP_C_TOTAL
---------- ---------- ---------------- ------------- ---------------- ------------- ---------------- -------------
      2027      43000                2         86000                                                              
      2031      68000                                               2        136000                               
      2032      43000                2         86000                                                              
      2032      68000                                               1         68000                               
      2033      68000                                               1         68000                               
      2033      97000                                                                              3        291000
      2037      43000                2         86000                                                              
      2041      68000                                               2        136000                               
      2042      43000                2         86000                                                              
      2042      68000                                               1         68000                               
      2043      68000                                               1         68000                               
      2045      97000                                                                              3        291000
      2047      43000                2         86000                                                              
      2051      68000                                               2        136000                               
      2052      43000                2         86000                                                              
      2052      68000                                               1         68000                               
      2053      68000                                               1         68000                               
      2057      43000                2         86000                                                              
      2057      97000                                                                              3        291000
      2061      68000                                               2        136000                               
      2062      43000                2         86000                                                              
      2062      68000                                               1         68000                               
      2063      68000                                               1         68000                               
      2067      43000                2         86000                                                              
      2069      97000                                                                              3        291000
      2071      68000                                               2        136000                               
      2072      43000                2         86000                                                              
      2072      68000                                               1         68000                               */

Collectives™ on Stack Overflow

How do I create data (not a temp table) using a loop and Oracle 12c?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related