<html><body>
<p><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><br>
<br>
<font size="2" face="sans-serif">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><br>
<br>
<font size="2" face="sans-serif">Select If and Select <file> If are similar but slightly different commands. </font><br>
<font size="2" face="sans-serif">* You can use Select <file> If when all the selection criteria are related to items in that single file. </font><br>
<font size="2" face="sans-serif">* You have to use Select If when you compare items in separate files. </font><br>
<font size="2" face="sans-serif">Also, you can have one Select <file> If statement for EACH file in your Access statement but ONLY ONE Select If statement. </font><br>
<br>
<font size="2" face="sans-serif">Access Employees link to Dependants</font><br>
<font size="2" face="sans-serif">Select Employees If Employees.Birth_Date > '1950-01-01' </font><br>
<font size="2" face="sans-serif">Select If Employees.Hire_Date > Dependants.Birth_Date</font><br>
<br>
<font size="2" face="sans-serif">This is essentially the same as:</font><br>
<font size="2" face="sans-serif">Access Employees link to Dependants</font><br>
<font size="2" face="sans-serif">Select If Employees.Birth_Date > '1950-01-01' AND Employees.Hire_Date > Dependants.Birth_Date</font><br>
<br>
<font size="2" face="sans-serif">Mostly, I used both versions; to make it easier to read and understand. </font><br>
<br>
<font size="2" face="sans-serif">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><br>
<br>
<font size="2" face="sans-serif">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><br>
<br>
<font size="2" face="sans-serif">Access Employees link to Dependants AND to Payroll</font><br>
<br>
<font size="2" face="sans-serif">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.</font><br>
<font size="2" face="sans-serif">Employee Dependant Payroll</font><br>
<font size="2" face="sans-serif"> 1 Spouse 2013-01-01</font><br>
<font size="2" face="sans-serif"> Child 2013-01-15</font><br>
<font size="2" face="sans-serif"> 2013-02-01</font><br>
<br>
<font size="2" face="sans-serif">If you use "Access Employees link to Dependants LINK to Payroll", you would get 6 record complexes (1:n:n)</font><br>
<font size="2" face="sans-serif">1 Spouse 2013-01-01</font><br>
<font size="2" face="sans-serif">1 Spouse 2013-01-15</font><br>
<font size="2" face="sans-serif">1 Spouse 2013-02-01</font><br>
<font size="2" face="sans-serif">1 Child 2013-01-01</font><br>
<font size="2" face="sans-serif">1 Child 2013-01-15</font><br>
<font size="2" face="sans-serif">1 Child 2013-02-01</font><br>
<font size="2" face="sans-serif">This is probably not what you wanted.</font><br>
<br>
<font size="2" face="sans-serif"> If you use "Access Employees link to Dependants AND to Payroll" you only get 3; Dependants and Payroll are read in parallel.</font><br>
<font size="2" face="sans-serif">1 Spouse 2013-01-01</font><br>
<font size="2" face="sans-serif">1 Child 2013-01-15</font><br>
<font size="2" face="sans-serif">1 <blank> 2013-02-01</font><br>
<font size="2" face="sans-serif">This is probably closer to what you want.</font><br>
<br>
<font size="2" face="sans-serif">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><br>
<br>
<font size="2" face="sans-serif">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 </font><br>
<font size="2" face="sans-serif">1 Child 2013-01-15</font><br>
<font size="2" face="sans-serif">1 <blank> 2013-02-01</font><br>
<font size="2" face="sans-serif">You just lost one of the dependants.</font><br>
<br>
<font size="2" face="sans-serif">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.</font><br>
<font size="2" face="sans-serif">1 Spouse 2013-01-15</font><br>
<font size="2" face="sans-serif">1 Child 2013-02-01</font><br>
<br>
<font size="2" face="sans-serif">And that is WHY there is a Select <file> If statement in the first place. :-)</font><br>
<br>
<font size="2" face="sans-serif">Anyway, hardly anyone uses Parallel Access statements, so it's probably not relevant. </font><br>
<br>
<font size="2" face="sans-serif">Just remembering the 'good old days'.</font><br>
<font size="2" face="sans-serif">Matt :-)</font><br>
<br>
<img width="16" height="16" src="cid:1__=09BBF162DFCF302D8f9e8a93df938@us.ibm.com" border="0" 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><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 08:53 AM</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">(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"> <br>
</font><font size="2" face="sans-serif"><br>
========================================================================</font><font size="3" face="serif"> <br>
</font><font size="2" face="sans-serif"><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"> <br>
</font><font size="2" face="sans-serif"><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"> <br>
</font><font size="2" face="sans-serif"><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. </font><font size="3" face="serif"><br>
</font><font size="2" face="sans-serif"><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... </font><font size="3" face="serif"><br>
</font><font size="2" face="sans-serif"><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"> <br>
</font><font size="2" face="sans-serif"><br>
===========================================================================</font><font size="3" face="serif"> <br>
<br>
<br>
Thanks! <br>
<br>
A+ <br>
<br>
Etienne <br>
</font><font size="2" face="sans-serif"><br>
============================================================================</font><font size="3" face="serif"> <br>
</font><font size="2" face="sans-serif"><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<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>