MVSFORUMS.com

sri50131 · Beginner Joined: 07 Oct 2004 Posts: 38 Topics: 15

Hello,

I have an SQL that looks into 2 tables A and B. Based on the condition, the SQL will insert rows of A into B.

When I execute the SQL of my cursor in SPUFI, I get 47 rows that will be inserted into B, which is correct.

When I run my program with the same SQL declared in a cursor, it is looping.

The following is the code that I have in my program.

I have delcared CURSOR1 with "WITH HOLD FOR" option
EXEC SQL
OPEN CURSOR1
END-EXEC.

IF SQLCODE = 0
MOVE 'YES' TO WS-ROWS
PERFORM C210-PROCESS-CURSOR1-DATA THRU
C210-PROCESS-CURSOR1-DATA-EXIT
UNTIL WS-ROWS = 'NO'

In C210-PROCESS-CURSOR1-DATA para, I do the following:

EVALUATE SQLCODE
WHEN 0
PERFORM D210-ADD-DWH-ROWS THRU
D210-ADD-DWH-ROWS-EXIT
WHEN +100
MOVE 'NO' TO WS-ROWS
PERFORM Z005-CLOSE-CURSOR1 THRU
Z005-CLOSE-CURSOR1-EXIT

What is wrong with this code?

I ran the program in Xpeditor and the FETCH CURSOR is going in a loop?

Thanks.

kolusu · Posted: Fri Oct 15, 2004 4:56 pm Post subject:

sri50131,

Where is the fetch after the first row fetch? is it in the D210-ADD-DWH-ROWS paragraph?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

sri50131 · Beginner Joined: 07 Oct 2004 Posts: 38 Topics: 15

Kolusu,

Sorry for not mentioninn it, I do the fetch in C210-PROCESS-CURSOR1-DATA.

The process is as follows:

In C210-PROCESS-CURSOR1-DATA para, I do the following:
I fetch the cusor, then do the following:

EVALUATE SQLCODE
WHEN 0
PERFORM D210-ADD-DWH-ROWS THRU
D210-ADD-DWH-ROWS-EXIT
WHEN +100
MOVE 'NO' TO WS-ROWS
PERFORM Z005-CLOSE-CURSOR1 THRU
Z005-CLOSE-CURSOR1-EXIT

Thanks.

kolusu · Posted: Fri Oct 15, 2004 5:13 pm Post subject:

sri50131,

This is how a sample to load another table fetching from another table.

sri50131 · Beginner Joined: 07 Oct 2004 Posts: 38 Topics: 15

Kolusu,

I changed my code as you suggested. It is still going in a loop.

The Following is my code:

PERFORM B210-DECLARE-CURSOR1
THRU B210-DECLARE-CURSOR1-EXIT.

PERFORM B211-FETCH-CURSOR1
THRU B211-FETCH-CURSOR1-EXIT.

PERFORM B212-LOAD-DWH-TABLE
THRU B212-LOAD-DWH-TABLE-EXIT UNTIL WS-ROWS = 'NO'.

However, it is still going in a loop. I just came to know that the target table (DWH tabe) is defined with no indexes and no keys. Does this have any thing to do with the looping of the code? If not declaring keys is an issue, I wonder how am I able to run this SQL in SPUFI with not problems (the SQL when run in SPUFI gives me 47 rows).

Thanks.