<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.3132" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=279360615-01082007><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=279360615-01082007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=279360615-01082007><FONT face=Arial color=#0000ff
size=2>Bob</FONT></SPAN></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com] <B>On Behalf
Of </B>Fry, Mark<BR><B>Sent:</B> August 1, 2007 7:00 AM<BR><B>To:</B> Daniel
Rodríguez; powerh-l@lists.sowder.com<BR><B>Subject:</B> RE: Nested SELECT
expresions in DECLARE CURSOR<BR><BR></FONT></DIV>
<DIV dir=ltr align=left><SPAN class=421344310-01082007><FONT face=Arial
color=#0000ff size=2>Hi Dani,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=421344310-01082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=421344310-01082007><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=421344310-01082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=421344310-01082007><FONT face=Arial
color=#0000ff size=2>Best regards,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=421344310-01082007></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=421344310-01082007><FONT face=Arial
color=#0000ff size=2>MF.</FONT> </SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
powerh-l-bounces+mark.fry=cognos.com@lists.sowder.com
[mailto:powerh-l-bounces+mark.fry=cognos.com@lists.sowder.com] <B>On Behalf Of
</B>Daniel Rodríguez<BR><B>Sent:</B> 01 August 2007 10:44<BR><B>To:</B>
powerh-l@lists.sowder.com<BR><B>Subject:</B> Re: Nested SELECT expresions in
DECLARE CURSOR<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Hi
Mark,<BR><BR>I didn't knew I can't use native SQL... Is there a COGNOS SQL
manual? <BR><BR>Thank you all,<BR><BR>Dani<BR><BR><BR>
<DIV><SPAN class=gmail_quote>2007/8/1, Fry, Mark < <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:Mark.Fry@cognos.com"
target=_blank>Mark.Fry@cognos.com</A>>:</SPAN>
<DIV><SPAN class=e id=q_11420cb89793226d_1>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV>
<DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff size=2>Hi
all,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff size=2>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).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff size=2>Try
coding the query as follows:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff size=2>select
table1.column1, dt.column2</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2>from</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2>table1, (select table2.column2 from table2 where ...)
dt</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2>where...</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff size=2>Best
regards,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2>MF.</FONT></SPAN></DIV><BR>
<DIV lang=en-us dir=ltr align=left>
<HR>
<FONT face=Tahoma size=2><B>From:</B> powerh-l-bounces+mark.fry=<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:cognos.com@lists.sowder.com"
target=_blank>cognos.com@lists.sowder.com</A> [mailto:<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:powerh-l-bounces+mark.fry=cognos.com@lists.sowder.com"
target=_blank>powerh-l-bounces+mark.fry=cognos.com@lists.sowder.com</A>]
<B>On Behalf Of </B>Daniel Rodríguez<BR><B>Sent:</B> 31 July 2007
22:50</FONT>
<DIV><SPAN><FONT face=Tahoma size=2><BR><B>To:</B> Joe Boyle<BR><B>Cc:</B>
<A onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:powerh-l@lists.sowder.com"
target=_blank>powerh-l@lists.sowder.com</A><BR><B>Subject:</B> Re: Nested
SELECT expresions in DECLARE CURSOR<BR></FONT></SPAN></DIV><BR></DIV>
<DIV><SPAN>
<DIV></DIV>I'm sorry, but I'm not very used to write in English... and
maybe I'm no able of explaining my problem properly.<BR><BR>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'. <BR><BR>I'm
trying to convert a SQL query into a PH CURSOR. That query involves a
great number of tables and nested selects. <BR><BR>Maybe if I had
written:<BR><BR>select table1.column1, (select
table2.column1<BR>
from table2
<BR>
where.....) <BR> from table1 <BR> where ....;<BR><BR>It would
have been more clear. Sorry.<BR><BR>Thank you all,<BR><BR>Daniel
Rodriguez<BR><BR><BR>
<DIV><SPAN class=gmail_quote>2007/7/31, Joe Boyle <<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:atla38@dsl.pipex.com"
target=_blank>atla38@dsl.pipex.com</A>>:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV lang=EN-GB link="blue" vlink="blue">
<DIV>
<P><FONT face=Arial color=blue size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: blue; FONT-FAMILY: Arial">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"</SPAN></FONT></P>
<P><FONT face=Arial color=blue size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: blue; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<DIV>
<DIV style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN lang=EN-US style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P><B><FONT face=Tahoma size=2><SPAN lang=EN-US
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> Joe Boyle [mailto:<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:atla38@dsl.pipex.com"
target=_blank>atla38@dsl.pipex.com</A>] <BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> 31 July 2007
19:21<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> 'Daniel
Rodríguez'<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE:
Nested SELECT expresions in DECLARE CURSOR</SPAN></FONT><SPAN
lang=EN-US></SPAN></P></DIV><SPAN>
<P><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P><FONT face=Arial color=blue size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: blue; FONT-FAMILY: Arial">How about
"select 1, 2 from dual"</SPAN></FONT></P>
<P><FONT face=Arial color=blue size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: blue; FONT-FAMILY: Arial"></SPAN></FONT> </P></SPAN>
<DIV>
<DIV style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN lang=EN-US style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P><B><FONT face=Tahoma size=2><SPAN lang=EN-US
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> powerh-l-bounces+atla38=<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:dsl.pipex.com@lists.sowder.com"
target=_blank>dsl.pipex.com@lists.sowder.com</A> [mailto:<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com"
target=_blank>
powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com</A>] <B><SPAN
style="FONT-WEIGHT: bold">On Behalf Of </SPAN></B>Daniel
Rodríguez<SPAN><BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B>
31 July 2007 18:55<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
Joe Boyle<BR></SPAN><B><SPAN style="FONT-WEIGHT: bold">Cc:</SPAN></B> <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:powerh-l@lists.sowder.com"
target=_blank>powerh-l@lists.sowder.com</A><BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: Nested SELECT
expresions in DECLARE CURSOR</SPAN></FONT><SPAN
lang=EN-US></SPAN></P></DIV>
<DIV><SPAN>
<P><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P style="MARGIN-BOTTOM: 12pt"><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">Hi Joe,<BR><BR>I used the DUAL table as a
general example.<BR><BR>If I declare a CURSOR with the expresion "SELECT
1 from DUAL", it works so that's not the problem. <BR><BR>Thank
you,<BR><BR>Dani<BR><BR></SPAN></FONT></P>
<DIV>
<P><SPAN><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">2007/7/31, Joe Boyle <<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:atla38@dsl.pipex.com" target=_blank>atla38@dsl.pipex.com
</A>>:</SPAN></FONT></SPAN></P>
<DIV link="blue" vlink="purple">
<DIV>
<P><FONT face=Arial color=blue size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: blue; FONT-FAMILY: Arial">I suspect that
dual is'nt known by PH and you have to return values from DUAL via a
stored procedure call.</SPAN></FONT></P>
<P><FONT face=Arial color=blue size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: blue; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<DIV>
<DIV style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN lang=EN-US style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P><B><FONT face=Tahoma size=2><SPAN lang=EN-US
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> powerh-l-bounces+atla38=<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:dsl.pipex.com@lists.sowder.com"
target=_blank>dsl.pipex.com@lists.sowder.com</A> [mailto:<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com"
target=_blank>
powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com</A>] <B><SPAN
style="FONT-WEIGHT: bold">On Behalf Of </SPAN></B>Daniel
Rodríguez<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> 31 July
2007 18:45<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:powerh-l@lists.sowder.com"
target=_blank>powerh-l@lists.sowder.com</A><BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> Nested SELECT expresions
in DECLARE CURSOR</SPAN></FONT></P></DIV>
<DIV><SPAN>
<P><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P style="MARGIN-BOTTOM: 12pt"><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">Hi all,<BR><BR>In Oracle I'm able to use nested
selects like this one:<BR><BR><I><SPAN style="FONT-STYLE: italic">select
1 as one, (select 2 from dual d) as two<BR> from
dual<BR></SPAN></I><BR>When I try to do it in a screen I get the
following message:<BR><BR><I><SPAN style="FONT-STYLE: italic">screen
kk<BR>sql in sgtompla &<BR> declare csr_example cursor for
& <BR>select 1 , (select 2 from dual d)&<BR> from
dual<BR> <BR> cursor csr_example primary occurs 12<BR><FONT
color=red><SPAN style="COLOR: red">*E* Error parsing SQL
statement.<BR>DMS-E-GENERAL, A general exception has occurred during
operation 'prepare request'.<BR></SPAN></FONT> access
sequential<BR></SPAN></I><BR>It is possible to use this expresion or I'm
missing something. <BR><BR><BR>Best regards,<BR><BR>Daniel
Rodriguez</SPAN></FONT></P></SPAN></DIV></DIV></DIV></DIV>
<P><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P></SPAN></DIV></DIV></DIV></BLOCKQUOTE></DIV><BR></SPAN></DIV></DIV>
<DIV> </DIV>
<DIV> 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.
</DIV></DIV></BLOCKQUOTE></SPAN></DIV></DIV><BR></BLOCKQUOTE></DIV><BR></BLOCKQUOTE></BODY></HTML>