SQL Cursor limitations?

[Joe Boyle]

You can code as below where the :varaiables are items from the subfile
executed in the previous run, 

can cle

set nostatistics

define ddate_start_p date cent included = parm 
define ddate_end_p date	cent included = parm 

define ddate_start date	cent included = ddate_start_p &
if ddate_start_p gt 1000 else 01000101

define ddate_end date	cent included =  ddate_end_p &
if ddate_end_p gt 1000 else  sysdate

define ddate_null char*1 = parm 

set sub keep name c:\joetop\phwork\SKIP_PAGE_1

rep summ  ddate_start ddate_end  ddate_null

BUILD SKIP_PAGE_1


can cle
set nostatistic nowarn
set rep nolim

SQL IN example_4gl DECLARE mydb_count CURSOR FOR     &
 SELECT count (*)  mytot FROM Orders &
  where ( ( order_date  between :ddate_start and :ddate_end  ) or &
 ( ( order_date is null ) and ('N' = :ddate_null )  )	  )

SQL IN example_4gl DECLARE dbquery CURSOR FOR     &
 SELECT * FROM Orders &
where ( ( order_date  between :ddate_start and :ddate_end ) or &
      ( ( order_date is null ) and ('N' = :ddate_null )  )	) &
          order by Sales_Rep_Id  ,order_no 

ACCESS * c:\joetop\phwork\SKIP_PAGE_1 link to mydb_cnt link to dbquery 

...

Build SKIP_PAGE_2

But, you would need a quiz command like 'quiz auto= two_passes.qzs', where
file two_passes.qzs would contain syntax like that below,

exe SKIP_PAGE_1.qzc
exe SKIP_PAGE_2.qzc
exit

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
Whittall, Conrad
Sent: 17 August 2005 15:28
To: powerh-l at lists.sowder.com
Subject: RE: SQL Cursor limitations?

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. 
        
 

-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.