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 >
