I am very new to PL/SQL. I have data in an initial table, named 'FLEX_PANEL_INSPECTIONS' that I am attempting to summarise in a second table, named 'PANEL_STATUS_2' using a PL/SQL procedure. However, due to the nature of the data, I have had to write a case statement in order to correctly summarise the data from FLEX_PANEL_INSPECTIONS. I have therefore created a third, intermediate table to bridge the two (named 'PANEL_STATUS_1') since the case statement will not allow columns in the group by clause which specifically order the data (to the extent of my knowledge - I get an error when I try and do this). I do not want to be storing data in the intermediate table - is there any way that I can either make it temporary (i.e. exist only while the procedure runs so that data from 'PANEL_STATUS_1' is not retained); create a view within the procedure, or remove the need for the intermediate table altogether?
Any help or criticism of my mistakes / misunderstanding of PL/SQL would be greatly appreciated. Here is the code I have written:
create or replace procedure PANEL_STATUS_PROCEDURE (panel_lot_id in number) as
--Populate intermediate table with information about the status of the panels.
insert into PANEL_STATUS_1 (FLEX_LOT_ID, FLEX_PANEL_DMX, FLEX_PANEL_STATUS)
select FLEX_LOT_ID, FLEX_PANEL_DMX,
--Sum the status values of the 4 panel inspections. A panel passes if and only if this sum = 4.
case sum (FLEX_PANEL_STATUS)
when 4 then 1
end as new_panel_status
where FLEX_LOT_ID = panel_lot_id
group by FLEX_LOT_ID, FLEX_PANEL_DMX;
--Add information about the machine ID and the upload time to this table.
insert into PANEL_STATUS_2 (FLEX_LOT_ID, FLEX_PANEL_DMX, FLEX_PANEL_STATUS, MACHINE_ID, UPLOAD_TIME)
select distinct PANEL_STATUS_1.*, MACHINE_ID, UPLOAD_TIME
from PANEL_STATUS_1, FLEX_PANEL_INSPECTIONS
where (FLEX_PANEL_INSPECTIONS.FLEX_LOT_ID = PANEL_STATUS_1.FLEX_LOT_ID
and FLEX_PANEL_INSPECTIONS.FLEX_PANEL_DMX = PANEL_STATUS_1.FLEX_PANEL_DMX)
and FLEX_PANEL_INSPECTIONS.FLEX_LOT_ID = panel_lot_id;
You can create your temp table as
create global temporary table gtt_panel_status ( column datatype ... ) on commit [delete|preserve] rows;
preserve in the
on commit clause).
However you usually don't need a temp table. You might try a
with clause (CTE), or else an inline view along lines of
select x, y, z from (select your subquery here).
Edit: actually looking at your query some more, I think what you a actually need is an analytic
sum, i.e. a total without aggregating. For example, something like this:
create or replace procedure panel_status_procedure ( panel_lot_id in number ) as begin -- Add information about the machine ID and the upload time to this table. insert into panel_status_2 ( flex_lot_id , flex_panel_dmx , flex_panel_status , machine_id , upload_time ) select distinct flex_lot_id , flex_panel_dmx , case sum(flex_panel_status) over (partition by flex_lot_id, flex_panel_dmx) when 4 then 1 else 0 end , machine_id , upload_time from flex_panel_inspections pi where pi.flex_lot_id = panel_lot_id; end panel_status_procedure;