How smart is the 'select' statement in QTP ?

Fri, 7 Sep 2001 06:32:17 -0400

Hi Richard,

this might be a case where you would wish to use a quick ghost screen to
generate a sequential list of unique record ID's which you would
subsequently read from within qtp and link out to the true data records of
interest.

The reason for this suggestion is that the qdesign 'while retrieving'
construct allows the use of the 'break' command which interrupts a given
retrieving cycle, you would make the break verb conditional on the date
range condition being satisfied.

The ghost screen would be called first followed by the qtp which would then
access the file written by the ghost screen.

psuedocode'ish example below,

screen ghost 
file  seqfile designer  open read
file  idxfile designer open read
file writekeys designer 

temp  t-flag-found reset at startup

; this proc is called from init' proc below
procedure internal getbyk
begin

let t-flag-found = 0
while ret idxfile viaindex ... using keyseg of seqfile
begin

if date conditions satisfied
then 
let t-flag-found = 1
break
end

if t-flag-found = 1
then begin
let uniquekeyval of writekeys = keyval of idxfile
put writekeys reset
end
end

; start  processing
procedure initial
begin

while ret seqfile
begin
if select condition true
 then do internal getbyk
end

; exit ghost screen 
return
end

Not being a AS400 whizz, I do not know if the example above is applicable to
PH 6.07 but it would certainly work on most systems.

regards,
Joe Boyle.
____________________________________________________
Software specialist.
Cognos Customer Support UK.  
Cognos Ltd.                   Phone:         +44 / (0)1344 / 707 799
Westerly Point                   Fax:         +44 / (0)1344 / 707 962
____________________________________________________
Market Street                       e-mail:   Joe.Boyle@cognos.com
Bracknell                       Homepage:   http://www.cognos.com
Cognos                   Support Page:   http://support.cognos.com

Cognos Limited. Registered in England, number 1635598.  Registered office:
Westerly Point, Market Street, Bracknell, Berkshire, RG12 1QB

This e-mail may contain confidential information and is confidential to the
user of the e-mail address to which it is addressed.  No-one else may copy
or forward all or any of this e-mail in any form.  If you receive this
e-mail in error please destroy this message and notify the sender by return
e-mail.   

Please send any e-mail response regarding this communication to
support.uk@cognos.com and it will be directed to the specialist dealing with
your call. Please make sure that you include the call reference number in
the subject line of your e-mail.

-----Original Message-----
From: Latimer, Richard [mailto:richard.latimer@airways.co.nz]
Sent: 05 September 2001 21:51
To: PowerHouse List (E-mail)
Subject: How smart is the 'select' statement in QTP ?

Listland,

We make extensive use of date ranged records in our billing application - eg
we record that a particular aeroplane was owned by customer a from 1 January
1993 to 30 July 1997 and customer b from 31 July 1997 to < some date well in
the future>.  This record is indexed on the aircraft registration
(ascending) and then the applies from date (descending). Code in the
maintenance screens ensures that there is only ever one 'active' record per
aircraft.

Lookups to get the customer for a particular flight are done by linking to
the aircraft registration and then selecting the record where the date of
flight is between the applies from and applies to date.  There was an
implicit assumption that the 'select' would not need to continue scanning
the file after the 'first' record since it's conditions would have been
satisfied . . . however I suspect this is not the case.

To date this method has worked well and run times are reasonable but we are
looking to extend the technique to another lookup - in this case the runway
and weather conditions applicable at the destination airport of a flight.

Our most traded aircraft has had 30 owners in the 10 years we keep so even
in the worst case the select only has to plough through that many records.
The new scenario has the potential to generate that many per day! !

My question is whether the select statement is smart enough to realise that
the date is indexed / sorted and that it can stop looking after it has found
the right 'handful' of records. ?

Alternatively does anyone have a better idea for handling the lookup ?

We are PowerHouse 6.07 on DB2/400

tia

Richard Latimer
Wellington MIS Manager
Airways New Zealand
Ph +64 4 471 4744
Fax +64 4 471 0395

**********************************************************************
This electronic message together with any attachments is confidential. If
you receive it in error: (i) you must not use, disclose, copy or retain
it; (ii) please contact the sender immediately by reply email and then
delete the emails. Views expressed in this email may not be those of the
Airways Corporation of New Zealand Limited
**********************************************************************

= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
This message may contain privileged and/or confidential information.  If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate, or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender by
e-mail promptly that you have done so.  Thank You.