Use of prompted values in sql-cursor code
Reinke, Chuck
chuck.reinke@cognos.com
Fri, 4 Oct 2002 12:55:57 -0400
This is a very interesting issue and you can fool yourself easily here, as I
have done many times myself.
When you create a Subfile you get two files (or on MPE a file and file
label). One has the data, the other the formatting. When you run Quiz, the
two things are merged, though this is not apparent from the resulting
report. So d_date has data and formatting in two different places. To create
the Cursor, they need to be merged.
It also bring up the additional issue that you really need to test to whole
example, that is, actually create a cursor to make sure your solution works.
Chuck
-----Original Message-----
From: Terry Curran [mailto:terrycurran@onetel.net.uk]
Sent: Friday, October 04, 2002 5:57 AM
To: powerh-l@lists.swau.edu
Subject: RE: Use of prompted values in sql-cursor code
This code while correct is not what is required., the date format in
PowerHouse is a numeric item (certain bytes represent day, month and year)
it is not a character string which is what Oracle requires.
-----Original Message-----
From: powerh-l-admin@cube.swau.edu
[mailto:powerh-l-admin@cube.swau.edu]On Behalf Of Markus Grossrieder
Sent: 04 October 2002 10:13
To: Reinke, Chuck; powerh-l@lists.swau.edu
Subject: Re: Use of prompted values in sql-cursor code
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
Q U I Z (7.10.G3)
Copyright 1998 COGNOS INC. (ALPHA)
> 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.
>
>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.
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.