SQL join Query

[Shaomin Zhang <Sh...@tudor.com>]

Hi

I have a couple of questions about SQL join queries: I can only use "select *", I get exceptions if I specify tables/alias rather than *, and this "select *" returns the value objects from the first table (or cache) of the join, nothing else. For example, if my query string is

        String joinSql = "select _val, * from \"Order\".Order, \"Trade\".Trade t where Order.orderId = t.orderId and Order.traderId = t.traderId and Order.traderId = ? and Order.orderId = ?";

I get the exception as below:

Exception in thread "main" javax.cache.CacheException: class org.apache.ignite.IgniteException: Only queries starting with 'SELECT *' are supported or use SqlFieldsQuery instead: select _val, * from "Order".Order, "Trade".Trade t where Order.orderId = t.orderId and Order.traderId = t.traderId


So my question is: is there a way to specify which objects should be returned from the joined caches?


Thank you

Shaomin


_________________________________________________________

This email, its contents, and any attachments transmitted with it are intended only for the addressee(s) and may be confidential and legally privileged. We do not waive any confidentiality by misdelivery. If you have received this email in error, please notify the sender immediately and delete it. You should not copy it, forward it or otherwise use the contents, attachments or information in any way. Any liability for viruses is excluded to the fullest extent permitted by law.

Tudor Capital Europe LLP (TCE) is authorised and regulated by The Financial Conduct Authority (the FCA). TCE is registered as a limited liability partnership in England and Wales No: OC340673 with its registered office at 10 New Burlington Street, London, W1S 3BE, United Kingdom

Re: SQL join Query

[Pavel Tupitsyn <pt...@gridgain.com>]

Hi Shaomin,

You'll have to use SqlFieldsQuery to select specific objects/fields.
_key and _val fields represent key and value objects.

Also, use fully-qualified table name or an alias before field names.
The following SqlFieldsQuery will return Order and Trade objects in a list:
"select o._val, t._val from \"Order\".Order o, \"Trade\".Trade t where
Order.orderId = t.orderId and Order.traderId = t.traderId and
Order.traderId = ? and Order.orderId = ?"

Thanks,

On Mon, Feb 15, 2016 at 1:45 PM, Shaomin Zhang <Sh...@tudor.com>
wrote:

> Hi
>
> I have a couple of questions about SQL join queries: I can only use
> "select *", I get exceptions if I specify tables/alias rather than *, and
> this "select *" returns the value objects from the first table (or cache)
> of the join, nothing else. For example, if my query string is
>
>         String joinSql = "select _val, * from \"Order\".Order,
> \"Trade\".Trade t where Order.orderId = t.orderId and Order.traderId =
> t.traderId and Order.traderId = ? and Order.orderId = ?";
>
> I get the exception as below:
>
> Exception in thread "main" javax.cache.CacheException: class
> org.apache.ignite.IgniteException: Only queries starting with 'SELECT *'
> are supported or use SqlFieldsQuery instead: select _val, * from
> "Order".Order, "Trade".Trade t where Order.orderId = t.orderId and
> Order.traderId = t.traderId
>
>
> So my question is: is there a way to specify which objects should be
> returned from the joined caches?
>
>
> Thank you
>
> Shaomin
>
>
> _________________________________________________________
>
> This email, its contents, and any attachments transmitted with it are
> intended only for the addressee(s) and may be confidential and legally
> privileged. We do not waive any confidentiality by misdelivery. If you have
> received this email in error, please notify the sender immediately and
> delete it. You should not copy it, forward it or otherwise use the
> contents, attachments or information in any way. Any liability for viruses
> is excluded to the fullest extent permitted by law.
>
> Tudor Capital Europe LLP (TCE) is authorised and regulated by The
> Financial Conduct Authority (the FCA). TCE is registered as a limited
> liability partnership in England and Wales No: OC340673 with its registered
> office at 10 New Burlington Street, London, W1S 3BE, United Kingdom
>



-- 
-- 
Pavel Tupitsyn
GridGain Systems, Inc.
www.gridgain.com