Solved

How to use 2 cursors, both not really related to each other.

Posted on 2011-09-06
15
366 Views
Last Modified: 2012-05-12
Hi,

  I have a stored procedure which uses a cursor , which is looped in turn, to insert into a table.
 
  Now, there is another cursor, which is not related to the first cursor, but will insert into the same table as the first cursor does.

  how do i  declare and use this second cursor outside of the first one? Please help.
0
Comment
Question by:pvsbandi
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36491870
i don't understand the question
just declare both cursors with different names and use them
there is no limit to use only a single cursor
0
 
LVL 45

Expert Comment

by:Kdo
ID: 36491876
Hi pvsbandi,

By "outside of the first one" do you mean that you're going to loop through these two cursors one at a time?  If so, just declare both cursors and process them one after the other.


Kent
0
 

Author Comment

by:pvsbandi
ID: 36491951
So, after i close the first cursor, i shall declare the second one?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36491971
no
declare both of them at the top of the procedure in the declare section and then just use them

the two will not interfere with one another
0
 

Author Comment

by:pvsbandi
ID: 36492088
So, if i open and fetch the first cursor and within the same cursor, i open and fetch the second one, will the first one have any impact on the second one?
 If no, then shall i close cursor 1 first and then cursor 2?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36492163
they should not impact one another, so it does not matter which one you close first
0
 

Author Comment

by:pvsbandi
ID: 36492497
and i loop the cursor results for the first and second cursors, right?
  So, shall i end the first loop and then open the second loop?

Sorry for coming to the same spot over and over again.
 each of my cursors are looped.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 27

Expert Comment

by:tliotta
ID: 36492616
Can you supply some pseudo-code that demonstrates the problem? The two loops INSERT into a common table, but that shouldn't be an issue by itself. However, the two cursors might see complications if any CONNECT statements are involved or if the INSERTs have any relationship to tables used by the cursors.

...and within the same cursor, i open and fetch the second one,...

I assume that means "within the loop for the first cursor, i open and fetch the second one". Are you expecting to OPEN/CLOSE the second cursor multiple times, i.e., each time you loop through the first cursor?

Tom
0
 
LVL 45

Expert Comment

by:Kdo
ID: 36492873
Hi pvsbandi,

We're still not clear about how you're going to use these cursors.  Part of the conversation suggests that you want to process the first cursor, then process the second one.  Other parts suggest that you want to read an item from the first cursor, then loop through the second cursor looking for key items.

The first really is nearly trivial.  If you can program a single cursor, you can program both cursors as the second one will be almost a copy/paste of the first.  Change the cursor names (and program logic) and you'll have processed both cursors.

If you want to loop through the second cursor for each row that you read in the first cursor, there are probably better ways to do this.  The only exception that I can think of is if the data items associated with both cursors are sorted by the same keys and you want to walk through the two items simultaneously.  (Still, there may be a better way.)  Looping through the second cursor for each row in the first cursor is really just an outer join to the first cursor.

  DECLARE cursor 1 AS select * from table1;
  DECLARE cursor 2 AS select * from table2;

  OPEN cursor1
  WHILE (not end-of-cursor1)
    FETCH cursor1
      OPEN cursor2
      WHILE (not end-of-cursor2)
        FETCH cursor2
      END-WHILE
      CLOSE cursor2
  END-WHILE
  CLOSE cursor1

That is identical to

  DECLARE cursor AS
  SELECT * FROM table1
  LEFT JOIN table2
     ON join-keys

Except that the explicit join will almost certainly be faster.


Kent
0
 

Author Comment

by:pvsbandi
ID: 36494793
Thank You! Here is what i'm trying to do. I don't want to involve the first cursor in the second one or viceversa.

 My code, without the second cursor and loop, looks like the one, attached.
 Now, i have to incorporate another cursor for the following SQL and loop through and fetch into the TB_Ticklers table. I want this below cursor to be in the attached SQL, but they both will be different entities. I hope i'm making sense. Please help.

DECLARE CUR1 CURSOR WITH HOLD FOR
 SELECT DISTINCT START_DT,END_DT
                     FROM TB_GUARDIAN_SUBSIDY_SUSPENSION
                   WHERE DELETE_SW = 'N'
                     AND APPROVAL_STATUS_CD = '3047'
                      AND ((START_DT IS NULL) OR (START_DT <= vd_previous_month_end_dt))
                      AND ((END_DT IS NULL) OR (END_DT > vd_previous_month_end_dt));
OPEN CUR1;
FETCH FROM CUR1 INTO SUSP_START_DT,SUSP_END_DT;
WHILE  (SQLSTATE = '00000') DO
  IF DAYS(ADT_RUN_DT) - DAYS(SUSP_END_DT) <= 60 THEN
 INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
			UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The Suspension will be off in 60 ays.','S',444,'3567',ADT_RUN_DT + 60 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
END IF;
FETCH FROM CUR1 INTO SUSP_START_DT,SUSP_END_DT;
END WHILE;
CLOSE CUR1;
END PI

Open in new window




CREATE PROCEDURE SP_TICKLERS(IN ADT_RUN_DT DATE)

P1: BEGIN
DECLARE V_STAFF_ID INTEGER DEFAULT 0;
DECLARE V_PROVIDER_ID INTEGER;
DECLARE V_RATE_START_DT DATE;
DECLARE V_RATE_END_DT DATE;
DECLARE V_SUP_ID INTEGER DEFAULT 0;
DECLARE V_ENTITY_NM VARCHAR(50) DEFAULT '';
DECLARE V_UNIT_ID INTEGER DEFAULT 0;
DECLARE SQLCODE    INTEGER     DEFAULT 0;--
DECLARE SQLSTATE   CHAR(5)     DEFAULT '00000';--

DECLARE TICK_369_DAYS CURSOR WITH HOLD FOR
    SELECT DISTINCT TGS.PROVIDER_ID,TGR.RATE_START_DT,TGR.RATE_END_DT
	        ,F_RPT_CASEWORKER(TGS.client_id,TGS.case_id,'2952',ADT_RUN_DT,ADT_RUN_DT) as worker_id  
      FROM TB_GUARDIAN_SUBSIDY TGS
	  JOIN
	       TB_GAP_RATES TGR
		ON (TGS.GUARDIAN_SUBSIDY_ID = TGR.ASSISTANCE_ID AND TGR.DELETE_SW = 'N')
	 WHERE TGS.DELETE_SW = 'N'
	   AND TGS.PROVIDER_ID IS NOT NULL
       AND TGR.APPROVAL_STATUS_CD = '3047';
	   
OPEN TICK_369_DAYS;
FETCH FROM TICK_369_DAYS INTO V_PROVIDER_ID,V_RATE_START_DT,V_RATE_END_DT,V_STAFF_ID;

WHILE (SQLSTATE = '00000') DO
        SET V_SUP_ID  = (SELECT F_SUPERVISOR(V_STAFF_ID) FROM SYSIBM.SYSDUMMY1);
		SET V_UNIT_ID = (SELECT PRIMARY_COUNTY_UNIT_ID
                           FROM TB_STAFF 
		                  WHERE STATUS_CD = '2292' 
		                    AND DELETE_SW = 'N' 
							AND STAFF_ID = V_STAFF_ID
						 );

        IF V_STAFF_ID = V_SUP_ID  OR V_SUP_ID IS NULL THEN
                SET V_SUP_ID = (SELECT ADMINISTRATOR_ID FROM TB_COUNTY_UNIT WHERE COUNTY_UNIT_ID = V_UNIT_ID FETCH FIRST 1 ROW ONLY)  ;
        END IF;

        SET V_ENTITY_NM = (SELECT F_ENAME('2956',V_STAFF_ID) FROM SYSIBM.SYSDUMMY1);
          /* Check if the rate end date is 90 days away from the run date */
		IF DAYS(V_RATE_END_DT) - DAYS(ADT_RUN_DT)  = 90 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
			UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is due; the rate will expire in 90 days.','S',444,'3567',ADT_RUN_DT + 90 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
		END IF;
		 /* Check if the rate end date is 60 days away from the run date */
		IF DAYS(V_RATE_END_DT) - DAYS(ADT_RUN_DT)  = 60 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
			UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is due; the rate will expire in 60 days.','S',444,'3567',ADT_RUN_DT + 60 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
		END IF;
		 /* Check if the rate end date is 30 days away from the run date */
        IF DAYS(V_RATE_END_DT) - DAYS(ADT_RUN_DT)  = 30 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
			UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is due; the rate will expire in 30 days.','S',444,'3567',ADT_RUN_DT + 30 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
		END IF;
		 /* Check if the rate end date is 30 days overdue as of the run date */
		IF  DAYS(ADT_RUN_DT) - DAYS(V_RATE_END_DT) = 30 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
			UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is past due; no payment was issued.Complete review or close case','S',444,'3567',ADT_RUN_DT - 30 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
		END IF;
		/* Check if the rate end date is 60 days overdue as of the run date */
		IF  DAYS(ADT_RUN_DT) - DAYS(V_RATE_END_DT) = 60 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
			UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is past due; no payment was issued.Complete review or close case','S',444,'3567',ADT_RUN_DT - 60 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
		END IF;
		/* Check if the rate end date is 90 days overdue as of the run date */
		IF  DAYS(ADT_RUN_DT) - DAYS(V_RATE_END_DT) = 90 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
			UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is past due; no payment was issued.Complete review or close case','S',444,'3567',ADT_RUN_DT - 90 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
		END IF;
        FETCH FROM TICK_369_DAYS INTO V_PROVIDER_ID,V_RATE_START_DT,V_RATE_END_DT,V_STAFF_ID;
END WHILE;

CLOSE TICK_369_DAYS;
END P1

Open in new window

0
 
LVL 45

Accepted Solution

by:
Kdo earned 250 total points
ID: 36495168
You don't need to chase a cursor around to insert those rows.  A pretty simple rewrite of the cursor logic to a single SQL statement should do the trick.

I've rewritten it below, in just 1 of several possible styles.  Note that you don't seem to be inserting a row for every row in the cursor (within the 60 day window) but otherwise don't use these dates.  It's probably a logic issue.


Kent

WITH tickler_dates (start_dt, end_dt) AS 
(
  SELECT DISTINCT START_DT,END_DT
  FROM TB_GUARDIAN_SUBSIDY_SUSPENSION
  WHERE DELETE_SW = 'N'
    AND APPROVAL_STATUS_CD = '3047'
    AND ((START_DT IS NULL) OR (START_DT <= vd_previous_month_end_dt))
    AND ((END_DT IS NULL) OR (END_DT > vd_previous_month_end_dt))
    AND (DAYS (ADT_RUN_DT) - DAYS (SUSP_END_DT) <= 60
)
INSERT INTO TB_TICKLERS (
   TICKLER_ID,
   TICKLER_TX,
   TICKLER_TYPE_SW,
   SYSTEM_TICKLER_ID,
   TICKLER_NATURE_CD,
   DUE_DT,
   REMINDER_START_DT,
   ENTITY_TYPE_CD,
   ENTITY_KEY_ID,
   ENTITY_NM,
   ASSIGNED_TO_STAFF_ID,
   CREATE_TS,
   CREATE_USER_ID,
   UPDATE_TS,
   UPDATE_USER_ID,
   DELETE_SW)
 SELECT 
   NEXTVAL FOR SQ_TICKLERS,
   'The Suspension will be off in 60 ays.',
   'S',
   444,
   '3567',
   ADT_RUN_DT + 60 DAYS,
   ADT_RUN_DT,
   '2956',
   V_STAFF_ID,
   V_ENTITY_NM,
   V_SUP_ID,
   CURRENT TIMESTAMP,
   'GAP',
   CURRENT TIMESTAMP, 
   'GAP',
   'N'
 FROM tickler_dates;

Open in new window

0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 250 total points
ID: 36495531
if you do want to use the cursor, you can try something like




CREATE PROCEDURE SP_TICKLERS(IN ADT_RUN_DT DATE)

P1: BEGIN
DECLARE V_STAFF_ID INTEGER DEFAULT 0;
DECLARE V_PROVIDER_ID INTEGER;
DECLARE V_RATE_START_DT DATE;
DECLARE V_RATE_END_DT DATE;
DECLARE V_SUP_ID INTEGER DEFAULT 0;
DECLARE V_ENTITY_NM VARCHAR(50) DEFAULT '';
DECLARE V_UNIT_ID INTEGER DEFAULT 0;
DECLARE SQLCODE    INTEGER     DEFAULT 0;--
DECLARE SQLSTATE   CHAR(5)     DEFAULT '00000';--

DECLARE CUR1 CURSOR WITH HOLD FOR
 SELECT DISTINCT START_DT,END_DT
                     FROM TB_GUARDIAN_SUBSIDY_SUSPENSION
                   WHERE DELETE_SW = 'N'
                     AND APPROVAL_STATUS_CD = '3047'
                      AND ((START_DT IS NULL) OR (START_DT <= vd_previous_month_end_dt))
                      AND ((END_DT IS NULL) OR (END_DT > vd_previous_month_end_dt));

DECLARE TICK_369_DAYS CURSOR WITH HOLD FOR
    SELECT DISTINCT TGS.PROVIDER_ID,TGR.RATE_START_DT,TGR.RATE_END_DT
              ,F_RPT_CASEWORKER(TGS.client_id,TGS.case_id,'2952',ADT_RUN_DT,ADT_RUN_DT) as worker_id  
      FROM TB_GUARDIAN_SUBSIDY TGS
        JOIN
             TB_GAP_RATES TGR
            ON (TGS.GUARDIAN_SUBSIDY_ID = TGR.ASSISTANCE_ID AND TGR.DELETE_SW = 'N')
       WHERE TGS.DELETE_SW = 'N'
         AND TGS.PROVIDER_ID IS NOT NULL
       AND TGR.APPROVAL_STATUS_CD = '3047';

OPEN CUR1;
FETCH FROM CUR1 INTO SUSP_START_DT,SUSP_END_DT;
WHILE  (SQLSTATE = '00000') DO
  IF DAYS(ADT_RUN_DT) - DAYS(SUSP_END_DT) <= 60 THEN
 INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
                  UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The Suspension will be off in 60 ays.','S',444,'3567',ADT_RUN_DT + 60 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
END IF;
FETCH FROM CUR1 INTO SUSP_START_DT,SUSP_END_DT;
END WHILE;
CLOSE CUR1;

        
OPEN TICK_369_DAYS;
FETCH FROM TICK_369_DAYS INTO V_PROVIDER_ID,V_RATE_START_DT,V_RATE_END_DT,V_STAFF_ID;

WHILE (SQLSTATE = '00000') DO
        SET V_SUP_ID  = (SELECT F_SUPERVISOR(V_STAFF_ID) FROM SYSIBM.SYSDUMMY1);
            SET V_UNIT_ID = (SELECT PRIMARY_COUNTY_UNIT_ID
                           FROM TB_STAFF
                              WHERE STATUS_CD = '2292'
                                AND DELETE_SW = 'N'
                                          AND STAFF_ID = V_STAFF_ID
                                     );

        IF V_STAFF_ID = V_SUP_ID  OR V_SUP_ID IS NULL THEN
                SET V_SUP_ID = (SELECT ADMINISTRATOR_ID FROM TB_COUNTY_UNIT WHERE COUNTY_UNIT_ID = V_UNIT_ID FETCH FIRST 1 ROW ONLY)  ;
        END IF;

        SET V_ENTITY_NM = (SELECT F_ENAME('2956',V_STAFF_ID) FROM SYSIBM.SYSDUMMY1);
          /* Check if the rate end date is 90 days away from the run date */
            IF DAYS(V_RATE_END_DT) - DAYS(ADT_RUN_DT)  = 90 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
                  UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is due; the rate will expire in 90 days.','S',444,'3567',ADT_RUN_DT + 90 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
            END IF;
             /* Check if the rate end date is 60 days away from the run date */
            IF DAYS(V_RATE_END_DT) - DAYS(ADT_RUN_DT)  = 60 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
                  UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is due; the rate will expire in 60 days.','S',444,'3567',ADT_RUN_DT + 60 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
            END IF;
             /* Check if the rate end date is 30 days away from the run date */
        IF DAYS(V_RATE_END_DT) - DAYS(ADT_RUN_DT)  = 30 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
                  UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is due; the rate will expire in 30 days.','S',444,'3567',ADT_RUN_DT + 30 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
            END IF;
             /* Check if the rate end date is 30 days overdue as of the run date */
            IF  DAYS(ADT_RUN_DT) - DAYS(V_RATE_END_DT) = 30 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
                  UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is past due; no payment was issued.Complete review or close case','S',444,'3567',ADT_RUN_DT - 30 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
            END IF;
            /* Check if the rate end date is 60 days overdue as of the run date */
            IF  DAYS(ADT_RUN_DT) - DAYS(V_RATE_END_DT) = 60 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
                  UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is past due; no payment was issued.Complete review or close case','S',444,'3567',ADT_RUN_DT - 60 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
            END IF;
            /* Check if the rate end date is 90 days overdue as of the run date */
            IF  DAYS(ADT_RUN_DT) - DAYS(V_RATE_END_DT) = 90 THEN
        INSERT INTO TB_TICKLERS(TICKLER_ID,TICKLER_TX,TICKLER_TYPE_SW,SYSTEM_TICKLER_ID,TICKLER_NATURE_CD,DUE_DT,REMINDER_START_DT,ENTITY_TYPE_CD,ENTITY_KEY_ID,ENTITY_NM,ASSIGNED_TO_STAFF_ID,CREATE_TS,CREATE_USER_ID,
                  UPDATE_TS,UPDATE_USER_ID,DELETE_SW)
        VALUES(NEXTVAL FOR SQ_TICKLERS,'The GAP Annual Review for (Child) is past due; no payment was issued.Complete review or close case','S',444,'3567',ADT_RUN_DT - 90 DAYS,ADT_RUN_DT,'2956',V_STAFF_ID,V_ENTITY_NM,V_SUP_ID,CURRENT TIMESTAMP,'GAP',
                    CURRENT TIMESTAMP, 'GAP','N');
            END IF;
        FETCH FROM TICK_369_DAYS INTO V_PROVIDER_ID,V_RATE_START_DT,V_RATE_END_DT,V_STAFF_ID;
END WHILE;

CLOSE TICK_369_DAYS;
END P1
0
 

Author Closing Comment

by:pvsbandi
ID: 36495701
Thank You so much!
0
 
LVL 27

Expert Comment

by:tliotta
ID: 36500610
You don't need to chase a cursor around to insert those rows.  A pretty simple rewrite of the cursor logic to a single SQL statement...

This is worth thinking about. I learned a rule-of-thumb quite a while ago that often comes in handy -- "If you're using a cursor, you're probably doing it wrong."

That's not absolute. It just means that people commonly use cursors when they aren't needed. Pure SQL often is all that's needed.

Cursors definitely have their place. Just don't start from an assumption that one (or two) is needed. Things can often be simplified without one.

Tom
0
 
LVL 45

Expert Comment

by:Kdo
ID: 36502407
Hi Tom,

I agree 100%.  I despise cursors.  Despise them.  Which makes working in Oracle so difficult as so much of PL/SQL and SQL*PLUS almost require that you use cursors.

Why in the world would you write a program when you can write a single statement?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now