Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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

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
pvsbandi
Asked:
pvsbandi
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
momi_sabagCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
pvsbandiAuthor Commented:
So, after i close the first cursor, i shall declare the second one?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
momi_sabagCommented:
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
 
pvsbandiAuthor Commented:
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
 
momi_sabagCommented:
they should not impact one another, so it does not matter which one you close first
0
 
pvsbandiAuthor Commented:
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
 
tliottaCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
pvsbandiAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
momi_sabagCommented:
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
 
pvsbandiAuthor Commented:
Thank You so much!
0
 
tliottaCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now