Community Tip - When posting, your subject should be specific and summarize your question. Here are some additional tips on asking a great question. X
Need help in converting below SQL query into queryspec
select * from wind.WTChangeOrder2 ch left outer join wind.WTChangeOrder2Master chm
on ch.idA3masterReference = chm.idA2A2
left outer join wind.ObjectHistory obj on obj.idA3A5 = ch.idA2A2
left outer join wind.LifeCycleHistory lch on lch.idA2A2=obj.idA3B5
where chm.WTCHGORDERNUMBER = '00039'
and lch.action='Set_State';
This should do the job.
Not tested 🙂
import wt.change2.WTChangeOrder2; import wt.change2.WTChangeOrder2Master; import wt.fc.PersistenceHelper; import wt.fc.QueryResult; import wt.lifecycle.LifeCycleHistory; import wt.lifecycle.ObjectHistory; import wt.query.QueryException; import wt.query.QuerySpec; import wt.query.SearchCondition; import wt.util.WTAttributeNameIfc; import wt.util.WTException; /** * $Rev:: $: Version * $Author:: $: Dernier modificateur * $Date:: $: Date du dernier commit * Url de la version la plus recente * $HeadURL$ **/ public class MyQuery { public static void main(String[] args) { /* select * from wind.WTChangeOrder2 ch left outer join wind.WTChangeOrder2Master chm on ch.idA3masterReference = chm.idA2A2 left outer join wind.ObjectHistory obj on obj.idA3A5 = ch.idA2A2 left outer join wind.LifeCycleHistory lch on lch.idA2A2=obj.idA3B5 where chm.WTCHGORDERNUMBER = '00039' and lch.action='Set_State'; */ try { QuerySpec querySpec = new QuerySpec(); int indChOrder = querySpec.appendClassList(WTChangeOrder2.class,true); int indChOrderMaster = querySpec.appendClassList(WTChangeOrder2Master.class,true); int indObjHistory = querySpec.appendClassList(ObjectHistory.class,false); int indLcHistory = querySpec.appendClassList(LifeCycleHistory.class,false); // Joins querySpec.appendWhere(new SearchCondition(WTChangeOrder2.class, "masterReference.key.id",WTChangeOrder2Master.class, WTAttributeNameIfc.ID_NAME),new int[]{indChOrderMaster,indChOrder}); querySpec.appendAnd(); querySpec.appendWhere(new SearchCondition(ObjectHistory.class, "roleAObjectRef.key.id",WTChangeOrder2.class, WTAttributeNameIfc.ID_NAME),new int[]{indObjHistory,indChOrder}); querySpec.appendAnd(); querySpec.appendWhere(new SearchCondition(LifeCycleHistory.class, WTAttributeNameIfc.ID_NAME,ObjectHistory.class, "roleBObjectRef.key.id"),new int[]{indLcHistory,indObjHistory}); // Select querySpec.appendAnd(); querySpec.appendWhere(new SearchCondition(WTChangeOrder2Master.class, WTChangeOrder2Master.NUMBER, SearchCondition.EQUAL, "00039"),new int[]{indChOrderMaster}); querySpec.appendAnd(); querySpec.appendWhere(new SearchCondition(LifeCycleHistory.class, LifeCycleHistory.ACTION, SearchCondition.EQUAL, "Set_State"),new int[]{indChOrderMaster}); QueryResult queryResult = PersistenceHelper.manager.find(querySpec); // use queryResult ... } catch (QueryException e) { e.printStackTrace(); } catch (WTException e) { e.printStackTrace(); } } }