How to use SQL UNION with Ignite Caches

Question

I was having a ignite cache and doing sql queries as follows.

    IgniteCache<String, ClassName> cache = ignite.cache(CACHE_NAME);
    private static final String sql = "Select timestamp from cache1 where  orderId = ? and timestamp <= ? and timestamp >= ? ";
    SqlFieldsQuery sqlQ = new SqlFieldsQuery(sql).setArgs(id, t1, t2);
        try (QueryCursor<List<?>> cursor = cache.query(sqlQ)) {
            for (List<?> row : cursor) {
                    timestamps.add((Long) row.get(0));          
            }

Now I want to query from two different caches and get the union. I was able to successfully write the SQL union operation.

private static final String sqlTwoCaches = "Select timestamp from cache1 union all Select timestamp from cache2 order by timestamp";

I want to add the timestamp results from both caches to a single arraylist. I want to know how to use the query cursor? Now there are two caches and how to do the this part? (QueryCursor<List<?>> cursor = cache.query(sqlTwoCaches))

Or is there any other way to do this?

Answer 1

You need to "fully qualify" the second cache name, but otherwise, it should Just Work.

Example:

IgniteCache<String, ClassName1> cache1 = ignite.cache("table1");
IgniteCache<String, ClassName2> cache2 = ignite.cache("table2");
private static final String sql = "select timestamp from ClassName1 UNION ALL select timestamp from table2.ClassName2 ";
SqlFieldsQuery sqlQ = new SqlFieldsQuery(sql);
try (QueryCursor<List<?>> cursor = cache1.query(sqlQ)) {
    for (List<?> row : cursor) {
            timestamps.add((Long) row.get(0));          
    }
 }