QTP in AXIANT 4GL - more examples

[Joe Boyle]

yet another variation on cursor construction, 

can cle
set default
set list sql
run cow_run

set process nolimit     
GLOBAL TEMP t_parm date PARM PROMPT "Yield/Period (YYPP)  "
GLOBAL TEMP t_parm2 char*1 PARM PROMPT "first number of cow code "

request cow_req

SQL IN mydb &
 DECLARE my_cursor CURSOR FOR & 
 SELECT  cow_d                                  &
            , cow_type                          &
            , year_yield                       &
            , source_code                       &
            , current_parm from cow_creat      &
WHERE year_yield = :t_parm                                          &
and substring (cow_d from 5 for 1) = :t_parm2                       &
 and not (                                                           &
(  substring ( cow_d from 5 for 4 )  between 'A300' and 'A308' )    &
  or   (  substring ( cow_d from 5 for 4 )  between 'A330' and 'A338' ) &
  or  substring ( cow_d from 5 for 4 )   = 'A353'                    &
  or   substring ( cow_d from 5 for 4 )  = 'A500'                   &
  or  substring ( cow_d from 5 for 4 )  = 'A510'                   &
  or  substring ( cow_d from 5 for 4 ) = 'A520'  )
                             
access my_cursor

...


Regards, Joe.

This e-mail and all information contained in it is confidential and may be
legally privileged. If you are not the intended recipient, your access to
this e-mail is unauthorized. Any use, dissemination, distribution,
publication or copying by you of this e-mail or any of the information
contained within it is prohibited and may be unlawful. Do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so. The content of this e-mail and any
attachments sent with it may have been altered without the consent or
knowledge of the author.


-----Original Message-----
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Whittall, Conrad
Sent: 08 August 2005 16:46
To: powerh-l at lists.sowder.com
Subject: RE: QTP in AXIANT 4GL

Hello Syed,

You can often improve performance of a PowerHouse application that used
to run against indexed files or non-relational databases when you
migrate them to run against a relational database by replacing some of
the data access with embedded SQL.

I'd suggest taking a look at the "PowerHouse and Relational Databases"
book as well as the "PowerHouse 4GL Migration Planning Guide for
MPE/iX". If you don't have copies of these you can download them from
the Cognos site at:

http://powerhouse.cognos.com/products/powerhouse/download.html

For your specific example I'd suggest creating a declared cursor that
uses the SQL to access, join, and select only the data you want passed
back from the database...then QTP simply has to access that declared
cursor. This offloads the majority of the data manipulation to the RDBMS
engine and will certainly improve performance, especially if
PowerHouse/Axiant is running on a different machine to the database. For
example...

SQL DECLARE c_transfers CURSOR FOR   &
   SELECT * <or simply a list of the columns you need> &
   FROM                              &
      TRANSFER-D,                    &
      COW-D,                         &
      COW-LOC-D,                     &
      UNIT-M                         &
   WHERE <you'll need to insert the SQL to join the tables together> &
     AND <then put in the SQL equivalent of your QTP SELECT statement>

ACCESS c_transfers


Best regards,
Conrad

Conrad Whittall
Senior Solutions Architect, Global Customer Services, Cognos
Incorporated
3755 Riverside Drive, Ottawa, Ontario, K1G 4K9, Canada

-----Original Message-----
From: powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com
[mailto:powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com] On
Behalf Of Syed Shahul Hameed Mustaffa
Sent: Monday 8 August 2005 09:53
To: powerh-l at lists.sowder.com
Cc: Joe Boyle
Subject: QTP in AXIANT 4GL

Dear Gurus,

Quick Intro: I'm migrating PH/MPEIX application to AXIANT-4GL/WINDOWS.

The following QTP statement,

access TRANSFER-D  in cwdb                        &
         link to COW-D   in cwdb                 &
         link to COW-LOC-D    in cwdb            &
         link UNIT-NO of COW-LOC-D to UNIT-M  in cwdb

select TRANSFER-D if                              &
         TRNFR-DATE ge TRAN-DT-FR      and       &
         TRNFR-DATE le TRAN-DT-TO      and       &
         TRNFR-TYPE eq "U"             and       &
         TRNFR-SUB-TYPE eq "U"         and       &
         TRNFR-NEW-NO ge UNIT-NO-FR    and       &
         TRNFR-NEW-NO le UNIT-NO-TO

 is retrieving all the records of all the tables linked first and then
select specific records out of the entire set. I am have a relational db
(SQL SERVER). Is there any way stop this sequential access of all
records and only select those records I need.

Thanks & Best Regards,
SYED

--
= = = = = = = = = = = = = = = = = = = = = = = = = = = = 
  
       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
promptly by e-mail that you have done so.  Thank you. 
        
 

-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.