Use of prompted values in sql-cursor code

Wed, 2 Oct 2002 09:50:36 -0700

What's with this "Access EMPLOYEES" business??
Did the rats all die?

> -----Original Message-----
> From: Reinke, Chuck [mailto:chuck.reinke@cognos.com]
> Sent: Tuesday, October 01, 2002 5:21 PM
> To: 'Leslie Tsukamoto'; 'powerh-l@lists.swau.edu'
> 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.
> 