Automate Passthru queries....

[harshakusam]

Hi all,

I use to run daily 110 passthru queries...

My work is ...

I ahve to run each query and output is saved into excel and save it..

This almost takes 2 1/2 hrs..

So i written a VB code that will work for normal queries...My code will excute and save output in excel in specified location...

But iam unable to handle the passthru queries Y* because these quries contains case statements.. as ms access does't support Case and soo...

Can someone pls let me know how to handle below queries plsssss..
----------------------------
Sample query...

select d.DEAL_FOLDER_STATUS, d.VALUE_DATE, d.BUSINESS_DATE,
case
when d.BUY_SETL_TYPE_IND = 11
then 'NET PEND'
when d.BUY_SETL_TYPE_IND = 12
then 'NETTED '
when d.buy_setL_type_ind = 15
then 'NET AS GROSS'
when d.buy_setl_type_ind = 19
then ' NET GO GROSS'
when d.BUY_SETL_TYPE_IND = 21
then 'GROSS PEND'
when d.BUY_SETL_TYPE_IND = 26
then 'GROSS AGGR'
when d.BUY_SETL_TYPE_IND = 22
then 'GROSS AD HOC'
when d.BUY_SETL_TYPE_IND = 29
then 'GROSS '
when d.BUY_SETL_TYPE_IND > 40
then 'CLS '
ELSE cast(d.buy_setl_type_ind as char(2))
end as setl_type
,d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type
,d.BUY_CCY_ID, d.BUY_AMOUNT, d.SELL_CCY_ID, d.SELL_AMOUNT, d.DEAL_RATE, tm.acct_ccy_equiv_amt,tm.TRADE_SOURCE_ID
, case
when tm.trade_source_id = 'RMS'
then (substr(tm.fo_deal_id,5,10))
else tm.fo_deal_id
end as fo_trade_id
,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind
, d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id
from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm
where
cle.company_id = c.company_id
and cle.cpty_id = c.cpty_id
and d.company_id = cle.company_id
and d.legal_entity_id = cle.legal_entity_id
and d.cpty_id = cle.cpty_id
and c.record_state = 'V'
and cle.cpty_id LIKE 'ABSA JB%'
and cle.record_state = 'V'
and d.record_state = 'V'
and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8))
and s.deal_folder_id = d.deal_folder_id
and s.ccy_id = d.buy_ccy_id
and tm.trade_id = d.trade_id
and tm.trans_id = d.trade_trans_id
and tm.ver_id = d.trade_ver_id
order by d.value_date , SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID , d.CPTY_ID

 

[harshakusam]

Hi all,

I had already written VBcode.... my code works fine for normal queries.. but all my queries using case, substr, and sooo.. so if you see access won't understand case, substr.. if you want access to run such queries then you have to make them as passthru queries.. then the query will work fine.. if i want same query to run thru VB it's throwing error (sytax error) .. so my problem is how to handle such queries in VB...

Below is the one sample query i provided for you ref ....


Sample query
------------

select d.DEAL_FOLDER_STATUS, d.VALUE_DATE, d.BUSINESS_DATE,
case
when d.BUY_SETL_TYPE_IND = 11
then 'NET PEND'
when d.BUY_SETL_TYPE_IND = 12
then 'NETTED '
when d.buy_setL_type_ind = 15
then 'NET AS GROSS'
when d.buy_setl_type_ind = 19
then ' NET GO GROSS'
when d.BUY_SETL_TYPE_IND = 21
then 'GROSS PEND'
when d.BUY_SETL_TYPE_IND = 26
then 'GROSS AGGR'
when d.BUY_SETL_TYPE_IND = 22
then 'GROSS AD HOC'
when d.BUY_SETL_TYPE_IND = 29
then 'GROSS '
when d.BUY_SETL_TYPE_IND > 40
then 'CLS '
ELSE cast(d.buy_setl_type_ind as char(2))
end as setl_type
,d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type
,d.BUY_CCY_ID, d.BUY_AMOUNT, d.SELL_CCY_ID, d.SELL_AMOUNT, d.DEAL_RATE, tm.acct_ccy_equiv_amt,tm.TRADE_SOURCE_ID
, case
when tm.trade_source_id = 'RMS'
then (substr(tm.fo_deal_id,5,10))
else tm.fo_deal_id
end as fo_trade_id
,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind
, d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id
from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm
where
cle.company_id = c.company_id
and cle.cpty_id = c.cpty_id
and d.company_id = cle.company_id
and d.legal_entity_id = cle.legal_entity_id
and d.cpty_id = cle.cpty_id
and c.record_state = 'V'
and cle.cpty_id LIKE 'ABSA JB%'
and cle.record_state = 'V'
and d.record_state = 'V'
and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8))
and s.deal_folder_id = d.deal_folder_id
and s.ccy_id = d.buy_ccy_id
and tm.trade_id = d.trade_id
and tm.trans_id = d.trade_trans_id
and tm.ver_id = d.trade_ver_id
order by d.value_date , SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID , d.CPTY_ID

 

[LPurvis]

Why can't you just execute the passthrough queries?