Hello to all,<br><br>I have some problems trying to use subqueries with INNER JOIN.<br><br>Assume the following schema:<br><br><blockquote style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote">
CREATE TABLE table1 (a INTEGER);<br>CREATE TABLE table2 (a INTEGER, b INTEGER);<br><br>INSERT INTO table1 VALUES(1);<br>INSERT INTO table1 VALUES(2);<br>INSERT INTO table2 VALUES(2, 2);<br clear="all"></blockquote><div><br>
I pose this query<br><blockquote style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote">SELECT * FROM (SELECT a, 2 AS b FROM table1) t1 INNER JOIN table2 t2 ON (t1.b = t2.b); <br>
</blockquote><div><br>and I get the following mal errors:<br><blockquote style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote">TypeException:user.s5_1[44]:&#39;bat.mirror&#39; undefined in: _62:any := bat.mirror(_61:int)<br>
TypeException:user.s5_1[46]:&#39;algebra.join&#39; undefined in: _64:any := algebra.join(_62:any, _63:bat[:oid,:oid])<br>TypeException:user.s5_1[47]:&#39;algebra.markH&#39; undefined in: _65:any := algebra.markH(_64:any, _66:oid)<br>
TypeException:user.s5_1[48]:&#39;algebra.markT&#39; undefined in: _67:any := algebra.markT(_64:any, _66:oid)<br>TypeException:user.s5_1[49]:&#39;bat.reverse&#39; undefined in: _68:any := bat.reverse(_67:any)<br>TypeException:user.s5_1[50]:&#39;algebra.join&#39; undefined in: _69:any := algebra.join(_68:any, _61:int)<br>
TypeException:user.s5_1[51]:&#39;algebra.join&#39; undefined in: _70:any := algebra.join(_65:any, _59:bat[:oid,:int])<br>TypeException:user.s5_1[55]:&#39;algebra.join&#39; undefined in: _77:any := algebra.join(_76:bat[:void,:oid], _65:any)<br>
TypeException:user.s5_1[56]:&#39;bat.reverse&#39; undefined in: _78:any := bat.reverse(_77:any)<br>TypeException:user.s5_1[57]:&#39;algebra.markT&#39; undefined in: _79:any := algebra.markT(_78:any, _43:oid)<br>TypeException:user.s5_1[58]:&#39;bat.reverse&#39; undefined in: _80:any := bat.reverse(_79:any)<br>
TypeException:user.s5_1[59]:&#39;algebra.leftjoin&#39; undefined in: _81:any := algebra.leftjoin(_80:any, _42:bat[:oid,:int])<br>TypeException:user.s5_1[64]:&#39;algebra.project&#39; undefined in: _86:any := algebra.project(_81:any, _83:int)<br>
TypeException:user.s5_1[65]:&#39;algebra.join&#39; undefined in: _87:any := algebra.join(_86:any, _85:bat[:int,:oid])<br>TypeException:user.s5_1[66]:&#39;bat.reverse&#39; undefined in: _88:any := bat.reverse(_87:any)<br>TypeException:user.s5_1[67]:&#39;algebra.markT&#39; undefined in: _89:any := algebra.markT(_88:any, _21:oid)<br>
TypeException:user.s5_1[68]:&#39;bat.reverse&#39; undefined in: _90:any := bat.reverse(_89:any)<br>TypeException:user.s5_1[69]:&#39;algebra.leftjoin&#39; undefined in: _91:any := algebra.leftjoin(_90:any, _20:bat[:oid,:int])<br>
TypeException:user.s5_1[76]:&#39;algebra.markT&#39; undefined in: _103:any := algebra.markT(_87:any, _101:oid)<br>TypeException:user.s5_1[77]:&#39;bat.reverse&#39; undefined in: _104:any := bat.reverse(_103:any)<br>TypeException:user.s5_1[78]:&#39;algebra.leftjoin&#39; undefined in: _105:any := algebra.leftjoin(_104:any, _81:any)<br>
TypeException:user.s5_1[79]:&#39;algebra.project&#39; undefined in: _106:any := algebra.project(_105:any, _96:bte)<br>TypeException:user.s5_1[86]:&#39;algebra.leftjoin&#39; undefined in: _113:any := algebra.leftjoin(_80:any, _59:bat[:oid,:int])<br>
TypeException:user.s5_1[87]:&#39;algebra.leftjoin&#39; undefined in: _114:any := algebra.leftjoin(_104:any, _113:any)<br>39000!program contains errors<br></blockquote> <br>If i don&#39;t use a subquery and i first create the table i need, the query executes flawlessly and it returns the expected results.<br>
<br><blockquote style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote">CREATE TABLE table3 (a INTEGER, b INTEGER);<br>INSERT INTO table3(a,b) (SELECT a, 2 AS b FROM table1);<br>
SELECT * FROM (SELECT a, b FROM table3) t1 INNER JOIN table2 t2 ON (t1.b = t2.b);<br></blockquote><br><br><blockquote style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote">
Results:<br>+------+------+------+----+<br>| a    | b    | a    | b    |<br>+==+===+===+===+<br>|    2 |    2 |    2 |    2 |<br>|    1 |    2 |    2 |    2 |<br>+------+------+------+----+<br></blockquote><br>Can anyone help? Is there something wrong in my first query or it is a bug?<br>
</div></div><br>Thank you in advance,<br>George Garbis<br>