How do I continually add a pl/sql return value to the last recorded value in a table?

3988598 · Jul 28, 2019 9:06AM

So I am quite new to SQL so please be gentle.

I am using Oracle Apex to generate a bar chart showing the amount of storage used in a particular database over the last 12 months. In this chart I am trying to add an additional feature where the chart will show (via a line) how much storage will likely be used on a fortnightly basis over the course of the next three months.

I have created a PL/SQL function (GET_PREDICTED_VALUE) which generates the anticipated value I require, but I am having trouble using that value to show the predicted trend. If the generated anticipated value is positive, that’s how much more storage is needed. If the anticipated value is negative, that’s how much less storage is needed (the arguments sent into this function are how many days ago do I want to draw the ‘storage used’ values. 30 will be 30 days ago, 150 is 150 days ago etc).

What I would like to do is use this anticipated value and add it to the last ‘current storage used’ value recorded in the database. I would like the anticipated value to be continually added so that a future trend can be observed.

For example, if the last ‘current storage used’ value saved in the database is 50, and the anticipated value is 3, then the value for the immediate future date should be 53. And the next future value should be 56, and the next should be 59, and so on up to three months ahead.

This is the code I have so far, and it works insofar that the chart I need does get generated and the trend line runs across the previous year and then the future three months. But for the future three months, I am only able to get the trend line to represent the anticipated value, not the value continually added E.g again, if the anticipated value is 3, my trend line shows 3 across all future dates.

WITH src AS ( SELECT GET_PREDICTED_VALUE(1, 30, 60, 90, 120, 150, 180) AS predictedValue FROM DUAL ), mydays as ( select level mylevel, sysdate + level as futureDate from dual connect by level <= 90 ), future_values as ( SELECT MAX(src.predictedValue) AS "ASM Used", TO_CHAR(TRUNC(futureDate,'IW') ,'DD/MM/YY') AS "Capture Date", TRUNC(futureDate,'IW') AS capture_date_order FROM mydays CROSS JOIN src GROUP BY TRUNC(futureDate,'IW') ORDER BY TRUNC(futureDate,'IW') ), SRC_back AS ( SELECT TRUNC(capture_date,'IW') AS week, database_name, MAX((os_usable_total_mb-os_usable_free_mb)) AS ASMUsed FROM tablespace_used WHERE capture_date >= (SYSDATE - 360) GROUP BY TRUNC(capture_date,'IW'), database_name, tablespace_name ), back_values as ( SELECT /*+ parallel(a, 8) */ TO_CHAR(week,'DD/MM/YY') AS "Capture Date", week AS capture_date_order, ROUND( MAX(ASMUsed/1024/1024 ) ) AS "ASM Used" FROM src_back a WHERE week >= (SYSDATE - 360) GROUP BY TO_CHAR(week,'DD/MM/YY'), week ORDER BY 2 ) select "Capture Date", capture_date_order, "ASM Used" from back_values union select "Capture Date", capture_date_order, "ASM Used" from future_values order by 2

I hope my explanation is clear, and if anyone could let me know what I have to do to get my trend line running the way I need?

(This is an image of how my chart looks so far):

AndrewSayer · Jul 28, 2019 2:01PM

What exactly is GET_PREDICTED_VALUE doing and why does it require so many hardcoded numeric inputs? From your description of the function it sounds like you want to pass in the date difference into the function for the parts that require predicting. You're currently cross joining your future dates to your predicted values subquery with no filters - every future date will get the same predicted value.

Maybe you wanted to do

future_values as
(
   SELECT MAX(GET_PREDICTED_VALUE(mydays.futureDate)   AS "ASM Used",
TO_CHAR(TRUNC(futureDate,'IW') ,'DD/MM/YY') AS "Capture Date",
TRUNC(futureDate,'IW')   AS capture_date_order
   FROM mydays
   GROUP BY TRUNC(futureDate,'IW')
   ORDER BY TRUNC(futureDate,'IW')
),

To get the function evaluated each time

Or maybe you wanted to do

future_values as
(
   SELECT MAX((futureDate-sysdate)/7* src.predictedValue)   AS "ASM Used",
TO_CHAR(TRUNC(futureDate,'IW') ,'DD/MM/YY') AS "Capture Date",
TRUNC(futureDate,'IW')   AS capture_date_order
   FROM mydays
   CROSS JOIN src
   GROUP BY TRUNC(futureDate,'IW')
   ORDER BY TRUNC(futureDate,'IW')
),

To get the predicted value to be multiplied by the amount of weeks between future date and sysdate

3988598 · Jul 29, 2019 7:18AM

Hi there,

The GET_PREDICTED_VALUE function calculates how much more or less storage will be required for the database in the future. The arguments it takes are the rows to be selected from a particular table (ordered by descending date), where 1 is the most current field and 180 would be the 180th entry, and the function returns an integer.

If we refer to the attached image of the chart, we can see that the last entry (represented by a bar) represents a value of about 62 units. So if my function returned 7, then the trend line should show for the first future value 69, and for the nest future value it should show 76, the next value should show 83, and so on into 3 months in the future.

And it's this last part I'm struggling with. Unfortunately, my SQL knowledge is extremely limited and so I've not found a solution to this.

Forum Stats

Quick Links

Categories

How do I continually add a pl/sql return value to the last recorded value in a table?

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

How do I continually add a pl/sql return value to the last recorded value in a table?

Answers