SQL Cursor limitations?

[Whittall, Conrad]

You cannot directly reference an item in a subfile within a cursor
declaration, since the DBMS that will be executing the SQL has no
knowledge of PowerHouse subfiles.

However, you might be able to achieve what you want by using
substitution variables and linking to your cursor in an ACCESS statement
that first accesses your subfile.

Take a look at the sections starting on page 19 of the "PowerHouse and
Relational Databases" manual for some ideas and examples. You can get
all of the manuals from the PowerHouse web site...

http://powerhouse.cognos.com/products/powerhouse/download.html

Best regards,
Conrad

-----Original Message-----
From: powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com
[mailto:powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com] On
Behalf Of fernando.olmos at hpa.com.au
Sent: Wednesday 17 August 2005 01:38
To: fernando.olmos at hpa.com.au; powerh-l at lists.sowder.com
Subject: RE: SQL Cursor limitations?

More questions...

Can SQL cursors, in PH, use subfiles and defines/temps within the query
itself? I assume the answer to the defines/temps is to use variables,
but I doubt it can use subfiles. I tried the obvious... and it failed
with syntax errors on the asterisk.

sql in hisdb declare crs_invoiced_mstdet        &
cursor for                              &
        select  im.order_no,            &
                sf1.somefield    &
        from    invoiced_master     im, &
                invoiced_detail     id,  &
		    *subfile1           sf1  &
        where   id.order_no = im.order_no and &
                id.order_no = sf1.order_no and &
                im.credit_note_flag          =  'Y'             and &
                id.gl_account_no             <> 'D2480'             &
        order by im.order_no


-----Original Message-----
From: Fernando Olmos
Sent: Wednesday, August 17, 2005 1:38 PM
To: 'powerh-l at lists.sowder.com'
Subject: RE: SQL Cursor limitations?

<snip>
**********************************************************************
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
********************************************************************** 
  
       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.