Use of prompted values in sql-cursor code
John MacLerran
macljohn@isu.edu
Thu, 03 Oct 2002 08:50:51 -0600
--------------080306060103090809030904
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Hmmm
Rats Employees...
Rat Race...
Is there a relationship here? :-)
Witkopp, Richard wrote:
>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.
>>
>>
>>
>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>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.
>
>
>
--
----------------------------------------------------------------------
John MacLerran
IT Systems Analyst email: macljohn@isu.edu
Idaho State University V(208) 282-2954
http://www.isu.edu/~macljohn F(208) 282-3673
----------------------------------------------------------------------
--------------080306060103090809030904
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
Hmmm
Rats Employees...
Rat Race...
Is there a relationship here? :-)
Witkopp, Richard wrote:
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.
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.
--
----------------------------------------------------------------------
John MacLerran
IT Systems Analyst email: macljohn@isu.edu
Idaho State University V(208) 282-2954
http://www.isu.edu/~macljohn F(208) 282-3673
----------------------------------------------------------------------
--------------080306060103090809030904--