I have some Excel 2010 workbooks that connect to an Oracle database. Data is returned via Microsoft Query. The query contains some aliases. The data continues to be returned to the spreadsheet with no errors on update. However, if I try to edit the query, I get the following error message: ORA-923: FROM keyword not found where expected. I've discovered that if I delete the alias, I do not get the error.
At first I thought it had to do with upgrading to a new computer (Windows 7 64bit), but I've tested on older computers in the office and I get the same error.
The code follows:
SELECT AR_TRX_HEADER.SONO, AR_TRX_HEADER.INVOICEDATE, GA_BLOCK.ID, Sum(AR_TRX_PRODUCT.QNT) AS "SHIP", AR_TRX_CHARGE_VIEW.QNT AS "SOLD", Sum(AR_TRX_HEADER_AUX.QNT) AS "COUNT", Sum(AR_TRX_PRODUCT.QNT*AR_TRX_DETAIL.COSTPRICE) AS "REVENUE"
FROM COMPANY_1.AR_TRX_CHARGE_VIEW AR_TRX_CHARGE_VIEW, COMPANY_1.AR_TRX_DETAIL AR_TRX_DETAIL, COMPANY_1.AR_TRX_HEADER AR_TRX_HEADER, COMPANY_1.AR_TRX_HEADER_AUX AR_TRX_HEADER_AUX, COMPANY_1.AR_TRX_PRODUCT AR_TRX_PRODUCT, COMPANY_1.GA_BLOCK GA_BLOCK
WHERE AR_TRX_PRODUCT.ARTRXHDRIDX = AR_TRX_HEADER.ARTRXHDRIDX AND AR_TRX_DETAIL.ARTRXHDRIDX = AR_TRX_PRODUCT.ARTRXHDRIDX AND AR_TRX_DETAIL.ARTRXDTLSEQ = AR_TRX_PRODUCT.ARTRXDTLSEQ AND AR_TRX_HEADER_AUX.ARTRXHDRIDX = AR_TRX_PRODUCT.ARTRXHDRIDX AND AR_TRX_HEADER_AUX.ARTRXDTLSEQ = AR_TRX_DETAIL.ORIGINALSEQ AND GA_BLOCK.GABLOCKIDX = AR_TRX_PRODUCT.GABLOCKIDX AND AR_TRX_CHARGE_VIEW.ARTRXHDRIDX = AR_TRX_PRODUCT.ARTRXHDRIDX
GROUP BY AR_TRX_HEADER.SONO, AR_TRX_HEADER.INVOICEDATE, GA_BLOCK.ID, AR_TRX_CHARGE_VIEW.QNT, AR_TRX_DETAIL.GLDELETECODE, AR_TRX_HEADER_AUX.TAB, AR_TRX_HEADER.CUSTNAMEIDX, AR_TRX_CHARGE_VIEW.CHARGEMETHODTYPE, AR_TRX_CHARGE_VIEW.FCCHARGEIDX, AR_TRX_CHARGE_VIEW.LINENO, AR_TRX_PRODUCT.GABLOCKIDX
HAVING (AR_TRX_HEADER.INVOICEDATE>{ts '2012-01-01 00:00:00'} And AR_TRX_HEADER.INVOICEDATE<={ts '2012-12-31 00:00:00'}) AND (AR_TRX_DETAIL.GLDELETECODE='N') AND (AR_TRX_HEADER_AUX.TAB=2) AND (AR_TRX_HEADER.CUSTNAMEIDX=9) AND (AR_TRX_CHARGE_VIEW.CHARGEMETHODTYPE='1') AND (AR_TRX_CHARGE_VIEW.FCCHARGEIDX=17) AND (AR_TRX_CHARGE_VIEW.LINENO=1) AND (AR_TRX_PRODUCT.GABLOCKIDX<>128 And AR_TRX_PRODUCT.GABLOCKIDX<>127 And AR_TRX_PRODUCT.GABLOCKIDX<>135)
ORDER BY AR_TRX_HEADER.INVOICEDATE
1. HKEY_CURRENT_USER\Software
2. After you select the subkey that is specified in step 1, point to New on the Edit menu, and then click DWORD Value.
3. Type AllowAlias, and then press ENTER.
4. Right-click AllowAlias, and then click Modify.
5. In the Value data box, type 2, and then click OK.
6. On the File menu, click Exit to exit Registry Editor.