Nested SELECT expresions in DECLARE CURSOR

[Fry, Mark]

Hi all,
 
Well, it's been a long while, but from memory I'm pretty sure that cursors in PowerHouse need to be written in Cognos SQL, not the native SQL of your database.  I think in this example, you probably need to use a derived table (called an inline view in Oracle).
 
Try coding the query as follows:
 
select table1.column1, dt.column2
from
table1, (select table2.column2 from table2 where ...) dt
where...
 
Best regards,
 
MF.

________________________________

From: powerh-l-bounces+mark.fry=cognos.com at lists.sowder.com [mailto:powerh-l-bounces+mark.fry=cognos.com at lists.sowder.com] On Behalf Of Daniel Rodríguez
Sent: 31 July 2007 22:50
To: Joe Boyle
Cc: powerh-l at lists.sowder.com
Subject: Re: Nested SELECT expresions in DECLARE CURSOR


I'm sorry, but I'm not very used to write in English... and maybe I'm no able of explaining my problem properly.

As I said, I'm using dual as a general table that can be found in every Oracle instalation in order to make the example code 'portable'. 

I'm trying to convert a SQL query into a PH CURSOR. That query involves a great number of tables and nested selects. 

Maybe if I had written:

select table1.column1, (select table2.column1
                                       from table2 
                                       where.....) 
  from table1 
  where ....;

It would have been more clear. Sorry.

Thank you all,

Daniel Rodriguez



2007/7/31, Joe Boyle <atla38 at dsl.pipex.com>: 

	By that I mean either "select 1 as one , 2 as two from dual" or even "select 1 as one , 2 as two from dual x, dual y"

	 

	
________________________________


	From: Joe Boyle [mailto:atla38 at dsl.pipex.com] 
	Sent: 31 July 2007 19:21
	To: 'Daniel Rodríguez'
	Subject: RE: Nested SELECT expresions in DECLARE CURSOR

	 

	How about "select 1, 2 from dual"

	 

	
________________________________


	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 Daniel Rodríguez
	Sent: 31 July 2007 18:55
	To: Joe Boyle
	Cc: powerh-l at lists.sowder.com
	Subject: Re: Nested SELECT expresions in DECLARE CURSOR

	 

	Hi Joe,
	
	I used the DUAL table as a general example.
	
	If I declare a CURSOR with the expresion "SELECT 1 from DUAL", it works so that's not the problem. 
	
	Thank you,
	
	Dani
	
	

	2007/7/31, Joe Boyle <atla38 at dsl.pipex.com >:

	I suspect that dual is'nt known by PH and you have to return values from DUAL via a stored procedure call.

	 

	
________________________________


	From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com [mailto: 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 Daniel Rodríguez
	Sent: 31 July 2007 18:45
	To: powerh-l at lists.sowder.com
	Subject: Nested SELECT expresions in DECLARE CURSOR

	 

	Hi all,
	
	In Oracle I'm able to use nested selects like this one:
	
	select 1 as one, (select 2 from dual d) as two
	 from dual
	
	When I try to do it in a screen I get the following message:
	
	screen kk
	sql in sgtompla &
	 declare csr_example cursor for & 
	select 1 , (select 2 from dual d)&
	 from dual
	 
	 cursor csr_example primary occurs 12
	*E* Error parsing SQL statement.
	DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
	  access sequential
	
	It is possible to use this expresion or I'm missing something. 
	
	
	Best regards,
	
	Daniel Rodriguez
 
       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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20070801/4d4af604/attachment.html