CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
Victor G
Victor G

Reputation: 15

PL/SQL funtion with If

I have a production records system, where the user selects the date, time, area, line, shift, and model. Introduce the number of fabric pieces in one hour in the field pieces. After adding another record, the user enters again the number of pieces, but it must also appear in another field, Total pieces, the total of pieces, that is, the sum of the current one plus the previous record.

This is the table

Date-------Time---------Area-------Line----Shift-----Model----Pieces-------Total volume

8/8/20-------7--------------A-----------1---------1--------XC1--------2--------------------2
8/8/20-------8--------------A-----------1---------1--------XC1--------3--------------------5

8/8/20-------9--------------A-----------1---------1--------XC1--------2--------------------7

The function should not add anything at the beginning of the day, and the following records should be added as long as the date, line, area, shift and model are the same.

I had planned to use a PL/SQL function where if the time is 7 (the start) it show the same number that was entered in pieces, but if it is another time (8 for example), select the pieces data in the database of the record that has the same date, area, line, shift, and model but the time is the previous one (hour-1).

I don't know if this can be done, I would appreciate your help.

Upvotes: 0

Views: 64

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

I wouldn't store total_volume into a table; calculate it using sum function in its analytic form. This is, after all, Oracle Apex (I presume interactive or classic report).

Here's an example (sample data from lines #1 - 14; query that actually does the job begins at line #15):

SQL> with test (datum, time, model, pieces) as                       -- Expected result:
  2    (select date '2020-08-08', 7 , 'XC1', 2 from dual union all   -- 2
  3     select date '2020-08-08', 8 , 'XC1', 3 from dual union all   -- 5
  4     select date '2020-08-08', 9 , 'XC1', 2 from dual union all   -- 7
  5     --
  6     select date '2020-08-09', 7 , 'XC1', 1 from dual union all   -- 1
  7     select date '2020-08-09', 8 , 'XC1', 3 from dual union all   -- 4
  8     select date '2020-08-09', 10, 'XC1', 2 from dual union all   -- 6
  9     select date '2020-08-09', 11, 'XC1', 1 from dual union all   -- 7
 10     select date '2020-08-09', 12, 'XC1', 6 from dual union all   -- 13
 11     --
 12     select date '2020-08-09', 9 , 'XC2', 5 from dual union all   -- 5
 13     select date '2020-08-09', 10, 'XC2', 4 from dual             -- 9
 14    )
 15  select datum, time, model, pieces,
 16    sum(pieces) over (partition by datum, model order by time) total_volume
 17  from test
 18  order by datum, model, time;

DATUM            TIME MOD     PIECES TOTAL_VOLUME
---------- ---------- --- ---------- ------------
08/08/2020          7 XC1          2            2
08/08/2020          8 XC1          3            5
08/08/2020          9 XC1          2            7
09/08/2020          7 XC1          1            1
09/08/2020          8 XC1          3            4
09/08/2020         10 XC1          2            6
09/08/2020         11 XC1          1            7
09/08/2020         12 XC1          6           13
09/08/2020          9 XC2          5            5
09/08/2020         10 XC2          4            9

10 rows selected.

SQL>

Upvotes: 2

Related Questions