multiple loop inside single for statement using cursor

Question

I have 2 cursors inside my procedure for which i want to use single for loop because using this i want to loop 2 variable inside single select query. The two variable has 2 different values which is used inside single select query. These 2 values are coming from KPI_DEFINITION table which gives me timestamp which i want to compare in my select query for data extraction. The first column KPI_FREQUENCY has value for example 30 and KPI_FREQ_TIME_UNIT column has value MINUTE. So if we combine these 2 column we will get 30 MINUTE and there is another combined column value which is 50 MINUTE and there might be more. So thats why i want to put this inside loop and compare in my select query with the start_date field value.But dont know how to do that. I simply use 2 cursors to take this 2 column and trying to loop it inside single for loop but dont know how to do that.There are might be another solution for this if i dont want to use two cursors but did not find a way.

create or replace PROCEDURE "EXT_TEST" AS 

LAST_WF_ID Number := 0;
LAST_UNIT NUMBER:=10;
LAST_UNIT_VAL VARCHAR2(20);

CURSOR KPI_FREQUENCY_CUR IS
Select KPI_FREQUENCY INTO LAST_UNIT from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION WHERE 
EVENT_ID = 10028;

CURSOR KPI_FREQ_TIME_UNIT_CUR IS
Select KPI_FREQ_TIME_UNIT INTO LAST_UNIT_VAL from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION WHERE 
EVENT_ID = 10028;

BEGIN

DBMS_OUTPUT.PUT_LINE('LAST_UNIT - ' || LAST_UNIT);
DBMS_OUTPUT.PUT_LINE('LAST_UNIT_VAL - ' || LAST_UNIT_VAL);

select MIN(ID) INTO LAST_WF_ID from WF_WORKFLOW@FONIC_RETAIL where start_date > sysdate - numtodsinterval(LAST_UNIT,LAST_UNIT_VAL);

DBMS_OUTPUT.PUT_LINE('LAST_WF_ID - ' || LAST_WF_ID);

END EXT_TEST;

Sample data from KPI_DEFINITION table:

KPI_DEF_ID  KPI_FREQUENCY KPI_FREQ_TIME_UNIT  EVENT_ID
1000136     30            MINUTE                10028   
1000137     50            MINUTE                10028

Why not join those tables ? It sounds like you have a 1 to 1 relationship .. with a single KPI_FREQUENCY matching up with a single KPI_FREQ_TIME_UNIT ?? Can you post some sample data you have ? and what you expect to see as output ? — Ditto
– Ditto, Commented Mar 31, 2015 at 14:07
hmm, just noticed, that "1 to 1" relationship I mentioned is same table! O.o this looks like you're going about things all wrong .. working on a rewrite .. post some sample data, please. — Ditto
– Ditto, Commented Mar 31, 2015 at 14:09
Actually these two columns are inside KPI_DEFINITION table so i think its not possible to join it right ? And i already given sample values in this 2 column which is 30 MINUTE. The KPI_FREQUENCY comtain value 30 and KPI_FREQ_TIME_UNIT contain value MINUTE. So i want to take this as combined input and used in my select query so as to compare with start_date in order to perform data extraction. So i will get for example the data for 30 MINUTE from the start_date. — Marcus
– Marcus, Commented Mar 31, 2015 at 14:13
no need to join since they are same table .. no need for 2 cursors, either ;) Post some sample data please. — Ditto
– Ditto, Commented Mar 31, 2015 at 14:14
I didn't get what sample data you need ? Can you please tell me what data you need ? — Marcus
– Marcus, Commented Mar 31, 2015 at 14:14

Ditto · Accepted Answer · 2015-03-31 14:15:56Z

1

Pending seeing what data your actually on .. I'd suggest trying something a lot simpler ... such as this:

  select r.kpi_frequency, r.kpi_freq_time_unit, min(f.id) 
    from rator_monitoring_configuration.kpi_definition   r,
         wf_workflow@fonic_retail    f
   where r.event_id = 10028
     and f.start_date > sysdate - numtodsinterval ( r.kpi_frequency, r.kpi_freq_time_unit );
   group by r.kpi_frequency, r.kpi_freq_time_unit
   order by r.kpi_frequency, r.kpi_freq_time_unit;

answered Mar 31, 2015 at 14:15

Ditto

3,3693 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marcus Over a year ago

Can you please check the sample data in question. Without loop will it work ?

Ditto Over a year ago

Can you please try the query and see how it runs with YOUR data ? And post results if error, or explain what results you expect ?

Marcus Over a year ago

Hello this query works perfectly and giving the result what i want :) thank you and now no need to write cursor or loop.

Ditto Over a year ago

Lesson learned: Never do in PL/SQL what you can do in pure SQL ;) always look for a pure SQL solution if you can. It'll be simpler, easier and perform better in the long run. Good luck!

Collectives™ on Stack Overflow

multiple loop inside single for statement using cursor

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related