Hi Chandan, Subqueries in IN clause (or as another operand in the WHERE clause for that matter) is currently not supported by MetaModel. MetaModel thus far only supports subqueries in the FROM clause. So to your question if it can be fixed, then at least not with the current design - but someone could of course spend the effort in implementing support for it, which would be lovely.
As a workaround, usually you can express the query with a join instead (and in your case then also an additional WHERE and GROUP BY item). Reading your code I couldn't find out exactly how because I think you might be missing something to join/link the two tables together? What would your preferred SQL query look like? One remark to your code: It seems you're defining the columns and tables yourself and putting them into some class structure which represents the tables? This is not how MetaModel was thought to be used; rather than you defining the model, it would be exposed by the library through the metadata discovery. Thus, calling for instance ... DataContext dataContext = ... Table[] tables = dataContext.getDefaultSchema().getTables(); ... would get you the *actual *tables of the data source you're working with. Similarly you can get tables, columns, schemas etc. by name and by traversal. Not sure if you are aware of this when coding? Best regards, Kasper 2013/11/1 Chandan Madhesia <[email protected]> > Hi > > I am trying to execute where clause of the following form. > > SELECT * FROM <someTable> WHERE <someColumn> IN (SELECT <someOtherColumn> > from <someOtherTable>) ; > > This is the code. > > import java.util.Date; > import org.eobjects.metamodel.query.FunctionType; > import org.eobjects.metamodel.query.OperatorType; > import org.eobjects.metamodel.query.Query; > import org.eobjects.metamodel.query.SelectItem; > import org.eobjects.metamodel.schema.Column; > import org.eobjects.metamodel.schema.ColumnType; > import org.eobjects.metamodel.schema.MutableColumn; > import org.eobjects.metamodel.schema.MutableTable; > import org.eobjects.metamodel.schema.Table; > > public class MetamodelTest { > > static class SupplyFact { > static Table table = new MutableTable("supply_fact"); > static Column dayKey = new MutableColumn("day_key", > ColumnType.BIGINT).setTable(table); > static Column adRequests = new MutableColumn("ad_requests", > ColumnType.BIGINT).setTable(table); > } > static class DateDim { > static Table dateDim = new MutableTable("date_dim"); > static Column dayKey = new MutableColumn("day_key", > ColumnType.BIGINT).setTable(dateDim); > static Column fullDate = new MutableColumn("full_date", > ColumnType.DATE).setTable(dateDim); > } > public static void main(String[] args) { > Query query = new Query(); > query.select(new > SelectItem(SupplyFact.dayKey).setAlias(SupplyFact.dayKey.getName())) > .select(new SelectItem(FunctionType.SUM, SupplyFact.adRequests)) > .from(SupplyFact.table) > .where(SupplyFact.dayKey, OperatorType.IN, new > > Query().select(DateDim.dayKey).from(DateDim.dateDim).where(DateDim.fullDate, > OperatorType.GREATER_THAN, new > Date()).getSelectClause().getSelectItem(DateDim.dayKey)) > .groupBy(SupplyFact.dayKey); > System.out.println(query.toSql()); > } > } > > > The Error I get is : > Exception in thread "main" java.lang.IllegalStateException: Could not > convert date_dim.day_key to number > at > > org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:214) > at > > org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:206) > at org.eobjects.metamodel.query.FilterItem.toSql(FilterItem.java:271) > at > > org.eobjects.metamodel.query.AbstractQueryClause.toSql(AbstractQueryClause.java:149) > at org.eobjects.metamodel.query.Query.toSql(Query.java:477) > at org.eobjects.metamodel.query.Query.toSql(Query.java:470) > at com.inmobi.metamodel.MetamodelTest.main(MetamodelTest.java:36) > > > IT seems that FilterItem converts everything to List if OperatorType.IN is > used . I think the operand type should be considered while generating query > string in toSql() method of FilterItem. > > Can this be fixed ? > > Regards > chandan >
