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.
"I cannot think when I would want to only delete a number of the records that match my criteria, but not all of them."
ReplyDeleteHere'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).
Great. I needed it and it helped. Thank you
ReplyDeleteMuzaffar, 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.
DeleteWell I use the FETCH FIRST ;x ROWS a lot. Usually when I only need one that matches my search or when I know there might be duplicates because when they created the file they didn't use the UNIQUE for the key list in a logical file. Indexes not so much here as they came in later with SQL-DDL.
Delete(I know, this is years late but I was looking for confirmation for my statement and now I found the LIMIT keyword!