SQL Cursor limitations?

[Joe Boyle]

first of all I would suggest that you place the 'where' clause in brackets
as below. Also, until the cursor is working, I would reduce the scope of the
cursor and build it up gradually, try the simple syntax below and then add
more columns followed by the 'order by' syntax,

sql in hisdb declare crs_invoiced_mstdet        &
cursor for                              &
        select  im.order_no,            &
                im.credit_note_flag,    &
                id.gl_account_no,       &

        from    invoiced_master     im, &
                invoiced_detail     id  &

        where  ( (im.credit_note_flag   =  'Y'  ) and &
                (id.gl_account_no      <> 'D2480'   )   )

;       &
;        order by im.order_no

I am assuming that you have a 'access crs_invoiced_mstdet' statement, and
that this is a quiz run ?

I also think that the cursor will return a x-product of all rows in each
table as I can see no link between im rows and id rows; if you can specify a
link this will reduce the number of rows returned in the cursor.

        where  (  (im.invoice_number    =   id.invoice_number ) and  &
(im.credit_note_flag   =  'Y'  )              and &
                  (id.gl_account_no      <> 'D2480'   )  )  
			order by im.order_no             

Regards, Joe.


-----Original Message-----
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
fernando.olmos at hpa.com.au
Sent: 17 August 2005 04:34
To: powerh-l at lists.sowder.com
Subject: SQL Cursor limitations?

Hi guys,

I am still working on this performance task using SQL cursors.
Can someone tell me why I am getting this error?

*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-01652: unable to extend temp segment by 128 in tablespace TEMP

Here is the cursor ...

sql in hisdb declare crs_invoiced_mstdet        &
cursor for                              &
        select  im.order_no,            &
                im.account_number,      &
                im.order_name,          &
                im.comment_1,           &
                im.comment_2,           &
                im.comment_3,           &
                im.credit_amount_1,     &
                im.credit_area_1,       &
                im.credit_note_flag,    &
                id.base_cost,           &
                id.care_number,         &
                id.charge_indicator,    &
                id.credit_note_no,      &
                id.gl_account_no,       &
                id.invoice_number,      &                id.invoice_qty,
&
                id.invoiced_date,       &
                id.minimum_cost,        &
                id.per_unit,            &
                id.sales_tax_amt,       &
                id.sell_price           &
        from    invoiced_master     im, &
                invoiced_detail     id  &
        where   im.credit_note_flag          =  'Y'             and &
                id.gl_account_no             <> 'D2480'             &
;                (substr(im.comment_1, 1, 10) = 'HPA Errors'     or  &
;                 substr(im.comment_1, 1, 12) = 'Process/Prod'   or  &
;                 substr(im.comment_1, 1, 13) = 'Service Level') &
        order by im.order_no


Also, if you know of a way to use the substr() function, that would be
great.

Cheers
**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and
intended solely for the use of the individual or entity to whom they are
addressed.  If you received this e-mail in error, please notify the HPA
Postmaster, postmaster at hpa.com.au, then delete  the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by MimeSweeper.  Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For
more information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************

-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.