Hi All, Currently the Dynamic Entity View (DVE) doesn't help me in creating the query listed below because of the query condition (AND condition) in the JOINS.
For this requirement do you suggest me to change the current Entity Engine's DVE implementation to take care of this? Or is there any other better approach to be taken to resolve this issue. Regards, Prashant -----Original Message----- From: Prashant Punekar Sent: Friday, August 28, 2009 3:39 PM To: '[email protected]' Subject: RE: FW: Creating DynamicViewEntity ! 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
