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.