Use of prompted values in sql-cursor code

Fri, 4 Oct 2002 11:12:34 +0200

Chuck,

> (e.g. '01-JAN-1999') would require a tedious series of Define statements
> because dates are always saved without formatting in Subfiles. Also the

> use mgtest
> define d_date date format ddmmmyyyy sep "-" = sysdate
> rep summ d_date
> set subfile name mgtest keep
> go

Records selected: 0
Records written:  1

> acc *mgtest
> rep all
> go
04.10.2002                                             ALBA systems S.à.r.l.
PAGE   1

     D_DATE

  04-OCT-2002

?

Records selected: 1
Lines printed:    3
Pages printed:    1

> sho item
                                             INPUT OUTPUT
MGTEST                                  TYPE SCALE SCALE  DEC PICTURE
  D_DATE                                DATE                  DD-MMM-YYYY

> e

Regards, Markus

----- Original Message -----
From: "Reinke, Chuck" <chuck.reinke@cognos.com>
To: "'Leslie Tsukamoto'" <leslie.tsukamoto@cae.com>; <powerh-l@lists.swau.edu>
Sent: Wednesday, October 02, 2002 2:21 AM
Subject: RE: Use of prompted values in sql-cursor code

> Here's part of an article I wrote for Support Link, that should be coming
> out in a few weeks. Maybe it will help:
>
>
> How do you deal with the following code in Quiz and move the Select to SQL?
>
> Access EMPLOYEES
> Define BEGIN Date = Parm Prompt "Enter Starting Date "
> Define END   Date = Parm Prompt "Ending Date "
> Select if START_DATE Ge BEGIN &
>    And    START_DATE Le END
> Report All
> Go
>
> An old trick is to get Quiz to write a Cursor at run time whenever the code
> is executed, which is done by creating a two-pass Quiz report. The first
> pass creates the Cursor and inserts the results of user prompts into it. The
> Second pass references this Cursor. The following code is unfortunately a
> bit dependent upon both the Operating System and the RDB. This example works
> on an NT using Oracle:
>
>
> ;pass1
> Cancel Clear
> Set NoHead
> Define BEGIN Date Format DDMMMYYYY Sep '-' = Parm Prompt "Enter starting
> date "
> Define END   Date Format DDMMMYYYY Sep '-' = Parm Prompt "Enter ending date
> "
>
> Define CR Integer*2 = 10 ; Ascii Carriage Return
> Define LF Integer*2 = 13 ; Ascii Line Feed
> Define CRLF Char*4 = Characters(CR) + Characters(LF)
>
> Define MYCURSOR1 Char*60 = "Declare X Cursor For Select * From EMPLOYEES &"
> Define MYCURSOR2 Char*60 = "  Where START_DATE Between & "
> Define MYCURSOR3 Char*10 = "' And '"
>
> Report MYCURSOR1  CRLF  Skip    &
>        MYCURSOR2  CRLF  Skip    &
>        "'" BEGIN                &
>        MYCURSOR3                &
>        END "'"
>
> Set Report Device Disk Name c:\trats\cur.qzs
> Go
>
> The Cursor is written to the file cur.qzs. By using Set Report Device Disk,
> the formatting required by Oracle is easy to obtain. Another approach would
> be to write the output to a Subfile, but then the Oracle date convention
> (e.g. '01-JAN-1999') would require a tedious series of Define statements
> because dates are always saved without formatting in Subfiles. Also the
> Subfile would need to be renamed with an operating system command within the
> Quiz run.
>
> The USE file for the entire Quiz report is:
>
> Cancel Clear
> Use PASS1; Creates the Cursor X
> Cancel Clear
> Use cur; Use the Cursor X
> Set Default; Reset the Report Device
> Access X
> Report All
> Go
>
>
> Chuck
>
> -----Original Message-----
> From: Leslie Tsukamoto [mailto:leslie.tsukamoto@cae.com]
> Sent: Monday, September 30, 2002 8:59 AM
> To: 'powerh-l@lists.swau.edu'
> Subject: Use of prompted values in sql-cursor code
>
>
> Everyone - we are still running version 7.33d3 of cognos quiz on an HP9000.
> Years ago when we installed the product we were migrating from an HP3000
> environment in which we used Robelle's Suprtool product to perform high
> speed extracts of data to pre-populate empty quiz subfiles in a faster
> manner.  When we migrated to the hp9000 there was a thought that we could
> address not having to bring the suprtool logic over as Quiz now supported
> sql code in sql cursors.  We attempted to test this to determine if we
> needed to purchase Suprtool for HPUX as well and what we found at that time
> was that if we coded a data extract using the sql cursor logic and used HARD
> CODED values for the extract -then the quiz sql cursor extract response time
> was pretty much equal to the suprtool for HPUX response time in extracting
> the data and pre-populating the quiz subfile that would be used in
> subsequent passes - however, as we tried to build into the sql cursor code -
> logic that would reference prompted values that were defined in a quiz DEF
> statement - we had problems - the version of quiz we had was not allowing us
> to code in that manner - when we talked with cognos support - they noted
> that QTP for HPUX did allow that syntax and the usage of prompted values in
> sql cursors but at that time that quiz did not.
>
> We ultimately purchased Suprtool and have been using it ever since -
> however, now that we are looking to move off of the HP box and over to a SUN
> box we have found out that Suprtool is not supported and does not run on the
> SUN platform - so we are back to trying to get a high speed extract for our
> quiz solution - my question was - does anyone know if the newer version of
> Quiz support referencing prompted values in sql cursor code?
>
> Sincerely,  Leslie
>
> Leslie J. Tsukamoto
> Manager of Programming, CAE SimuFlite
> ph:  407-445-0226
> fax: 407-295-9493
> email: leslie.tsukamoto@cae.com
>
>
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> 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
> promptly by e-mail that you have done so.  Thank you.
>
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> 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.
>
>