<div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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 href="mailto:Mark.Fry@cognos.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">Mark.Fry@cognos.com</a>>:</span><div><span class="e" id="q_11420cb89793226d_1"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Hi all,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Try coding the query as follows:</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">select table1.column1, dt.column2</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">from</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">table1, (select table2.column2 from table2 where ...)
dt</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">where...</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Best regards,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">MF.</font></span></div><br>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font face="Tahoma" size="2"><b>From:</b>
powerh-l-bounces+mark.fry=<a href="mailto:cognos.com@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">cognos.com@lists.sowder.com</a>
[mailto:<a href="mailto:powerh-l-bounces+mark.fry=cognos.com@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">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 href="mailto:powerh-l@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">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 href="mailto:atla38@dsl.pipex.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">atla38@dsl.pipex.com</a>>:</span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div vlink="blue" link="blue" lang="EN-GB">
<div>
<p><font color="blue" face="Arial" 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 color="blue" face="Arial" 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 style="font-size: 12pt;" lang="EN-US">
<hr align="center" size="2" width="100%">
</span></font></div>
<p><b><font face="Tahoma" size="2"><span style="font-weight: bold; font-size: 10pt; font-family: Tahoma;" lang="EN-US">From:</span></font></b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family: Tahoma;" lang="EN-US">
Joe Boyle [mailto:<a href="mailto:atla38@dsl.pipex.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">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 color="blue" face="Arial" size="3"><span style="font-size: 12pt; color: blue; font-family: Arial;">How about "select 1,
2 from dual"</span></font></p>
<p><font color="blue" face="Arial" 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 style="font-size: 12pt;" lang="EN-US">
<hr align="center" size="2" width="100%">
</span></font></div>
<p><b><font face="Tahoma" size="2"><span style="font-weight: bold; font-size: 10pt; font-family: Tahoma;" lang="EN-US">From:</span></font></b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family: Tahoma;" lang="EN-US">
powerh-l-bounces+atla38=<a href="mailto:dsl.pipex.com@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">dsl.pipex.com@lists.sowder.com</a> [mailto:<a href="mailto:powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
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 href="mailto:powerh-l@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">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 href="mailto:atla38@dsl.pipex.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">atla38@dsl.pipex.com
</a>>:</span></font></span></p>
<div vlink="purple" link="blue">
<div>
<p><font color="blue" face="Arial" 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 color="blue" face="Arial" 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 style="font-size: 12pt;" lang="EN-US">
<hr align="center" size="2" width="100%">
</span></font></div>
<p><b><font face="Tahoma" size="2"><span style="font-weight: bold; font-size: 10pt; font-family: Tahoma;" lang="EN-US">From:</span></font></b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family: Tahoma;" lang="EN-US">
powerh-l-bounces+atla38=<a href="mailto:dsl.pipex.com@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">dsl.pipex.com@lists.sowder.com</a> [mailto:<a href="mailto:powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
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 href="mailto:powerh-l@lists.sowder.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">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>