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

How big is it? How many rows returned? Have you run it in Oracle, because it does not look quite right here `FROM OPS$CPDMUSER.TRANSACTION_DETAIL TRANSACTION_DETAIL`. Can you avoid LIKE? — Fionnuala, Feb 25 '11 at 20:53
How are you connecting to the database - ODBC? If so, which ODBC driver are you using? Have you tried a different driver? — Jeffrey Kemp, Feb 27 '11 at 06:50

score 0 · Answer 1 · answered Dec 04 '12 at 10:26

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')
)

Office 2010 query crashes

1 Answers1