You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Prashant Punekar <Pr...@mindtree.com> on 2010/12/14 20:16:02 UTC
Sub queries using entity engine !
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
Re: Sub queries using entity engine !
Posted by David E Jones <de...@me.com>.
Sorry, it's "EntityWhereString", not "EntityWhereClause".
-David
On Dec 15, 2010, at 3:49 PM, David E Jones wrote:
>
> 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:bjfree@free-man.net]
>> Sent: Tuesday, December 14, 2010 11:25 AM
>> To: user@ofbiz.apache.org
>> 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
>>>
>>
>
Re: Sub queries using entity engine !
Posted by David E Jones <de...@me.com>.
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:bjfree@free-man.net]
> Sent: Tuesday, December 14, 2010 11:25 AM
> To: user@ofbiz.apache.org
> 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
>>
>
RE: Sub queries using entity engine !
Posted by Prashant Punekar <Pr...@mindtree.com>.
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:bjfree@free-man.net]
Sent: Tuesday, December 14, 2010 11:25 AM
To: user@ofbiz.apache.org
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
>
Re: Sub queries using entity engine !
Posted by BJ Freeman <bj...@free-man.net>.
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
>