Nested SELECT expresions in DECLARE CURSOR

[Daniel Rodríguez]

>
> 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]
> > > *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]
> > > *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/d8c8e15b/attachment.htm