Nested SELECT expresions in DECLARE CURSOR

Wed Aug 1 06:00:17 CDT 2007

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