Oracle Select via loop in Stored Procedure

41 views Asked by At

I'm trying to select data from a table based on a array of filters calculated from another query. I have tried to describe what I'm trying to accomplish in pseudo code below.

SELECT Equipment, MIN(TIME) as FractionStart, MAX(TIME) as FractionEnd
INTO FRACTIONS
FROM DATA
WHERE ID = 1
GROUP BY (Equipment)

/* Pseudo code */
FOR EACH ROW IN FRACTIONS
  INSERT INTO MYTABLE (SELECT * FROM EVENTTABLE WHERE EVTTIME BETWEEN ROW.FractionStart AND ROW.FractionEnd AND EVTAREA = ROW.Equipment);
FOR NEXT;

RETURN MYTABLE;

I have been looking at cursors, but I haven't figured out how I can add rows to them in a loop. Am I looking at the right functions? or is there a better way to solve this?

1

There are 1 answers

2
access_granted On BEST ANSWER

Can you try this?

INSERT INTO MYTABLE 
(
SELECT * FROM EVENTTABLE te,  
(
SELECT Equipment, MIN(TIME) as FractionStart, MAX(TIME) as FractionEnd
FROM DATA
WHERE ID = 1
GROUP BY (Equipment)
) td
WHERE te.EVTTIME BETWEEN td.FractionStart AND td.FractionEnd AND te.EVTAREA = td.Equipment
);