Selecting RRN's from PF's with SQL?
Last Post 19 Dec 2012 05:28 PM by Ringer. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
apiman
New Member
New Member
Posts:3

--
06 Dec 2012 07:48 AM

Good day.

How do you SELECT the relative record number(s) of records from an SQL query?

I am querying a plain physical file, one with no DDS.  It is used as a log within an application and has no key fields or logical files attached.

With SQL statements, I am able to select records "SUBSTRying" the whole single records.  But I am interested in obtaining the corresponding relative record numbers of each selected row.

How do you do that?

Thanks in advance.

Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
06 Dec 2012 07:57 AM
SELECT RRN(A) FROM MYFILE A

if you want the data and the RRN:

SELECT RRN(A), A.* FROM MYFILE A

 
Ringer
Veteran Member
Veteran Member
Posts:1750
Avatar

--
06 Dec 2012 08:33 AM
Just a word of caution. If the SQL is a complicated statement and the SQL query engine decides to build a temporary file, you might be grabbing the RRN of a record in that temporary file instead of the RRN on the PF in the select statement. I once did some updates to a PF based on the RRN in the WHERE clause and the result wasn't pretty. To counter this unexpected behavior, I had to select the RRN in a WITH and then use that field in the WHERE clause.

Chris Ringer
apiman
New Member
New Member
Posts:3

--
06 Dec 2012 08:50 AM
Thanks Tommy, Chris.

The query actually is not a very simple one. I'll try and have your comments into account.

Have nice days this month.

Regards.
apiman
New Member
New Member
Posts:3

--
06 Dec 2012 10:42 AM

Hi again.

I ran my query selecting the RRN() function along with the rest of the record fields, and it worked well.

Thanks again Chris for your valued comment.  This time it did not happen, but it is surely good to keep always in mind.

Nice day out there.

Regards.

Craig R. Lockhart
Veteran Member
Veteran Member
Posts:1167
Avatar

--
06 Dec 2012 11:41 AM
It is probably a good habit to use Ringer's suggestion.  That way, you can avoid a "got'cha".  (Just my 2 cents.)
Ringer
Veteran Member
Veteran Member
Posts:1750
Avatar

--
06 Dec 2012 12:24 PM
As I recall now, my gotcha happened when I used SQL to delete the higher RRN duplicate keys in a PF (original designer did not specify a UNIQUE key). Well the WHERE clause was a bit complicated and referenced some RRNs of the PFs and the SQL query engine decided to create temp files to run the SQL. At that point, the WHERE clause RRN references were evaluated as from the temp files but my SQL was deleting that RRN from the PF. Oops. Deleted the wrong rows. Oh it worked fine in a test environment with small files. But on the production box the query engine executed the SQL statement differently. But I did make a back up of the file, so I got to retry.

Chris Ringer
Rocky Marquiss
Senior Member
Senior Member
Posts:2906

--
19 Dec 2012 04:30 PM
Posted By Ringer on 06 Dec 2012 09:33 AM
Just a word of caution. If the SQL is a complicated statement and the SQL query engine decides to build a temporary file, you might be grabbing the RRN of a record in that temporary file instead of the RRN on the PF in the select statement. I once did some updates to a PF based on the RRN in the WHERE clause and the result wasn't pretty. To counter this unexpected behavior, I had to select the RRN in a WITH and then use that field in the WHERE clause.

Chris Ringer


You could address this by stepping into it...      

with f1 as (
select rrn(a)as recno, a.fld1,a.fld2.... from file a
)
select .... from f1 ....

 

This should insert the rec # before SQL has to do any real work... :)

Ringer
Veteran Member
Veteran Member
Posts:1750
Avatar

--
19 Dec 2012 05:28 PM
Rocky,

If you re-read my response (last sentence), that's what I did.

Chris Ringer
Ringer
Veteran Member
Veteran Member
Posts:1750
Avatar

--
19 Dec 2012 05:28 PM
Rocky,

If you re-read my response (last sentence), that's what I did.

Chris Ringer
You are not authorized to post a reply.

Acceptable Use Policy