Office 2010 query crashes

Question

Office 2010 causes MSQuery to crash when running a big query like this:

SELECT TRANSACTION_DETAIL.ACCT_ID, TRANSACTION_DETAIL.ACCT_DESC, 
       TRANSACTION_DETAIL.ACT_COST, TRANSACTION_DETAIL.EMPL_ID, 
       TRANSACTION_DETAIL.EMPL_NAME, TRANSACTION_DETAIL.FISCAL_QTR, 
       TRANSACTION_DETAIL.FY_CD, TRANSACTION_DETAIL.NOTES, 
       TRANSACTION_DETAIL.OBS_ORG_NAME, TRANSACTION_DETAIL.OWNING_ORG_ID, 
       TRANSACTION_DETAIL.PD_NO, TRANSACTION_DETAIL.TRANSACTION_TYPE, 
       TRANSACTION_DETAIL.TRANS_DATE, TRANSACTION_DETAIL.TRN_DESC
FROM OPS$CPDMUSER.TRANSACTION_DETAIL TRANSACTION_DETAIL
WHERE (TRANSACTION_DETAIL.ACCT_ID like '5%') 
  AND (TRANSACTION_DETAIL.FY_CD='2011')

It was working fine in MS 2007.

This runs fine in database and i need to use like as i want all the account id's that starts with 5.

It is pulling data from oracle database and we are using Oracle 10 g

I am using oracle 10G ODBC driver

Answer 1

I would try wrapping your query into a sub query. There are few quirks when using MSQuery with Oracle which I think arise from MSQuery trying to show the query in the query editor. Wrapping in a sub query forces MSQuery to give up trying to display the editor and just use the SQL as is. Your query would then become:

SELECT * FROM (
SELECT TRANSACTION_DETAIL.ACCT_ID, TRANSACTION_DETAIL.ACCT_DESC, 
       TRANSACTION_DETAIL.ACT_COST, TRANSACTION_DETAIL.EMPL_ID, 
       TRANSACTION_DETAIL.EMPL_NAME, TRANSACTION_DETAIL.FISCAL_QTR, 
       TRANSACTION_DETAIL.FY_CD, TRANSACTION_DETAIL.NOTES, 
       TRANSACTION_DETAIL.OBS_ORG_NAME, TRANSACTION_DETAIL.OWNING_ORG_ID, 
       TRANSACTION_DETAIL.PD_NO, TRANSACTION_DETAIL.TRANSACTION_TYPE, 
       TRANSACTION_DETAIL.TRANS_DATE, TRANSACTION_DETAIL.TRN_DESC
FROM OPS$CPDMUSER.TRANSACTION_DETAIL TRANSACTION_DETAIL
WHERE (TRANSACTION_DETAIL.ACCT_ID like '5%') 
  AND (TRANSACTION_DETAIL.FY_CD='2011')
)