are you sure prod_cd and reg_id\ are emitted by respective entities in
the same name if not you may need to alias those fields (using as)
keep in mind ,the field namkes are case sensitive. Just to know what
are the values emitted use debug mode or use logTransformer
On Wed, Jun 10, 2009 at 4:55 AM, jayakeerthi s<mail2keer...@gmail.com> wrote:
> Hi All,
>
> I am facing an issue while fetching the records from database by providing
> the value" '${prod.prod_cd}' " in this type at db-data-config.xml.
> It is working fine If I provide the exact value of the product code ie
> '302437-413'
>
> Here is the db-data-config.xm I am using
>
> <dataConfig>
> <dataSource type="JdbcDataSource" driver="oracle.jdbc.driver.OracleDriver"
> url="jdbc:oracle:thin:@*********:1521:****" user="lslsls"
> password="*******"/>
>
> <document name="products">
> <entity name="prod" pk="prod_id" query="SELECT p.prod_id,
> p.prod_cd,
> ps.styl_cd,
> p.colr_disp_cd,
> p.colr_comb_desc,
> p.div_id,
> p.po_grid_desc,
> p.silo_id,
> p.silh_id,
> psa.sport_acty_desc,
> pga.gndr_age_desc,
> psh.silh_desc,
> pso.silo_desc,
> od.org_lgcy_div_cd,
> greatest (
> nvl(p.last_mod_dt,sysdate-9999),
> nvl(ps.last_mod_dt,sysdate-9999),
> nvl(od.last_mod_dt,sysdate-9999),
> nvl(psa.last_mod_dt,sysdate-9999),
> nvl(pga.last_mod_dt,sysdate-9999),
> nvl(psh.last_mod_dt,sysdate-9999),
> nvl(pso.last_mod_dt,sysdate-9999)
> ) last_mod_dt
> FROM prod p
> INNER JOIN prod_styl ps ON p.prod_styl_id = ps.prod_styl_id
> INNER JOIN org_div od ON p.div_id = od.div_id
> LEFT OUTER JOIN prod_sport_acty psa ON p.sport_acty_id =
> psa.sport_acty_id
> LEFT OUTER JOIN prod_gndr_age pga ON p.gndr_age_id = pga.gndr_age_id
> LEFT OUTER JOIN prod_silh psh ON p.silh_id = psh.silh_id
> LEFT OUTER JOIN prod_silo pso ON p.silo_id = pso.silo_id
> WHERE nvl(od.stat,'A') = 'A'
> AND nvl(psa.stat,'A') = 'A'
> AND nvl(pga.stat,'A') = 'A'
> AND nvl(psh.stat,'A') = 'A'
> AND nvl(pso.stat,'A') = 'A'">
> AND p.prod_cd = '302437-413'">
> <field column="prod_id" name="prod_id"/>
> <field column="prod_cd" name="prod_cd"/>
> <field column="styl_cd" name="styl_cd"/>
> <field column="colr_disp_cd" name="colr_disp_cd"/>
> <field column="colr_comb_desc" name="colr_comb_desc"/>
> <field column="div_id" name="div_id"/>
> <field column="po_grid_desc" name="po_grid_desc"/>
> <field column="silo_id" name="silo_id"/>
> <field column="sport_acty_desc" name="sport_acty_desc"/>
> <field column="silh_id" name="silh_id"/>
> <field column="gndr_age_desc" name="gndr_age_desc"/>
> <field column="silh_desc" name="silh_desc"/>
> <field column="silo_desc" name="silo_desc"/>
> <field column="org_lgcy_div_cd" name="org_lgcy_div_cd"/>
>
>
> <entity name="prod_reg" query="SELECT pr.prod_id,
> pr.prod_cd,
> pr.reg_id,
> pr.retl_pr_amt,
> pr.whsle_pr_amt,
> pr.retl_crcy_id,
> pr.whsle_crcy_id,
> pr.frst_prod_offr_dt,
> pr.end_ftr_offr_dt,
> pr.last_mod_dt last_mod_dt
> FROM prod_reg pr
> WHERE prod_cd =* '${prod.prod_cd}' ">
> *
>
> <field column="retl_pr_amt" name="retl_pr_amt"/>
> <field column="whsle_pr_amt" name="whsle_pr_amt"/>
> <field column="retl_crcy_id" name="retl_crcy_id"/>
> <field column="whsle_crcy_id" name="whsle_crcy_id"/>
> <field column="frst_prod_offr_dt"
> name="frst_prod_offr_dt"/>
> <field column="end_ftr_offr_dt" name="end_ftr_offr_dt"/>
>
> <field column="last_mod_dt" name="last_mod_dt"/>
>
>
> <entity name="prod_reg_cmrc_styl" query="SELECT p.prod_id,
> p.prod_cd,
> pr.reg_id,
> prcs.sap_lang_id,
> prcs.reg_cmrc_styl_nm,
> prcs.insm_desc,
> prcs.otsm_desc,
> prcs.dim_desc,
> prcs.prfl_desc,
> prcs.upr_desc,
> prcs.mdsl_desc,
> prcs.outsl_desc,
> prcs.ctnt_desc,
> prcs.size_run_desc,
> greatest (
> nvl(p.last_mod_dt,sysdate-9999),
> nvl(ps.last_mod_dt,sysdate-9999),
> nvl(pr.last_mod_dt,sysdate-9999),
> nvl(prcs.last_mod_dt,sysdate-9999)
> ) last_mod_dt
> FROM prod p
> INNER JOIN prod_styl ps ON p.prod_styl_id = ps.prod_styl_id
> INNER JOIN prod_reg pr ON p.prod_id = pr.prod_id
> INNER JOIN prod_reg_cmrc_styl prcs ON prcs.prod_styl_id = ps.prod_styl_id
> AND prcs.reg_id = pr.reg_id
> WHERE prcs.stat_cd = 'A'
> *AND prod_cd ='${prod.prod_cd}'
> AND reg_id = '${prod_reg.reg_id'">
> *
> <field column="sap_lang_id" name="sap_lang_id"/>
> <field column="reg_cmrc_styl_nm"
> name="reg_cmrc_styl_nm"/>
> <field column="insm_desc" name="insm_desc"/>
> <field column="otsm_desc" name="otsm_desc"/>
>
> <field column="dim_desc" name="dim_desc"/>
> <field column="prfl_desc" name="prfl_desc"/>
> <field column="upr_desc" name="upr_desc"/>
>
> <field column="mdsl_desc" name="mdsl_desc"/>
> <field column="outsl_desc" name="outsl_desc"/>
> <field column="ctnt_desc" name="ctnt_desc"/>
> <field column="size_run_desc" name="size_run_desc"/>
>
>
> </entity>
> </entity>
> </entity>
> </document>
> </dataConfig>
>
>
> The issue is IF I replace the *AND prod_cd ='${prod.prod_cd}' AND reg_id =
> '${prod_reg.reg_id'">* with the exact value '302437-413' I am getting the
> result If not it is not
> executing the prod_reg and prod_reg_cmrc_styl entity.
>
> Please advise anything I am missing in the above db-data-config.xml.
>
> Thanks in advance.
>
> Regards,
> Jayakeerthi
>
--
-----------------------------------------------------
Noble Paul | Principal Engineer| AOL | http://aol.com
