Hi Awdesh,
Your reply is correct for the given query.
I can move the condition (AND price BETWEEN 15 AND 20) to where clause but I
have other queries where I need the condition to be part of
INNER JOIN ON (condtion). In that case how do I do it?
Here is the main query to be generated with dynamic entity view:
SELECT pcm.product_id,
PRICE_FLAT_AMOUNT.amount PRICE_FLAT_AMOUNT,
PRICE_FOL_AMOUNT.amount PRICE_FOL_AMOUNT,
PRICE_PFLAT_AMOUNT.amount PRICE_PFLAT_AMOUNT,
PRICE_POAC_AMOUNT.amount PRICE_POAC_AMOUNT,
PRICE_POD_AMOUNT.amount PRICE_POD_AMOUNT,
PRICE_POL_AMOUNT.amount PRICE_POL_AMOUNT,
PRICE_POM_AMOUNT.amount PRICE_POM_AMOUNT,
PRICE_WFLAT_AMOUNT.amount PRICE_WFLAT_AMOUNT
FROM product_category_member pcm
INNER JOIN product_price_cond ppc ON (
pcm.product_id = ppc.cond_value AND ppc.input_param_enum_id = 'PRIP_PRODUCT_ID')
INNER JOIN product_price_action PRICE_FLAT_AMOUNT ON (
PRICE_FLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_FLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_FLAT' AND
PRICE_FLAT_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_FOL_AMOUNT ON (
PRICE_FOL_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_FOL_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_FOL' AND
PRICE_FOL_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_PFLAT_AMOUNT ON (
PRICE_PFLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_PFLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_PFLAT' AND
PRICE_PFLAT_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POAC_AMOUNT ON (
PRICE_POAC_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POAC_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POAC' AND
PRICE_POAC_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POD_AMOUNT ON (
PRICE_POD_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POD_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POD' AND
PRICE_POD_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POL_AMOUNT ON (
PRICE_POL_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POL_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POL' AND
PRICE_POL_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POM_AMOUNT ON (
PRICE_POM_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POM_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POM' AND
PRICE_POM_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_WFLAT_AMOUNT ON (
PRICE_WFLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_WFLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_WFLAT' AND
PRICE_WFLAT_AMOUNT.amount BETWEEN 15 AND 20)
WHERE pcm.product_category_id IN
('100','101','102','201','20111','202','CATALOG1_QUICKADD1','CATALOG1_QUICKADD2')
The above between condition can also be re-written as:
PRICE_POM_AMOUNT.amount >= 15 AND PRICE_POM_AMOUNT.amount <= 20
Request you to help me in creating the above query using DVE.
Thanks in advance!
Regards,
Prashant
-----Original Message-----
From: Awdesh Parihar [mailto:[email protected]]
Sent: Friday, August 28, 2009 3:27 PM
To: [email protected]
Subject: Re: FW: Creating DynamicViewEntity !
Hello Prashant ,
You can refer following code to create dynamic view entity .
DynamicViewEntity dynamicView = new DynamicViewEntity();
dynamicView.addMemberEntity("PCM", "ProductCategoryMember");
dynamicView.addAliasAll("PCM", "")
dynamicView.addMemberEntity("PP", 'ProductPrice');
dynamicView.addAliasAll("PP" , "")
dynamicView.addViewLink("PCM", "PP", Boolean.TRUE,
ModelKeyMap.makeKeyMapList("productId"));
>
>
> pcm.product_id = pp.product_id AND price BETWEEN 15 AND 20)
> WHERE pcm.product_category_id IN ('100', '101')
For this put condition on dynamic view entity (for reference prefer
findParty service)
--
Awdesh Parihar
http://www.mindtree.com/email/disclaimer.html
