<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.3132" name=GENERATOR></HEAD>
<BODY>
<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 vlink="blue" link="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 vlink="purple" link="blue">
<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></BODY></HTML>