<font size=2 face="sans-serif">Small world, when I think back on it, I
think it was you who tried to explain the difference between Choose/Select
file If /Select IF </font>
<br><font size=2 face="sans-serif">in a PH class out in "Lost Colanis"
many many moons ago... </font>
<br>
<br><font size=2 face="sans-serif">Used to Work for International Paper
way back then, a few years back they "got Medieval" and sold
their Wood Products </font>
<br><font size=2 face="sans-serif">division to West Fraser in a "Castle
and attendant Serfs" type transaction. (My status in that was
"Attendant Serf"). </font>
<br>
<br><font size=2 face="sans-serif">If you remember our old sales system
MDS (Materials Distribution System), its still running. Started out
on RMS/ISAM, Vaxes, vms 5.1,</font>
<br><font size=2 face="sans-serif">nowadays we're running on Itanium machines,
vms 8.4 against oracle 11.2 (db is on a Solaris server these days).
</font>
<br><font size=2 face="sans-serif">We had to put some effort into the migration
to oracle, but everything since then has been "mostly" recompile
and go.</font>
<br><font size=2 face="sans-serif">Too bad there's not really a marketing
department that cares about things like that anymore...</font>
<br>
<br><font size=2 face="sans-serif">Good to see some old names every now
and then...</font>
<br>
<br>
<br>
<br><font size=1 color=#5f5f5f face="sans-serif">From:
</font><font size=1 face="sans-serif">Matt Ohmes <mohmes@us.ibm.com></font>
<br><font size=1 color=#5f5f5f face="sans-serif">To:
</font><font size=1 face="sans-serif"><powerh-l@lists.sowder.com>,
</font>
<br><font size=1 color=#5f5f5f face="sans-serif">Date:
</font><font size=1 face="sans-serif">09/25/2013 01:53 PM</font>
<br><font size=1 color=#5f5f5f face="sans-serif">Subject:
</font><font size=1 face="sans-serif">Re: QTP reads
every row instead of using index?</font>
<br><font size=1 color=#5f5f5f face="sans-serif">Sent by:
</font><font size=1 face="sans-serif"><powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com></font>
<br>
<hr noshade>
<br>
<br>
<br><font size=2 face="sans-serif">I couldn't resist responding to this,
even though I haven't used PowerHouse in 10 years (at least).</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
First, always use Choose if you can. That limits the number of rows
read from your source. All forms of Select only are checked after
the data has been read (although it may not have been added to the record
complex yet). Now... </font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Select If and Select <file> If are similar but slightly different
commands. <br>
* You can use Select <file> If when all the selection criteria are
related to items in that single file. <br>
* You have to use Select If when you compare items in separate files. <br>
Also, you can have one Select <file> If statement for EACH file in
your Access statement but ONLY ONE Select If statement. </font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Access Employees link to Dependants<br>
Select Employees If Employees.Birth_Date > '1950-01-01' <br>
Select If Employees.Hire_Date > Dependants.Birth_Date</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
This is essentially the same as:<br>
Access Employees link to Dependants<br>
Select If Employees.Birth_Date > '1950-01-01' AND Employees.Hire_Date
> Dependants.Birth_Date</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Mostly, I used both versions; to make it easier to read and understand.
</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
HOWEVER... Select If and Select <file> If are only identical
if your Access statement is the basic form ( "Access A link to B link
to C"). If you are constructing a "parallel" Access statement
(Access A link to B AND to C), there is a BIG difference between the two
Select forms. Essentially you can't guarantee you'll get the record
records from files B and C if you don't use Select <file> If.</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
I had some good examples in papers I wrote 20 years ago that are somewhere
on backup CDs. I'll try to recreate one:</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Access Employees link to Dependants AND to Payroll</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Dependants and Payroll records have nothing directly to do with one another;
they are only related through Employees. Say I have 2 dependants
and 3 payroll records.<br>
Employee Dependant Payroll<br>
1
Spouse 2013-01-01<br>
Child 2013-01-15<br>
2013-02-01</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
If you use "Access Employees link to Dependants LINK to Payroll",
you would get 6 record complexes (1:n:n)<br>
1 Spouse 2013-01-01<br>
1 Spouse 2013-01-15<br>
1 Spouse 2013-02-01<br>
1 Child 2013-01-01<br>
1 Child 2013-01-15<br>
1 Child 2013-02-01<br>
This is probably not what you wanted.</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
If you use "Access Employees link to Dependants AND to Payroll"
you only get 3; Dependants and Payroll are read in parallel.<br>
1 Spouse 2013-01-01<br>
1 Child 2013-01-15<br>
1 <blank> 2013-02-01<br>
This is probably closer to what you want.</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Now, what if you only wanted to see payroll records after the first of
2013 but you still wanted to see all the employee's dependants?</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
On the parallel linkage, if you used "Select If Payroll.pay_date
> '2013-01-02'" then the first record COMPLEX would be rejected
(first payroll record AND the first dependant record) and you would get
<br>
1 Child 2013-01-15<br>
1 <blank> 2013-02-01<br>
You just lost one of the dependants.</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
If you used "Select Payroll If Payroll.pay_date > '2013-01-02'"
then the payroll record would be discarded before it reached the record
complex and your first dependant would be preserved.<br>
1 Spouse 2013-01-15<br>
1 Child 2013-02-01</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
And that is WHY there is a Select <file> If statement in the first
place. :-)</font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Anyway, hardly anyone uses Parallel Access statements, so it's probably
not relevant. </font><font size=3><br>
</font><font size=2 face="sans-serif"><br>
Just remembering the 'good old days'.<br>
Matt :-)</font><font size=3><br>
<br>
</font><img src=cid:_1_093731BC093719CC006A62D886257BF1 alt="Inactive hide details for ---09/25/2013 08:53:14 AM---(Had to trim most of the history, size of this message thread of was get"><font size=2 color=#424282 face="sans-serif">---09/25/2013
08:53:14 AM---(Had to trim most of the history, size of this message thread
of was getting too big for this list.</font><font size=3><br>
</font><font size=1 color=#5f5f5f face="sans-serif"><br>
From: </font><font size=1 face="sans-serif"><Herald.Kaffka@westfraser.com></font><font size=1 color=#5f5f5f face="sans-serif"><br>
To: </font><font size=1 face="sans-serif"><powerh-l@lists.sowder.com>,
</font><font size=1 color=#5f5f5f face="sans-serif"><br>
Date: </font><font size=1 face="sans-serif">09/25/2013 08:53 AM</font><font size=1 color=#5f5f5f face="sans-serif"><br>
Subject: </font><font size=1 face="sans-serif">Re: QTP reads every row
instead of using index?</font><font size=1 color=#5f5f5f face="sans-serif"><br>
Sent by: </font><font size=1 face="sans-serif">powerh-l-bounces+mohmes=us.ibm.com@lists.sowder.com</font><font size=3><br>
</font>
<hr noshade><font size=3><br>
<br>
</font><font size=2 face="sans-serif"><br>
(Had to trim most of the history, size of this message thread of was getting
too big for this list...).</font><font size=3> </font><font size=2 face="sans-serif"><br>
<br>
========================================================================</font><font size=3>
</font><font size=2 face="sans-serif"><br>
<br>
I also seem to remember something about "select file if" conditions
short circuiting the build of a record complex,</font><font size=3> </font><font size=2 face="sans-serif"><br>
(Select File if was supposed to kick in as soon as the file entered the
record complex, and would prevent PH from</font><font size=3> </font><font size=2 face="sans-serif"><br>
growing the record complex with unneeded records, while select was
evaluated "after all records were retreived").</font><font size=3>
</font><font size=2 face="sans-serif"><br>
<br>
Don't know if this is an urban legend, an relic of an early PH version,
or if there some truth to it...</font><font size=3> </font><font size=2 face="sans-serif"><br>
<br>
To this day, (If I'm not working with a cursor), I still tend to <br>
"Choose" on the primary file, <br>
select file if as soon as possible on the other files in the access list
(esp if selecting on some field condition in just that file such as "select
inventory_balance if status of inventory_balance = "A").</font><font size=3>
</font><font size=2 face="sans-serif"><br>
and run a final select on cross table fields at the bottom. <br>
<br>
Don't know if really more efficient or not (at one time I was told it was),
but it does tend to make the logic on a large ugly join a little
clearer (at least to me).</font><font size=3> </font><font size=2 face="sans-serif"><br>
I'm not likely to change my style at this point, but I do idly wonder if
"I'm right" or if it's just a "Crusty Old Guy" thing...
<br>
<br>
(Or if the answer is like all good fairy tales and begins "once upon
a time" this was correct, but after version x it doesn't matter any
more...).</font><font size=3> </font><font size=2 face="sans-serif"><br>
<br>
===========================================================================</font><font size=3>
<br>
<br>
<br>
Thanks! <br>
<br>
A+ <br>
<br>
Etienne </font><font size=2 face="sans-serif"><br>
<br>
============================================================================</font><font size=3>
</font><font size=2 face="sans-serif"><br>
<br>
Think first, reply later. (gotta remember that rule).</font><font size=3>
</font><font size=2 face="sans-serif"><br>
<br>
There is/was an advantage to using Choose (or choose with select) on RMS/ISAM.</font><font size=3>
</font><font size=2 face="sans-serif"><br>
<br>
The choose (which can only operate on the initial file on the access statement),
can limit the size of the data set which <br>
is processed. (You have to be choosing an indexed item, only items
which match the choose criteria are driven down</font><font size=3> </font><font size=2 face="sans-serif"><br>
into the link x of table a to y of table b logic). With just a select,
all of table A will be read, POWERHOUSE will apply the</font><font size=3>
</font><font size=2 face="sans-serif"><br>
select, and then use that to drive out the the other tables in the Join.
If table A is very big, and the select is very selective,</font><font size=3>
</font><font size=2 face="sans-serif"><br>
this can be a big difference, (read a million records, throw out the ones
before yesterday, link the remaining 2000 records</font><font size=3> </font><font size=2 face="sans-serif"><br>
into a results set, as opposed to reading 2000 rows since yesterday (choose
date(days(sysdate)-1)) and building the complex...</font><font size=3>
<br>
<br>
<br>
<br>
<br>
---------------------------- <br>
This e-mail message and any attachments are confidential. Any dissemination
or use of this information by a person other than the intended recipient
is unauthorized. If you are not the intended recipient, please notify me
by return e-mail, do not open any attachment and delete this communication
and any copy. Thank you </font><tt><font size=2>--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: powerh-l@lists.sowder.com<br>
Subscribe: 'subscribe' in message body to powerh-l-request@lists.sowder.com<br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
powerh-l-request@lists.sowder.com</font></tt><tt><font size=2 color=blue><u><br>
</u></font></tt><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><font size=2 color=blue><u>http://lists.sowder.com/mailman/listinfo/powerh-l</u></font></tt></a><tt><font size=2><br>
This list is closed, thus to post to the list you must be a subscriber.<br>
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list archive at Google.--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: powerh-l@lists.sowder.com<br>
Subscribe: 'subscribe' in message body to powerh-l-request@lists.sowder.com<br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
powerh-l-request@lists.sowder.com<br>
</font></tt><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><font size=2>http://lists.sowder.com/mailman/listinfo/powerh-l</font></tt></a><tt><font size=2><br>
This list is closed, thus to post to the list you must be a subscriber.<br>
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list archive at Google.</font></tt>
<br><br />
<br />----------------------------
<br />This e-mail message and any attachments are confidential. Any dissemination or use of this information by a person other than the intended recipient is unauthorized. If you are not the intended recipient, please notify me by return e-mail, do not open any attachment and delete this communication and any copy. Thank you