Hi Mike,
Two thoughts.
1) You might try setting whileopt off prior to running this and the
other routines you're running in sequence.
2) If #1 gives you no joy, create a view that replaces your two
cursors and declare one cursor against the view (or insert into a
temp table and process from there if need be).
Ben Petersen
On 12 Mar 2004 at 10:03, Ramsour Mike wrote:
> Good morning:
>
> I have an application that has two cursors defined. The first cursor is the
> primary cursor and the second cursor is based on the first cursor. I am
> trying to design the application so that as the second cursor loops through
> from 1 to 5 rows it updates appropriate columns for the first cursor table
> using a "WHERE CURRENT OF cursor" approach -- the update will happen from 1
> to 5 times based on the results of the second cursor. The problem I am
> encountering is that after the inside (second) cursor loops more than one
> time my "UPDATE table WHERE CURRENT OF cursor" throws an error message
> saying "The cursor is not positioned in a valid row". I have not fetched
> the first cursor to the next row at the point in time that this error occurs
> so I was assuming that "WHERE CURRENT OF cursor" should continue to point to
> the same row as the second cursor does its looping.
>
> Here's my code
> --
> -- This is the first or primary cursor. The important value is CSR which is
> used in
> -- the second cursor. Each row in the csr_step_rdct table has a unique CSR
> value.
> --
> DECLARE vcsr_cursor CURSOR FOR SELECT csr,oper1,mill1,gauge1,oper2,mill2, +
> gauge2,oper3,mill3,gauge3,oper4,mill4,gauge4,oper5,mill5,gauge5 +
> FROM csr_step_rdct WHERE mill1 IS NULL
> --
> -- This is the second cursor. Which is based on the CSR column from the
> -- the first cursor. It will have from 1 to 5 rows per CSR.
> --
> DECLARE vcsr_rdct_step CURSOR FOR SELECT csr,step,cst_cntr,opertype,gauge +
> FROM rdct_data WHERE csr = .vcsr ORDER BY csr,step
> --
> -- This opens the first cursor and fetches the values
> --
> OPEN vcsr_cursor
> --
> FETCH vcsr_cursor INTO vcsr INDICATOR vind, +
> voper1 INDICATOR vind, vmill1 INDICATOR vind, vgauge1 INDICATOR vind, +
> voper2 INDICATOR vind, vmill2 INDICATOR vind, vgauge2 INDICATOR vind, +
> voper3 INDICATOR vind, vmill3 INDICATOR vind, vgauge3 INDICATOR vind, +
> voper4 INDICATOR vind, vmill4 INDICATOR vind, vgauge4 INDICATOR vind, +
> voper5 INDICATOR vind, vmill5 INDICATOR vind, vgauge5 INDICATOR vind
> --
> SET VAR verrtrap = .verrcode
> --
> WHILE verrtrap = 0 THEN
> --
> SET VAR vcntr INTEGER = 1
> --
> -- This opens the second cursor for processing and fetches it into
> variables
> --
> OPEN vcsr_rdct_step
> --
> FETCH vcsr_rdct_step INTO vcs_csr, vcs_step, vcs_cst_cntr, vcs_opertype, +
> vcs_gauge
> --
> SET VAR verrtrap = .verrcode
> --
> WHILE verrtrap = 0 THEN
> --
> -- The next few lines set variables to use macro substitution for column
> names
> --
> SET VAR vmill_col TEXT = ('MILL' + (CTXT(.vcntr)))
> --
> SET VAR vgage_col TEXT = ('GAUGE' + (CTXT(.vcntr)))
> --
> SET VAR voper_col TEXT = ('OPER' + (CTXT(.vcntr)))
> --
> -- This creates an update command variable and then executes it with the
> &vcmd
> --
> SET VAR vcmd TEXT = ('UPDATE csr_step_rdct SET ' + +
> .vmill_col + ' = .vcs_cst_cntr, ' + +
> .voper_col + ' = .vcs_opertype, ' + +
> .vgage_col + ' = .vcs_gauge WHERE CURRENT OF vcsr_cursor')
> --
> &vcmd
> --
> SET VAR vcntr INTEGER = (.vcntr + 1)
> --
> FETCH vcsr_rdct_step INTO vcs_csr, vcs_step, vcs_cst_cntr, vcs_opertype,
> +
> vcs_gauge
> --
> SET VAR verrtrap = .verrcode
> --
> ENDWHILE -- it's after this loop processes more than one time that I get
> an error
> --
> CLOSE vcsr_rdct_step
> --
> CLS
> --
> FETCH vcsr_cursor INTO vcsr
> --
> SET VAR verrtrap = .verrcode
> --
> ENDWHILE
> --
> DROP CURSOR vcsr_rdct_step
> --
> DROP CURSOR vcsr_cursor
> --
> RETURN
>
> Any thoughts, insights or help will be appreciated. My ultimate goal here
> is to optimize this program. If this program is run in sequence with
> several others it takes over a half an hour to run but if I run it by itself
> it only takes less than 10 minutes. I'm trying to take a different approach
> to see if I can speed it up when it's run in sequence with other programs.
>
> Thanks
>
> Mike Ramsour
> AK Steel
>
> 740-829-4340
>
