Use of prompted values in sql-cursor code

[Reinke, Chuck]

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.