<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>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2>Hi all,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><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 class=593082307-01082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2>Try coding the query as follows:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2>select table1.column1, dt.column2</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2>from</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2>table1, (select table2.column2 from table2 where ...)
dt</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2>where...</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2>Best regards,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=593082307-01082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=593082307-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> 31 July 2007 22:50<BR><B>To:</B> Joe
Boyle<BR><B>Cc:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> Re: Nested
SELECT expresions in DECLARE CURSOR<BR></FONT><BR></DIV>
<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
href="mailto:atla38@dsl.pipex.com">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 class=q>
<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 class=q><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 class=e id=q_1141dbd85fc8d627_5>
<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></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></BODY></HTML>