Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
How to Include Row Nbr on SQL SELECT Stmt
has no ratings.
Published: 07 Apr 2014
Revised: 16 May 2014 - 1133 days ago
Last viewed on: 22 Jun 2017 (3183 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

How to Include Row Nbr on SQL SELECT Stmt Published by: Bob Cozzi on 07 Apr 2014 view comments(9)

I often need to know which row a value occurs in a file. More often I simply want the output from SELECT to include the ROW number as occurs when you use RUNQRY instead of SQL SELECT. Turns out, you can.

Normally a quick and dirty SELECT statement (for interactive output) might look like this:

SELECT * FROM mylib/myData

SQL displays all the fields in the file and all the rows in the file, one page at a time. Of course you can add the WHERE and ORDER BY clause to the results if you wish.

While each field is nicely listed for you, one thing you don't see in the output, is the record number. Not the offical relative record number as that doesn't apply to SQL, I mean the row counter for the result set.

While SQL result sets do not strictly include a record number, they can include a row number within the result set. To make the row ID (as it's called) appear in your results, have to add two keywords and change the file name slightly, but it's really easy, here's how:

SELECT row_number() over(), C.* FROM mylib/myData C

The row_number() tells SQL to insert a relative row number into the result set. The OVER() clause is an optional feature that allows you to sort the assignment of the row numbers by something. If you specify OVER() it uses the statements ORDER BY clause or in this example, nothing. When I say "optional" I mean the values in the parens of the OVER() keyword are optional, but the keyword is require or the feature doesn't work.

Next in the above example, a comma follows the row_number() over() keywords and a correlation name is specified. You can still do the infamous "SELECT *" but to do that and include the row number you MUST assign a correlation name on the FROM clause. So instead of the "FROM lib/file" you specify "FROM lib/file C" and then use that correlation name to qualify the "*" in "SELECT *" as follows:

SELECT C.* FROM myLib/myFile C

Once you do that, simply insert "row_number() over()," just before the "C.*" and just after the "SELECT".

SELECT row_number() over(), C.* FROM mylib/myData C

Now you get the rows numbered in the output along with the data.

If you have a list of fields instead of "SELECT *", the correlation name is not required.

SELECT row_number() over(), custno, custname,addr1,city,state,county
FROM mylib/myData

No correlation name needed.

My users often want to know "how many" records they're looking at or what row contains "this information". This little feature gives them that information.

To make life easier, it would be nice if the correlation name was NOT required when "SELECT *" was used, that way I could just embed the extra "row_number() over()" keywords and get the results they want. But nothing is easy, I suppose. In normal SELECT statements, I rarely, if ever, use "SELECT *" anyway, so including a row_number() function along with the list of fields isn't going to be an issue for me. Likewise, I almost always use correlational names for easy of typing and clarity when specifying fields. It's just a habbit that makes sense to me.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 2 months 16 days 19 minutes ago

If you only use the table once in your query, you should also be able to use the table name, as in myData.* or myData.custno. You can move the ROW_NUMBER() around in the SELECT list; it doesn't have to be first. And you can give it a name, as in ROW_NUMBER() OVER() AS SeqNo.

Posted by: clbirk
Premium member *
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 2 months 14 days 20 hours 16 minutes ago

It would be nice if db2 had a function that told you the number of rows in the select.  SQLER3 tells you the number of rows that is effected by like a delete or update.

I know in php, in mysql, I can get the number of rows, but not with db2 and obviously not in rpg either. It seems to me that it could be one of those names like sqler3, etc. that would tell us how many rows in the record set was returned. Obviously if you put a fetch only 100 rows, that value would be 100 (or less in case you hit eof).

 

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 2 months 14 days 19 hours 4 minutes ago
Edited: Wed, 14 May, 2014 at 07:57:08 (1135 days ago)

I think in embedded SQL, the row count is stored in sqler1 and/or sqler2.

However, to retrieve the row count, you can also use:

EXEC SQL Prepare dynPrep ATTRIBUTES INSENSITIVE
                   FOR SELECT a,b,c,d FROM ... ;
EXEC SQL Get Diagnostics :rowCount = DB2_NUMBER_ROWS;

[EDIT] In addition to the above, I've located the setting in SQL CLI that returns the result set size (i.e., row count). It is a pointer to a variable that needs to be set prior to running the SQL stmt. It works well.

Posted by: Ringer
Premium member *
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 2 months 12 days 17 hours 30 minutes ago

Nice tip. I used this today. 

Posted by: Bret
Premium member *
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 1 months 8 days 23 hours 42 minutes ago

CLBIRK states that you cannot get the number of rows from RPG. if i remember correctly, you can use the file information data structure to get the number of records. it's a binary field starting in pos 156.

certainly not as cool as the SQL method, but it has been there for a while. just dont remember if it contains number of reocrds INCLUDING deleted records.....i mean rows.

-bret

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 1 months 8 days 22 hours 35 minutes ago
Edited: Wed, 14 May, 2014 at 11:34:46 (1135 days ago)

Sarge, his comment was in context of an SQL SELECT statement, which of course would not apply to the INFDS since there's no corresponding F spec for the file.

Posted by: Bret
Premium member *
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 1 months 8 days 21 hours 51 minutes ago

i probably misread part of the message. i know we started out in SQL comments, but you can get it with RPGxx, but i misread the first sentence where he speaks of (and sets the stage for) MySql.

sucks just as much to be Right at the Wrong time, as it does being Wrong, anytime. yell

-bret

Posted by: avrSteve
Premium member *
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 1 months 7 days 20 hours 49 minutes ago

You can also use RRN( tableName ) to get the RRN in the output.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Include Row Nbr on SQL SELECT Stmt
Posted: 3 years 1 months 7 days 53 minutes ago

Thanks Steve, that's an awesome clarification of that function. I haven't been able to interpret it (rarely have needed it) but it does work as you described. Very cool. Thanks again.