Select vs Choose -Reply

[Ohmes, Matt]

Lindley wrote:
>>>
Is select <filename> any different from select if .... in this regard? I was
always led to believe it was more efficient to have selects by file where
applicable rather than one large 'select if' which included all of the
conditions.

Lindley Hill
NSW Dept Of Health
Sydney  Aust
<<<

Oh darn!  Bob beat me to the punch!  Well, that happens a bunch. ;-)

The way I like to explain it is very similar to Bob's.  I just put a
different spin on it.

The SELECT <filename> IF will test the record, after it is read, but BEFORE
it is placed in the record complex. If the records fails the SELECT
<filename> IF, it is rejected right away and never makes it into the record
complex.

The SELECT IF tests AFTER a record has been written to the record complex.

Let's take a (slightly) modified version of Author Kogan's example. We want
to find all employees who DON'T know PowerHouse.  This includes all
employees who have never been trained in anything, AND employees who have
been trained, but in something other than PH.

access EMPLOYEES link to SKILLS optional
SELECT SKILLS IF SKILL_CODE = "PH"
SELECT IF SKILL OF SKILLS <> "PH"

I'm deliberately NOT using "Not Record Skills Exists" because I think these
Selects make the difference more obvious.

When a Skill record is read, one of three possibilities exist.

1. A Skill record was found, but it was NOT PH.  The SELECT SKILLS IF
statement checks and rejects the record, before it ever gets to the record
complex.

2. A Skill record was found and it IS PH! The SELECT SKILLS IF is tested and
it passes! It is then moved to the record complex.  (We'll tell what happens
to it there, in just a second!)

3. No Skill record was found (no skills or ran out).  If no Skill records
ever made it into the record complex (i.e. none passed the SELECT SKILLS
IF), then Quiz/QTP looks to see if the file was Optional (which it is) and
created one record complex with the Skill code blank.

Now, if you followed all that, what you discover is that every employee will
have only one record complex created (assuming that they have, at most, one
PH skill record).  That single record complex will either contain the Skill
"PH" or the Skill code will be blank (either no skills or only skills that
aren't PH).

NOW!  Whichever case, after the PH skill or blank skill record is moved to
the record complex, the SELECT IF will test the record complex.  

If the SELECT IF finds the skill "PH", the employee already knows PH.  We
don't want to train them again, so the record complex is rejected.

If the SELECT IF finds anything else (i.e. the blank skill), then the
employee does not have a PH skill record. We want to train them, so the
record complex is kept.

Tah Tah!  That illustrates the difference. 8-)

As to whether or not SELECT <filename> IF improves performance over SELECT
IF; that is debatable.  In theory, it could be faster since it is performed
just prior to the SELECT IF, but all this is happening in memory, which is
VERY fast, so I doubt you'll see much difference in most cases.  

SELECT <filename> IF was really invented for parallel linkage not for
performance.  If you want to select on any of the parallel files, the SELECT
IF tests too late. You may throw away one parallel record because it's
already in the record complex with a "reject" record from another parallel
file.  You need to test parallel files BEFORE they make it into the record
complex. Hence, the need for SELECT <filename> IF.

Hope that helps!
Matt 8-)

Matt.Ohmes@Cognos.Com
Cognos Corporation
909 E. Las Colinas Blvd.
Suite 1900
Irving, TX  75039
214-259-6200
"Matt doesn't really know anything.  He just likes to pontificate a lot.
We refuse to acknowledge that he works for Cognos or that we have ever
met him or anyone with whom he's ever been associated.  Don't lend him
money and don't let him talk to your sister!" ;-)

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.