QUIZ truncation vs varchar
fernando.olmos at hpa.com.au
fernando.olmos at hpa.com.au
Wed Sep 7 19:19:26 CDT 2005
Joe,
Thanks for the quick replies. I did not ask the list because you guys
(and Conrad) were the ones that predominantly answered my earlier
questions anyway. I will though.
But before I do, let me understand your email more.
Firstly, to answer your earlier questions ...
>Have you checked that the second cursor actually works ?
Yes. I ran it on its own and got a number of rows.
ie:
> sql in isdb declare crs_secondary_table &
> cursor for &
> select st.primary_id, &
> st.secondary_id, &
> st.gl_account_no &
> from secondary_table st
> acc crs_secondary_table
__ Sql after PowerHouse variables/expression are removed:
__ SELECT ST.PRIMARY_ID, ST.SECONDARY_ID, ST.GL_ACCOUNT_NO
__ FROM SECONDARY_TABLE ST
> rep
> set rep nolim
> go
Records selected: 117
>Also, it seems that the table space you have available might be
insufficient to receive
>the output and I would speak to your DBA to get more resources.
I am leaving the dba out of this as much as possible, but it looks like
cursors are continually becoming a problem for me, yet they will resolve
so many performance problems in my company's software. I will pass on an
earlier email from the PH list that explains what needs to be done by
the dba and get the ball rolling.
>How many rows are in the second cursor and is there a one to one match
on the link
>items you are using ?
Well there are 117 rows in the 2nd cursor. And no, there is not a one to
one match with the 1st cursor.
>This may be nothing, but I would always code as below,
>where (st.primary_id = :primary_id and &
>st.secondary_id = :secondary_id)
This is what I need you to explain.
I tried one of your previous emails (17/08/2005) where you gave to the
list an example of how to use defines with SQL cursor variable
declaration. ie: how to pass a define into an SQL cursor. Well I tried
your example (less the build method) but I am getting syntax errors ...
ie:
Q U I Z (PowerHouse 8.23.D7)
Copyright 2001 COGNOS INCORPORATED
>
> use mipssc5d.txt nol
> report summary &
> c_date_start &
c_date_end
set subfile name mipssc5a> > >
go
>
Records selected: 0
Records written: 1
> acc *mipssc5a
> show item
INPUT OUTPUT
MIPSSC5A TYPE SCALE SCALE DEC PICTURE
C_DATE_START DATE
DD-MMM-YYYY
C_DATE_END DATE
DD-MMM-YYYY
> rep all
> go
08-Sep-2005 MIPS Test Melbourne System (v10.1)
PAGE 1
C_DATE_START C_DATE_END
01-Jan-2004 31-Dec-2004
?
Records selected: 1
Lines printed: 3
Pages printed: 1
> sql in hisdb declare crs_invoiced_mstdet &
> cursor for &
select im.order_no, &
im.account_number, &
im.sales_rep, &
id.primary_id, &
id.secondary_id, &
id.gl_account_no, &
id.per_unit, &
id.minimum_cost, &
id.base_cost, &
> > > > > > > > > > id.invoice_qty, &
> id.charge_indicator, &
> id.sell_price, &
> id.invoiced_date &
> from (invoiced_master im &
> inner join invoiced_detail id &
> on (im.order_no = id.order_no)) &
> where (id.invoiced_date >= &
> :c_date_start and &
> id.invoiced_date <= &
> :c_date_end) &
> order by im.account_number, &
> im.sales_rep
> access crs_invoiced_mstdet
^^^^^^^^^^^^
*E* Expected: ( ABSOLUTE CEILING CENTRE CENTER CHECKSUM DATE
DATEEXTRACT INTERVAL DECIMALTIME REVERSE DAYS DOWNSHIFT
GETSYSTEMVAL ROUND LINKVALUE FLOOR LASTDAY CENTURY
ADDCENTURY
REMOVECENTURY LEFT LJ NCONVERT PACK ATTRIBUTE SUBSTITUTE
RIGHT
RJ SIZE SPREAD TRUNCATE UPSHIFT ZEROFILL DECRYPT ENCRYPT
INDEX
SOUNDEX MOD SUM ASCII <number> <string> MISSING NULL
CHARACTERS
LOGONID PORTID SYSDATE SYSTIME SYSNAME SYSDATETIME
SIGNONUSER UIC
<qualifier> <item>
*E* Invalid expression in SQL statement at: C_DATE_START A
^^^^^^^^^^
*E* Expected: ( ABSOLUTE CEILING CENTRE CENTER CHECKSUM DATE
DATEEXTRACT INTERVAL DECIMALTIME REVERSE DAYS DOWNSHIFT
GETSYSTEMVAL ROUND LINKVALUE FLOOR LASTDAY CENTURY
ADDCENTURY
REMOVECENTURY LEFT LJ NCONVERT PACK ATTRIBUTE SUBSTITUTE
RIGHT
RJ SIZE SPREAD TRUNCATE UPSHIFT ZEROFILL DECRYPT ENCRYPT
INDEX
SOUNDEX MOD SUM ASCII <number> <string> MISSING NULL
CHARACTERS
LOGONID PORTID SYSDATE SYSTIME SYSNAME SYSDATETIME
SIGNONUSER UIC
<qualifier> <item>
*E* Invalid expression in SQL statement at: C_DATE_END)
*E* Error parsing SQL statement.
DMS-E-GENERAL, A general exception has occurred during operation
'prepare
request'.
DMS-E-SS_SYNTAX, A syntax error was detected near 'C_DATE_START'.
>
-----Original Message-----
From: atla38 at dsl.pipex.com [mailto:atla38 at dsl.pipex.com]
Sent: Thursday, September 08, 2005 9:33 AM
To: Fernando Olmos; darren.reely at latticesemi.com
Subject: RE: QUIZ truncation vs varchar
And it could be that you have to qualify the columns as in im.primary_id
when linking in the second cursor.
Access the second cursor in quiz without the where clause and then do
'show items' to see how quiz is naming them - then try the same naming
format.
P.S. I still think you are better off asking the list as a whole.
Regards, Joe.
-----Original Message-----
From: Joe Boyle [mailto:atla38 at dsl.pipex.com]
Sent: 08 September 2005 00:26
To: 'fernando.olmos at hpa.com.au'; 'darren.reely at latticesemi.com'
Subject: RE: QUIZ truncation vs varchar
Have you checked that the second cursor actually works ?
Also, it seems that the table space you have available might be
insufficient to receive the output and I would speak to your DBA to get
more resources.
How many rows are in the second cursor and is there a one to one match
on the link items you are using ?
This may be nothing, but I would always code as below,
where (st.primary_id = :primary_id and &
st.secondary_id = :secondary_id)
and I wouldn't even qualify the link items as they can only be in the
second cursor, but then I always try anything to get code like this
working, regardless of how dumb the attempt might appear :)
P.S. I always advocate asking the list as a whole because there are
simply more heads working on the problem - and unlike me - others may
have experienced the same problem, or it could be a known bug.
Regards, Joe.
-----Original Message-----
From: fernando.olmos at hpa.com.au [mailto:fernando.olmos at hpa.com.au]
Sent: 07 September 2005 23:36
To: darren.reely at latticesemi.com
Cc: atla38 at dsl.pipex.com
Subject: RE: QUIZ truncation vs varchar
Thanks Darren. That's a neat one.
Whilst I've got you (and you too Joe), can you tell me what this error
means? I am again playing with cursors for another performance issue
(same tables as the original emails I posted to the forum).
*E* Data access error. (CRS_INVOICED_MSTDET)
*E* DMS-E-GENERAL, A general exception has occurred during operation
'asynchronous open'. (CRS_INVOICED_MSTDET)
*E* ORA-25153: Temporary Tablespace is Empty
(CRS_INVOICED_MSTDET)
Records selected: 0
The two cursors I am using is ...
;cursor to get the columns required from the invoiced ;master/detail in
hisdb
sql in hisdb declare crs_invoiced_mstdet &
cursor for &
select im.order_no, &
im.account_number, &
im.sales_rep, &
id.primary_id, &
id.secondary_id &
; id.gl_account_no, &
; id.per_unit, &
; id.minimum_cost, &
; id.base_cost, &
; id.invoice_qty, &
; id.charge_indicator, &
; id.sell_price, &
; id.invoiced_date &
from (invoiced_master im &
inner join invoiced_detail id &
on (im.order_no = id.order_no)) &
order by im.account_number, &
im.sales_rep
;cursor to get the columns required from the secondary_table in isdb
sql in isdb declare crs_secondary_table &
cursor for &
select st.primary_id, &
st.secondary_id, &
st.gl_account_no &
from secondary_table st &
where (:primary_id of &
crs_invoiced_mstdet &
= st.primary_id and &
:secondary_id of &
crs_invoiced_mstdet &
= st.secondary_id)
;-----------------------------------------------------------------
access crs_invoiced_mstdet link to crs_secondary_table
..
..etc..
..
As you can see the first cursor originally had more columns I wanted to
report, but I tried reducing it, and even with the mandantory columns
that I require, I still get the above error. I realise this is an Oracle
error message, but what does it mean? I know for a fact there are rows
to report, so I don't know why it says there are no records.
If I run the first cursor without linking the 2nd cursor I get over 2.8
million records without a problem - and fast too!
Thanks again for your valuable time and advice, as always.
Cheers
-----Original Message-----
From: darren.reely at latticesemi.com [mailto:darren.reely at latticesemi.com]
Sent: Thursday, September 08, 2005 3:05 AM
To: Fernando Olmos
Subject: Re: QUIZ truncation vs varchar
Fernando,
Good to here you figured it out on your own.
Here is piece of code that right justifies a string. It's overly
complicated because of the quote marks you wanted.
define x varchar*18 = 'abcd'
define y char*20 = rj("'" + x[1:18]) + "'"
report tab 1 "12345678901234567890123456789012345678901234567890" &
skip &
tab 1 x tab 31 y
go
The important part to notice is the x[1:18] that tells Powerhouse to use
the full 18 characters, even though we know only 4 are used with the
varchar x variable. That allows you to use the RJ function to shove the
text to the right.
Have a good day.
Darren
fernando.olmos at hpa.com.au wrote:
> Hi again gang.
>
> Here is a tricky one...
More information about the powerh-l
mailing list