Crystal Reports and updateable cursor

From: Mike <mcrissy_at_mediaone.net>
Date: Fri, 07 Jan 2000 01:08:15 GMT
Message-ID: <38753a8b.320306@news.pompano.net>

Any help would be greatly appreciated. I have a report that will look at the codes in one table and report the number of incidents associated with that code using a large query. I can create the cursor to return the rows from the table but need to return a variable list with each row of the returned set. I'm trying to read the query and process the variable list in one stored procedure but don't see how to update the return cursor so that the query results get inserted. If I open the return cursor as FOR UPDATE Crystal reports gives me a fetch error. Below is the package and the procedure I'm using. Any suggestions would be helpful.
Thanks,
Mike

CREATE OR REPLACE PACKAGE CRYSTAL_PACKAGE AS

   TYPE ReportRecCur IS REF CURSOR;
END;     CREATE OR REPLACE PROCEDURE CRYSTAL_MYPROC (ReportCur IN OUT CRYSTAL_PACKAGE.ReportRecCur)
IS
TYPE myRecordStruct IS RECORD(

     code TABLE_ONE.CODE%TYPE,
     decription TABLE_ONE.DESCRIPTION%TYPE,
     dollar_value TABLE_TWO.DOLLAR_VALUE%TYPE,
     time_delay TABLE_TWO.DELAY%TYPE,
     elite TABLE_THREE.ELITE%TYPE,

);
myRecord myRecordStruct;
TYPE myCursorType IS REF CURSOR RETURN myRecordStruct; myCursor myCursorType;  

CURSOR working_cursor IS
SELECT
     Fields....
FROM
     Tables.......
WHERE
     Condition...;  

IS
BEGIN
-- able to load some default values for the two fields I need to
change
OPEN ReportCurFOR SELECT
CODE, DESCRIPTION , 0,0,0
FROM TABLE_ONE;  

• Loads TABLE_ONE which has about 100 rows
• Code_One, Description_One,0,0,0
• Code_Two, Description_Two,0,0,0
• : :
• : :
• Code_OneHundred, Description_OneHundred,0,0,0
• then for the million or so records I need to test OPEN working_cursor;
• for each code check some logic LOOP FETCH working_cursor INTO myRecord; EXIT WHEN working_cursor%NOTFOUND; IF (myRecord.time_delay < 90) OR (myRecord.time_delay IS NULL) THEN DBMS_OUTPUT.PUT_LINE('IGNORE'); -- filler ELSIF myRecord.time_delay BETWEEN 90 AND 240 THEN IF NOT (myRecord.dollar_value IS NULL) THEN entries_one := entries_one + myRecord.dollar_value; END IF; ELSIF myRecord.time_delay BETWEEN 240 AND 300 THEN IF NOT (myRecord.dollar_value IS NULL) THEN entries_two := entries_two + myRecord.dollar_value; END IF; ELSE IF NOT (myRecord.dollar_value IS NULL) THEN entries_three := entries_three + myRecord.dollar_value; END IF; END IF; END LOOP;
  --
  
• need to return to the report a curso that looks like
• Code_One, Description_One,entries_one,entries_two,entries_three
• Code_Two, Description_Two,326,23,945
• : :
• : :
• Code_OneHundred, Description_OneHundred,500,333,123
• if I open the cursor FOR UPDATE I get a fetch error

IF working_cursor%ISOPEN THEN
  CLOSE working_cursor;
END IF;   IF myCursor%ISOPEN THEN
  CLOSE myCursor;
END IF;     Received on Thu Jan 06 2000 - 19:08:15 CST