Wednesday, November 15, 2017

Limit-ing the number of rows affected by SQL statement

using limit to give the number of results to return using sql

Included in the latest Technology Refresh PTFs, IBM i 7.3 TR3 and 7.2 TR7, is the Limit clause for SQL statements. At the time of my writing this post I cannot find a description of this clause within IBM's KnowledgeCenter, therefore, I have come up with my own:

The Limit clause will affect the number of results given by it. If I use Limit in a Select statement, I will only return the number of rows in the Limit clause. Limit does not replace the FETCH NEXT x ROWS, it does the same.

Below I will give examples of using Limit in Delete and Update statements, and also show how it can be used when defining a cursor in a RPG program.

If I am going to use all these types of SQL statements I am going to need a file to use them upon. I will be using my PERSON table, you can see how I defined this table, and the data within it, in the post Fetching more rows when using multiple row Fetch.

In my first example I am going to use an Update statement to change only the first five rows with an even number. To determine if the Person id column is an even number I am using the MOD function to determine if I have an even PERSON_ID. If the remainder returned by MOD is zero then the Person id is even.

SELECT * FROM PERSON
 WHERE MOD(PERSON_ID,2) = 0
 ORDER BY PERSON_ID

The result set looks like:

Person   First                      Last
id       name                       name
     2   JACK                       CROMPTON
     4   JOHNNY                     CAREY
     6   BILLY                      REDMAN
     8   ALLENBY                    CHILTON
    10   DONALD                     GIBSON
    12   WILLIAM                    MCGLEN
    14   JOHN                       ASTON JR
    16   BRIAN                      BIRCH
    18   LAURENCE                   CASSIDY
    20   JOHN                       DOWNIE
    22   STAN                       PEARSON
    24   JOHN                       WALTON
    26   ALFRED                     STEWARD
    28   JACK                       MELLOR
    30   RAYMOND                    BENNION
    32   LAL                        HILDITCH
    34   HUGH                       MCLENAHAN
    36   FRANK                      WILLIAMS
    38   JAMES                      BULLOCK
    40   SAMUEL                     HOPKINSON

If I want to change the first five rows where the PERSON_ID is even, and not all the even PERSON_ID rows, I would do:

01  UPDATE PERSON
02    SET LAST_NAME = 'Even number'
03   WHERE MOD(PERSON_ID,2) = 0
04   ORDER BY PERSON_ID
05   LIMIT 5

The Limit clause limits my update to just the first five eligible rows. To view the changes I would use the following Select statement:

SELECT * FROM PERSON ORDER BY PERSON_ID

As I selected all rows, not just the even PERSON_ID rows, I can see that only the first five even rows were changed.


Person   First                      Last
id       name                       name
     1   REG                        ALLEN
     2   JACK                       Even number
     3   ROGER                      BYRNE
     4   JOHNNY                     Even number
     5   THOMAS                     MCNULTY
     6   BILLY                      Even number
     7   JACKIE                     BLANCHFLOWER
     8   ALLENBY                    Even number
     9   HENRY                      COCKBURN
    10   DONALD                     Even number
    11   MARK                       JONES
    12   WILLIAM                    MCGLEN

I can use the Limits clause in a SQL statement within a RPG program. In this example I want to change the first two rows I changed in my last example.

01  **free
02  ctl-opt option(*srcstmt) ;

03  dcl-s NewName char(15) inz('Changed again') ;
04  dcl-s OldName like(NewName) inz('Even number') ;
05  dcl-s Number packed(2) inz(2) ;

06  exec sql SET OPTION COMMIT = *NONE ;

07  exec sql UPDATE PERSON
08              SET LAST_NAME = :NewName
09            WHERE LAST_NAME = :OldName
10            LIMIT :Number ;

11  *inlr = *on ;

Line 1: Nowadays I only write new RPG in all free RPG.

Line 2: I have used this control option as I always want the line numbers generated in the program to match my source member.

Lines 3 – 5: Definitions for the variables I will be using. I have also initialized them with values.

Line 6: I have added the SET OPTION to make sure that the program does not commit any of the changes.

Lines 7 – 10: My Update statement uses the variables, defined in lines 3 – 5, rather than hard coded values. I am changing the first two rows where the last name is "Even number".

After the program has completed when I use the SQL Select to view all of the rows I can see that only the first two rows that had the last name "Even number" have been changed.

Person   First                      Last
id       name                       name
     1   REG                        ALLEN
     2   JACK                       Changed again
     3   ROGER                      BYRNE
     4   JOHNNY                     Changed again
     5   THOMAS                     MCNULTY
     6   BILLY                      Even number
     7   JACKIE                     BLANCHFLOWER
     8   ALLENBY                    Even number
     9   HENRY                      COCKBURN
    10   DONALD                     Even number

I can also use the Limit in the definition of a cursor in an RPG program. For example:

01  **free
02  ctl-opt option(*srcstmt) ;

03  dcl-ds Data qualified dim(99) ;
04    PID packed(9) ;
05    FNAME char(25) ;
06    LNAME char(30) ;
07  end-ds ;

08  dcl-s Number packed(2) inz(2) ;
09  dcl-s RowsFetched uns(5) ;

10  exec sql DECLARE C0 CURSOR FOR
11             SELECT * FROM PERSON
12              ORDER BY PERSON_ID
13              LIMIT :Number
14              FOR READ ONLY ;

15  exec sql OPEN C0 ;

16  Number = %elem(Data) ;
17  exec sql FETCH C0 FOR :Number ROWS INTO :Data ;

18  exec sql GET DIAGNOSTICS :RowsFetched = ROW_COUNT ;

19  exec sql CLOSE C0 ;
20  *inlr = *on ;

Lines 3 – 7: As I am going to be going to be fetching more than one row I need a data structure array to match my result set.

Line 8 and 9: Variable definitions.

Lines 10 – 14: As my cursor definition contains a Limit clause it will only return the same number of results as the value of the variable Number, two.

Line 15: Open the cursor.

Line 16: I am changing the value in Number to be the same the as the number of elements in the data structure array, 99. If I had done this before the Open statement then the value for the Limit clause would be 99.

Line 17: I Fetch multiple rows into the data structure array. As Number contains the value 99 I have asked to retrieve 99 rows.

Line 18: By using the GET DIAGNOSTICS I can retrieve the number of rows Fetched.

Line 19: As I am done with the cursor I need to close it.

If I run this program in debug, and place the break point at 20, I can check the value of RowsFetched to see how many rows were Fetched.

EVAL rowsfetched
ROWSFETCHED = 2

This shows that the value in the Limit clause in the cursor overrides the FOR x ROWS in the Fetch clause.

I think the most bizarre use of the Limit clause I have found is to use it in the Delete statement. I cannot think when I would want to only delete a number of the records that match my criteria, but not all of them. Anyway if I wanted to delete the first ten rows from the table:

DELETE FROM PERSON 
 ORDER BY PERSON_ID 
 LIMIT 10

When I use the Select statement I can see that the first ten rows have gone.

SELECT * FROM PERSON ORDER BY PERSON_ID

Person   First                      Last
id       name                       name
    11   MARK                       JONES

For those of you on an earlier release, or on a current release without the latest Technology Refresh, try using FETCH FIRST x ROWS ONLY instead of the Limit clause. If I was to create cursor, from the earlier example, for an older release it would look like.

10  exec sql DECLARE C0 CURSOR FOR
11             SELECT * FROM PERSON
12              ORDER BY PERSON_ID
13              FETCH FIRST :Number ROWS ONLY
14              FOR READ ONLY ;

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.3 TR3 and 7.2 TR7.

3 comments:

  1. "I cannot think when I would want to only delete a number of the records that match my criteria, but not all of them."

    Here's a scenario. Let's say you have a file with groups of duplicate records and you want to retain only one of each group. You could have a sub-select counting the number of records in each group. Then in your DELETE statement it can test if equal to the group field(s) and do a LIMIT (MyCount - 1).

    ReplyDelete
  2. Great. I needed it and it helped. Thank you

    ReplyDelete
    Replies
    1. Muzaffar, you said you "needed it". Could you please post a use case for that. To me the LIMIT clause is as useful as an emergency parachute in a submarine.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.