Multiple selects in an Access Passthru query

[jonomac]

Hi,

I'm trying to run a pass thru query into a sybase server from Access. The query contains multiple selects which Access doesn't appear to like very much. The query works fine when run in Sybase.

Is there any way of circumventing this problem?

I could split the three selects into three seperate queries to view the individual results but the third set is dependant on the previous two sets and so therefore is difficult (impossible?) to seperate out as a third individual query.

Thanks for any suggestions?

Select 
s.id_prtf    Portfolio_ID,
s.id_imnt   Instrument_ID, 
s.id_imnt_swap   Instrument_Swap_ID,
s.id_ccy_std   Currency, 
s.am_posn  Position, 
s.am_pnl_eqty   PNL_Eqty

into #temptable1
from 
SET_VALUATION s, INSTRUMENT i
where
s.id_imnt = i.id_imnt
and s.id_posn_ref is NULL
and s.id_imnt_swap is NULL
and s.am_pnl_eqty > 0
and i.id_typ_imnt != 'SFX'

Select 
s.id_prtf  Portfolio_ID, 
s.id_imnt   Instrument_ID, 
s.id_imnt_swap   Instrument_Swap_ID,
s.id_ccy_std   Currency, 
s.am_posn  Position, 
s.pr_imnt_mtm_prev,
sy.pr_imnt_mtm,
s.am_mv_prev,
sy.am_mv

into #temptable1a
from SET_VALUATION s, SET_VALUATION_HIST sy, REFERENCE_DATA r
where
s.id_prtf = sy.id_prtf
and s.id_imnt = sy.id_imnt
and s.pr_imnt_mtm_prev != sy.pr_imnt_mtm
and s.am_mv_prev != sy.am_mv
and r.dt_bus_lst = sy.dt_bus

select 
t.*,
tt.pr_imnt_mtm_prev,
tt.pr_imnt_mtm,
tt.am_mv_prev,
tt.am_mv

into #newtemp
from #temptable1 t, #temptable1a tt
where
t.Portfolio_ID = tt.Portfolio_ID
 
delete from #temptable1
where #temptable1.Portfolio_ID in (select Portfolio_ID from #newtemp)
delete from #temptable1a
where #temptable1a.Portfolio_ID in (select Portfolio_ID from #newtemp)

select "Where id_posn_ref is NULL and id_imnt_swap is NULL is the only criteria"
select * from #temptable1
select "Where SET VAL's pr_imnt_mtm_prev does not equal  SET VAL HIST's pr_imnt_mtm, is the only criteria"
select * from #temptable1a
select "Where the output satisfies both Where id_posn_ref is NULL and id_imnt_swap is NULL  AND Where SET VAL's pr_imnt_mtm_prev does not equal  SET VAL HIST's pr_imnt_mtm"
select * from #newtemp

drop table #temptable1
drop table #temptable1a
drop table #newtemp

 

[Pat Hartman]

Try creating a stored procedure and running that. Alternatively, you can create Access queries since Access can nest queries. Create the first two queries and save them. Then the third query can use them rather than temp tables.

 

[jonomac]

Hi Pat,

Hadn't seen your reply until now, apologies!

Yep, the the two pass thru queries with a normal Access Select query on top worked fine when I figured it out!

Thanks, jono