Topic
1 reply Latest Post - ‏2010-05-22T13:30:11Z by SystemAdmin
maheshk.online
maheshk.online
1 Post
ACCEPTED ANSWER

Pinned topic bind variables in XML

‏2010-05-10T07:56:35Z |
is there any way to use bind variables for the select query embedded in XML tags?my shared pull was overflowed with same select statements last night !

<Query>
SELECT nvl(SUM(apportion_amt),0),ca_ss_store.st_code,ca_ss_store.COST_CENTER, trunc(a.tran_date),ca_ss_store.st_code FROM st_lo_trans A, ca_ss_store,
st_lo_apportions,st_lo_master slm WHERE ca_ss_store.st_code = A.st_code AND st_lo_apportions.loan_tran_code = A.loan_tran_code AND slm.loan_code =
A.loan_code AND trunc(tran_date) BETWEEN TO_DATE(?,'DD/MM/YYYY') and TO_DATE(?,'DD/MM/YYYY') AND A.st_code = ? AND
A.ST_CODE=CA_SS_STORE.ST_CODE and tran_id in('ADV') AND apportion_id = 'F' AND (select count(*) from st_lo_master slm1 where slm1.bo_code =
slm.bo_code and loan_date < a.tran_date) > 0 AND A.VOID_ID = 'N' AND A.created_by>;0 GROUP BY CA_SS_STORE.ST_CODE,ca_ss_store.COST_CENTER,trunc
(a.tran_date) order by trunc(a.tran_date)
</Query>
Thanks,
Mahesh.
Updated on 2010-05-22T13:30:11Z at 2010-05-22T13:30:11Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1391 Posts
    ACCEPTED ANSWER

    Re: bind variables in XML

    ‏2010-05-22T13:30:11Z  in response to maheshk.online
    Have to at least hurdle the obstacle of ill-formedness? There are bare > and < inside the embedded query which are not acceptable. Can replce them by &gt; and &lt; or simply wrapped the query as a CDATA section before proceeding further?
    
    <Query><![CDATA[ SELECT nvl(SUM(apportion_amt),0),ca_ss_store.st_code,ca_ss_store.COST_CENTER, trunc(a.tran_date),ca_ss_store.st_code FROM st_lo_trans A, ca_ss_store, st_lo_apportions,st_lo_master slm WHERE ca_ss_store.st_code = A.st_code AND st_lo_apportions.loan_tran_code = A.loan_tran_code AND slm.loan_code = A.loan_code AND trunc(tran_date) BETWEEN TO_DATE(?,
    'DD/MM/YYYY') and TO_DATE(?,
    'DD/MM/YYYY') AND A.st_code = ? AND A.ST_CODE=CA_SS_STORE.ST_CODE and tran_id in(
    'ADV') AND apportion_id = 
    'F' AND (select count(*) from st_lo_master slm1 where slm1.bo_code = slm.bo_code and loan_date < a.tran_date) > 0 AND A.VOID_ID = 
    'N' AND A.created_by>;0 GROUP BY CA_SS_STORE.ST_CODE,ca_ss_store.COST_CENTER,trunc (a.tran_date) order by trunc(a.tran_date) ]]></Query>