apache ignite cross cache query - cannot parse sql -

im trying query has inner sub queries between 2 classes. every time try it, gives me sql parse error. therefore figure out problem, did basic cross cache query , gives me same parse error. ideas?

cacheconfiguration<integer, myclass1> cfg = new cacheconfiguration<>("class1cache"); cfg.setindexedtypes(integer.class, myclass1.class);  igniteconfiguration ignitionconfig = new igniteconfiguration(); ignitionconfig.setcacheconfiguration(cfg);  ignite ignite = ignition.getorstart(ignitionconfig);  ignitecache<integer, myclass1> cache = ignite.getorcreatecache(cfg);  stringbuilder builder = new stringbuilder();  builder.append(" select "); builder.append("    c.name "); builder.append("  "); builder.append("    myclass1 c,  \"class2cache\".myclass2 b  "); builder.append("  "); builder.append("    c.name = b.name   ");   sqlfieldsquery qry = new sqlfieldsquery(builder.tostring());  // execute query , obtain query result cursor. list<list<?>> res = cache.query(qry).getall(); 

so got basic sql working adding:

cfg.setcachemode(cachemode.replicated); 

but still cannot inner subqueries work.

builder.append(" select "); builder.append("    coalesce(sel1.name, sel2.name) name, "); builder.append("    coalesce(sel1.account, sel2.account) account, "); builder.append("    sel1.total_pos, sel2.total_neg, least(total_pos, total_neg) lbs,  "); builder.append("    result.period,   "); builder.append("    (lbs/period) * 100 total_result   "); builder.append("  ");  builder.append("    (  "); builder.append("        select  "); builder.append("            account, type, sum(item*cost) total_pos, name  "); builder.append("         "); builder.append("            myclass1 "); builder.append("         "); builder.append("            name = ?  "); builder.append("        ,  "); builder.append("            type = 'buy'  "); builder.append("        group  "); builder.append("            type  "); builder.append("    ) sel1,  ");  builder.append("    (  "); builder.append("        select  "); builder.append("            account, type, sum(item*cost) total_neg, name "); builder.append("         "); builder.append("            myclass1 "); builder.append("         "); builder.append("            name = ?  "); builder.append("        ,  "); builder.append("            type = 'sell'  "); builder.append("        group  "); builder.append("            type  "); builder.append("    ) sel2  ");   builder.append("    (  "); builder.append("        select  "); builder.append("            avg(b.d_rec) period, account, name  "); builder.append("         "); builder.append("        (  "); builder.append("            select  "); builder.append("                sum(price) d_rec, date, account, name "); builder.append("             "); builder.append("            (  "); builder.append("                select  "); builder.append("                    b.name, b.account, b.price, b.date"); builder.append("                 "); builder.append("                    class2cache.myclass2 b "); builder.append("                 "); builder.append("                    b.name = ?  "); builder.append("            )  "); builder.append("        group  "); builder.append("            date, account, name "); builder.append("        ) b  "); builder.append("        group "); builder.append("            account, name "); builder.append("    ) result  "); builder.append("  "); builder.append("    sel1.account = sel2.account  "); builder.append(" ,  "); builder.append("    result.account = sel1.account  "); 

edit: sql failing because cannot seem me result of aggregrated fields:

(lbs/period) * 100 total_result 

if random calc works

(20/10) *100 total_result 

any ideas?