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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="blue" lang="EN-GB">
<div>
<p><font color="blue" face="Arial" size="3"><span style="font-size: 12pt; font-family: Arial; color: blue;">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; font-family: Arial; color: blue;"> </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-size: 10pt; font-family: Tahoma; font-weight: bold;" 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 class="q">
<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; font-family: Arial; color: blue;">How about "select 1, 2 from
dual"</span></font></p>
<p><font color="blue" face="Arial" size="3"><span style="font-size: 12pt; font-family: Arial; color: blue;"> </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-size: 10pt; font-family: Tahoma; font-weight: bold;" 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 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 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 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 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 link="blue" vlink="purple">
<div>
<p><font color="blue" face="Arial" size="3"><span style="font-size: 12pt; font-family: Arial; color: blue;">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; font-family: Arial; color: blue;"> </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-size: 10pt; font-family: Tahoma; font-weight: bold;" 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>