Open cursor not giving proper results.

[former_member649377]

Hi all,

I have a program which compares quantity from 2 tables(custom and standard), and do the processing accordingly, since the records present in database is huge in numbers the program process the records in packets.

The issue:- The program gives the result in an email( from an internal table), Providing you below screen shots of the issue.

From the screenshots, we can see the record counts are decreasing after each run( I ran the program in background and foreground as well, the logic for both of them is same), I also tried closing the GUI and then running the program to see if I can recreate the issue(results from time 9:57 in the email).

I have never came across such an issue.

Below is the code for fetch logic.

Here I am opening a cursor and fetching records from the custom table in packets, then I am fetching records from a standard table using the results from custom table, my first approach was to check on the logic I have created, but since the output is coming in a random way, I think there could be an issue with open cursor, I have read the documents but was not able to understand those properly, I need help to solve this issue.

 OPEN CURSOR WITH HOLD w_cursor FOR
    SELECT "some fields"
     FROM  "y*customtable"
     WHERE "some fields"

DO.
    CLEAR "internal_table 1".
    FETCH NEXT CURSOR   w_cursor
          INTO TABLE    "internal_table 1"
          PACKAGE SIZE  p_packsz.   <------------------ parameter in selection screen for packet size

    IF NOT sy-subrc IS INITIAL.
      CLOSE CURSOR w_cursor.
      EXIT.
    ENDIF.


    IF tl_yatpt_dea_thres IS NOT INITIAL.
       SELECT "some fields"
        FROM "Database table 1" AS a INNER JOIN
        "database table 2" AS b
        ON a~pluid = b~pluid
        INTO TABLE "internal table 2"
        FOR ALL ENTRIES IN "internal table 1"
        WHERE "some fields"
        %_HINTS ORACLE '&max_in_blocking_factor 100&'.

      ""lots of processing and filtering of data for final output""
    ENDIF.

ENDDO.

[pfefferf]

What is your issue here? The the amount of data is decreasing? If yes, are you sure that the data source itself was not changed (entries removed, data records updated so that it does not match anymore your criteria, ...)?

[former_member649377]

I found the issue, it seems open cursor is not fetching records in sequence, and to answer your question No the records are not getting updated.

[matt]

May I ask you to learn the following important fact concerning all relational databases.

There is no concept of order in a relational database table.

And now go an tell it to everyone you know!

The order of records is not defined at all. You cannot rely on the order of the records being according to primary key.

The problem is that people see often that they are sorted that way - but essentially, that is just an emergent feature. It cannot be replied upon. In the past twenty years I've seen this problem again and again. Sometimes years after the program has been in production. It applies to HANA. It applies to ORACLE.

You MUST use ORDER BY if your logic depends on the order of the returned values.

[former_member649377]

Hi Matthew,

What will be the syntax of order by for open cursor fetch cursor in this case?

[matt]

 OPEN CURSOR WITH HOLD w_cursor FOR
    SELECT "some fields"
     FROM  "y*customtable"
     WHERE "some fields"
     ORDER BY "some other fields".

You can check the ABAP help you know to get the syntax!