UNIQUE or not UNIQUE

[Leonard_Berkowitz@harvardpilgrim.org]

Yes, that is the question.

HPe3000, PH 8.19C

Last week I posted a question about why Powerhouse creates a UNIQUE index for a
search item (key field) on a TurboImage detail dataset while at the same time
*W*-ing that IMAGE does not support unique keys. Someone (I've deleted the
message, sorry) responded that an/the effect is that QUICK will GENERATE code:
LOOKUP NOTON for that field.

I need to understand how PowerHouse in general treats the data when the Index is
defined as UNIQUE even though the underlying DBMS does not support this.

1. OK, so QUICK will GENERATE the LOOKUP NOTON code. What if I delete that code
from my screen source? Will the hidden procedural code still force a LOOKUP
NOTON? Yes, I do know how to see the procedural code, but I have no idea which
screens may be relying on some non-self-evident feature of QUICK.

2. QUIZ and QTP: In establishing linkages to records with this value from a
higher-level file, will QUIZ and QTP just stop retrieving records because the
Index is defined as UNIQUE? To take as an example line items of an invoice where
the invoice number is the key from the master to the detail: will QUIZ/QTP
retrieve all of the line items with the common invoice number or a record
complex with just the first line item?

3. What other "gottchas" are there? In other words what are unexpected results
where the PHD definition will override the data base structure?

This is part of an effort to remove all of the warnings that I can as part of
compiling a PHD. I discovered a few detail datasets with UNIQUE indices. I
believe that most of them were just ignorance or careless cloning on the part of
someone in hoary antiquity (well, four or five years ago), but there just might
be one that was created that way intentionally. My own practice when I am not
overyly lazy <grin> is to put a comment ad loc. whenever I do something that is
unexpected or obscure enough that I had to research the issue myself. I've
discovered that I am the most frequent beneficiary of such comments, by the way,
when I revisit the code months later -- or even after the weekend and I have to
scratch my head about what the code contains.

It seems that the only *W*s that I cannot eliminate are the ones about the item
being two small to hold all possible values. I can live with them as I can live
with "Left truncation may occur" from the COBOL compiler.

Thanks.
===================
Leonard S. Berkowitz
Perot Health Care Systems
(Harvard Pilgrim Health Care account)
voice: 617-509-1212
fax:   617-509-3737
pager: 781-226-2431