<html><body>
<p><font size="2" face="sans-serif">I do remember; you, IP and MDM. :-) (Although the memories are getting fuzzier these days.)</font><br>
<font size="2" face="sans-serif">Good to still be around, considering the alternative. Cheers!</font><br>
<br>
<img width="16" height="16" src="cid:1__=09BBF162DFF918918f9e8a93df938@us.ibm.com" border="0" alt="Inactive hide details for ---09/25/2013 02:23:11 PM---Small world, when I think back on it, I think it was you who tried to ex"><font size="2" color="#424282" face="sans-serif">---09/25/2013 02:23:11 PM---Small world, when I think back on it, I think it was you who tried to explain the difference betwee</font><br>
<br>
<font size="1" color="#5F5F5F" face="sans-serif">From: </font><font size="1" face="sans-serif"><Herald.Kaffka@westfraser.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 02:23 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+mohmes=us.ibm.com@lists.sowder.com</font><br>
<hr width="100%" size="2" align="left" noshade style="color:#8091A5; "><br>
<br>
<br>
<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 <br>
in a PH class out in "Lost Colanis" many many moons ago... </font><font size="3" face="serif"><br>
</font><font size="2" face="sans-serif"><br>
Used to Work for International Paper way back then, a few years back they "got Medieval" and sold their Wood Products <br>
division to West Fraser in a "Castle and attendant Serfs" type transaction. (My status in that was "Attendant Serf"). </font><font size="3" face="serif"> <br>
</font><font size="2" face="sans-serif"><br>
If you remember our old sales system MDS (Materials Distribution System), its still running. Started out on RMS/ISAM, Vaxes, vms 5.1,</font><font size="3" face="serif"> </font><font size="2" face="sans-serif"><br>
nowadays we're running on Itanium machines, vms 8.4 against oracle 11.2 (db is on a Solaris server these days). <br>
We had to put some effort into the migration to oracle, but everything since then has been "mostly" recompile and go.</font><font size="3" face="serif"> </font><font size="2" face="sans-serif"><br>
Too bad there's not really a marketing department that cares about things like that anymore...</font><font size="3" face="serif"> <br>
</font><font size="2" face="sans-serif"><br>
Good to see some old names every now and then...</font><font size="3" face="serif"> <br>
<br>
<br>
</font><font size="1" color="#5F5F5F" face="sans-serif"><br>
From: </font><font size="1" face="sans-serif">Matt Ohmes <mohmes@us.ibm.com></font><font size="3" face="serif"> </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 01:53 PM</font><font size="3" face="serif"> </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="3" face="serif"> </font><font size="1" color="#5F5F5F" face="sans-serif"><br>
Sent by: </font><font size="1" face="sans-serif"><powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com></font><font size="3" face="serif"> <br>
</font><hr width="100%" size="2" align="left" noshade><font size="3" face="serif"><br>
<br>
</font><font size="2" face="sans-serif"><br>
I couldn't resist responding to this, even though I haven't used PowerHouse in 10 years (at least).<br>
<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... <br>
<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. <br>
<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<br>
<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<br>
<br>
Mostly, I used both versions; to make it easier to read and understand. <br>
<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.<br>
<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:<br>
<br>
Access Employees link to Dependants AND to Payroll<br>
<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<br>
<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.<br>
<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.<br>
<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?<br>
<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.<br>
<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<br>
<br>
And that is WHY there is a Select <file> If statement in the first place. :-)<br>
<br>
Anyway, hardly anyone uses Parallel Access statements, so it's probably not relevant. <br>
<br>
Just remembering the 'good old days'.<br>
Matt :-)</font><font size="3" face="serif"><br>
<br>
</font><img src="cid:1__=09BBF162DFF918918f9e8a93df938@us.ibm.com" width="16" height="16" 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="1" color="#5F5F5F" face="sans-serif"><br>
<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" face="serif"><br>
</font><hr width="100%" size="2" align="left" noshade><font size="3" face="serif"><br>
</font><font size="2" face="sans-serif"><br>
<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" face="serif"> </font><font size="2" face="sans-serif"><br>
<br>
========================================================================</font><font size="3" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </font><font size="2" face="sans-serif"><br>
<br>
===========================================================================</font><font size="3" face="serif"> <br>
<br>
<br>
Thanks! <br>
<br>
A+ <br>
<br>
Etienne </font><font size="2" face="sans-serif"><br>
<br>
============================================================================</font><font size="3" face="serif"> </font><font size="2" face="sans-serif"><br>
<br>
Think first, reply later. (gotta remember that rule).</font><font size="3" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> </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" face="serif"> <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><font size="3" color="#0000FF" face="serif"><u><br>
</u></font><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><font size="2" color="#0000FF"><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</font></tt><font size="3" color="#0000FF" face="serif"><u><br>
</u></font><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><font size="2" color="#0000FF"><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.</font></tt><font size="3" face="serif"> <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<br>
</font></tt><tt><font size="2"><a href="http://lists.sowder.com/mailman/listinfo/powerh-l">http://lists.sowder.com/mailman/listinfo/powerh-l</a></font></tt><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>
</body></html>