Support of Inner View Query

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Support of Inner View Query

Suresh. T. K
Hello, is there a way I can build SQL Mapper to take advantage of
Inner view query
e.g.
select a.*, b.column1,b.column2
from table1 a,
        (select c.column1,c.column2 from table2 b, table3 c
         where b.column4 = c.column4) b
where a.column2 = b.column2
I know this SQL works in Oracle, but not sure any other RDBMS
supports.

How  can I achieve this in MyBatis so that my XMLMapper is independent
of RDBMS.

Thanks
Suresh
Reply | Threaded
Open this post in threaded view
|

Re: Support of Inner View Query

Guy Rouillier
On 10/1/2010 6:08 PM, Suresh. T. K wrote:

> Hello, is there a way I can build SQL Mapper to take advantage of
> Inner view query
> e.g.
> select a.*, b.column1,b.column2
> from table1 a,
> (select c.column1,c.column2 from table2 b, table3 c
> where b.column4 = c.column4) b
> where a.column2 = b.column2
> I know this SQL works in Oracle, but not sure any other RDBMS
> supports.
>
> How  can I achieve this in MyBatis so that my XMLMapper is independent
> of RDBMS.

I don't understand the question.  The SQL you create is passed to the
DBMS as is; MyBatis doesn't do anything with it, so as long as your
target DBMS can process the SQL you specify, you are all set.  All
MyBatis is doing is mapping the resultset to one or more Java object
types.  (Well, that's all in the most common case; as you can see in the
User's Guide, MyBatis also has facilities for conditional clauses, etc.
  But that doesn't change the fact that the resulting SQL gets passed to
the DBMS directly.)

So, your question seems to be, completely outside the scope of MyBatis,
if there is a way to specify SQL that has full nested SQL statements
where column names or expressions are usually expected, in a way that is
universally recognized by multiple DBMSs.  The answer is no, since such
nested SQL statements are not standard.  Having said that, DBMSs other
than Oracle recognize such syntax today, e.g., PostgreSQL.


--
Guy Rouillier