db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Can I write a query that joins tables in two databases?
Date Fri, 16 May 2008 14:14:43 GMT
Hi Ian,

Table Functions can be invoked in the FROM list of any query block. 
Practically speaking, that means that you can invoke them in the 
top-level FROM list and materialize them anywhere that you can include a 
subquery. And yes, you can use them in outer joins. Here for instance is 
an outer join involving the PropertyFileVTI table function which lives 
in java/demo/vtis/java/org/apache/derbyDemo/vtis/example:

select *
from table( propertyFileVTI( 'foo' ) ) foo
left join table( propertyFileVTI( 'bar' ) ) bar
on foo.messageID = bar.messageID

Hope this helps,

Leslie Software wrote:
> Thanks Rick,
> I am glad I described the problem I was trying to solve.  Table Functions look very promising
I will start experimenting with them.  Are there any restrictions on where such a table can
be used?  Specifically, in my application, I want to be able to use an outer join.  That way
I can join the full card table with the users collection table and show them both cards they
do not have and the number of the cards they do have.
> Ian
> ----- Original Message ----
> From: Rick Hillegas <Richard.Hillegas@Sun.COM>
> <snip>
> XA, by itself, will not solve the following problem, which may be the 
> crux of your question: 
> <snip>
> You may want to investigate whether Table Functions can help you here. 
> Table Functions let you treat external data like local tables in the 
> database you are connected to. 
> <snip>
>       __________________________________________________________________
> Looking for the perfect gift? Give the gift of Flickr! 
> http://www.flickr.com/gift/

View raw message