Nested SELECT expresions in DECLARE CURSOR

[Deskin, Bob]

We never specifically documented what Cognos SQL was. It conforms to SQL 92 so database specific options may not be available. Try what Mark suggests. Also, contact Customer Support (assuming you're supported). I will attempt to get Cognos SQL documented properly for the next manual update.
 
Bob

	-----Original Message-----
	From: powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com [mailto:powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com] On Behalf Of Fry, Mark
	Sent: August 1, 2007 7:00 AM
	To: Daniel Rodríguez; powerh-l at lists.sowder.com
	Subject: RE: Nested SELECT expresions in DECLARE CURSOR
	
	
	Hi Dani,
	 
	The 'PowerHouse and Relational Databases' manual may be useful, although it does not document each legal SQL clause.  PowerHouse's Cognos SQL conforms to the SQL 92 ANSI standard (Entry level), so the assumption is that you code queries that conform to this.  You may also find the PowerHouse Rules manual useful - chapter 6 (I think!) documents the SQL functions you can use.
	 
	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: 01 August 2007 10:44
	To: powerh-l at lists.sowder.com
	Subject: Re: Nested SELECT expresions in DECLARE CURSOR
	
	

		Hi Mark,
		
		I didn't knew I can't use native SQL... Is there a COGNOS SQL manual? 
		
		Thank you all,
		
		Dani
		
		
		
		2007/8/1, Fry, Mark < Mark.Fry at cognos.com>: 
		

			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 <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/aca2a59a/attachment.html