Re: AW: Uniquely identifying a record in SQL without a unique key?

If your file is set to reuse deleted records, the RRN is not guaranteed to be in chronological order, so this would not work.

If the goal is to only report unique records, the OP can always add DISTINCT to the SELECT. But I don't think that addresses the question - just sayin'!!

Happiest New Year, y'all!

Vern

On 1/1/2013 5:47 PM, Pete Hall wrote:

I'm not sure if this is the situation, but I've dealt with non-unique
rows in the past, where I wanted only the oldest row, by ordering by rrn
and fetching first row only. It worked in my situation, although
depending on cardinality and dataset size, it might be better to use a cte
containing the rrn and use an order by on that dataset to retrieve the
first row for the result.

On Sun, 30 Dec 2012 09:51:30 +0100, Birgitta Hauser wrote:

I have never seen a file that is not "uniquely keyed".

Have you ever used transaction files with of multiple (a few hundreds)
columns?
Even if you think you found an unique key it may not be unique!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Matt Olson
Gesendet: Friday, 28.12 2012 20:26 An: 'Midrange Systems Technical
Discussion'
Betreff: RE: Uniquely identifying a record in SQL without a unique key?

I have never seen a file that is not "uniquely keyed". There is always
some combination of fields (composite key) that makes up a way of
uniquely identifying a record.

The only time this would matter is if you had a two identical records in
a table.

But then this begs two questions:

1. Why is there duplicate data with no difference in any of the
columns. 2. Why is there not a unique key column, or composite key on
the table.

In either of the two cases above the developer did something very bad.

-----Original Message-----
From: James H. H. Lampert [mailto:jamesl@xxxxxxxxxxxxxxxxx]
Sent: Friday, December 28, 2012 12:54 PM To: 'Midrange Systems Technical
Discussion'
Subject: Uniquely identifying a record in SQL without a unique key?

I'm beginning to study Scott Klement's JDBCR4 API, as a means of
extending our QuestView product to access external databases (and it
would also allow us to expand our very limited support of data types
that are normally SQL-only), and something suddenly struck me:

Suppose I'm looking at a record, and I decide to make a change to it.
And the file isn't uniquely keyed.

Now, with native RLA, we can easily determine the RRN of the record
we're looking at, and acquire an update lock on that record by its RRN.
After all, RRNs only change if a RGZPFM is done, and that requires an
exclusive lock on the member.

But now, suppose I have to use SQL, JDBC, and the JDBCR4 API. I'm no
expert on SQL (SQL for Dummies is still my primary reference on the
subject), but I don't recall ever reading anything anywhere about a way
to get a record's RRN, or retrieve a record by its RRN, from SQL.

I must be missing *something* here, but what? How do I guarantee that an
update will go to the right record, when I initially read it without a
lock,
and apparently can't grab a lock on it by its RRN?

--
JHHL --
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email:
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to
review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email:
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to
review the archives at http://archive.midrange.com/midrange-l.