You can do a nested query using the EntityWhereClause condition, just mix it in with other conditions you need as part of your condition tree. It won't be part of the entity definition, but rather the code that does the query.
-David On Dec 15, 2010, at 1:55 PM, Prashant Punekar wrote: > Freeman, > > This query is not generated. I want to generate this query using > DynamicViewEntity object. > Is it possible to use the sub-query as the table alias and then do left outer > join on it? > > Regards, > Prashant > > > -----Original Message----- > From: BJ Freeman [mailto:[email protected]] > Sent: Tuesday, December 14, 2010 11:25 AM > To: [email protected] > Subject: Re: Sub queries using entity engine ! > > what was the entity or entity view that created this sql. > > ========================= > BJ Freeman > Strategic Power Office with Supplier Automation > <http://www.businessesnetwork.com/automation/viewforum.php?f=52> > Specialtymarket.com <http://www.specialtymarket.com/> > Systems Integrator-- Glad to Assist > > Chat Y! messenger: bjfr33man > > > Prashant Punekar sent the following on 12/14/2010 11:16 AM: >> Hi All, >> >> Can we write a sub query using entity engine ? >> Here is the query which is to be generated using entity engine. >> >> Thanks in advance. >> >> Regards, >> Prashant >> >> >> SELECT >> pc.category_name >> ,COUNT(DISTINCT p.product_id) >> >> FROM product_category_member pcm INNER JOIN >> product_category pc >> ON pc.product_category_id = pcm.product_category_id LEFT OUTER JOIN >> ( >> SELECT p.product_id >> FROM product p INNER JOIN >> inventory_item ii >> ON p.product_id = ii.product_id >> >> GROUP BY p.product_id >> HAVING SUM(ii.AVAILABLE_TO_PROMISE_TOTAL)> 0 /***no inventory**/) AS p >> >> ON pcm.product_id = p.product_id >> GROUP BY pc.category_name >> >> ________________________________ >> >> http://www.mindtree.com/email/disclaimer.html >> >
