We help IT Professionals succeed at work.

SQL Passthrough Query Error: ORA-00936: Missing Expression (#936)

jn1480
jn1480 used Ask the Experts™
on
Hey guys -

I wrote a relatively simple query in MSAccess 2010 against an ODBC linked Oracle table.
It was taking a bit long to run, so I opted to convert it to a SQL Pass Through query, and run it that way to gain the advantage of it running on the server as opposed to my desktop.

However, while the query did run as a standard Make Table query in Access, it now errors out as SQL Pass Through query in Access.

The SQL is in the code below.
The error is as follows:
=========
ODBC--call failed.

[Oracle][ODBC][Ora]ORA-00396: missing expression
(#936)

OK    Help
=========

I tried running it with single quotes instead of # for the dates, and without a WHERE statement at all, but same error…
Any thoughts?

SELECT XBRADMIN_POS_SKU_TAB.STORENUM AS Store, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY, XBRADMIN_POS_SKU_TAB.LINENUM, Int([CASHIERNUM]) AS SalesID, XBRADMIN_POS_SKU_TAB.ITEM, XBRADMIN_POS_SKU_TAB.MERCH_DEPT, XBRADMIN_POS_SKU_TAB.MERCH_CLASS INTO 2010T_POSSkuTab
FROM XBRADMIN_POS_SKU_TAB
WHERE (((XBRADMIN_POS_SKU_TAB.STORENUM)<3000) AND ((XBRADMIN_POS_SKU_TAB.TRANSDATE) Between #7/31/2011# And #8/6/2011#) AND ((XBRADMIN_POS_SKU_TAB.ORGID)=1) AND ((XBRADMIN_POS_SKU_TAB.DIVISION)=1) AND ((XBRADMIN_POS_SKU_TAB.TRANSSTAT)="COMPLETE") AND ((XBRADMIN_POS_SKU_TAB.TRANSTYPE)<>"RETURN") AND ((XBRADMIN_POS_SKU_TAB.TRAINING_FLAG)="N") AND ((XBRADMIN_POS_SKU_TAB.VOID_CODE)=0))
ORDER BY XBRADMIN_POS_SKU_TAB.STORENUM, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infrastructure and Database Design Consultant
Commented:
SELECT ... INTO returns one row into memory variables.
Is this what you were planning?
You probably want to:

CREATE OR UPDATE TABLE 2010T_POSSkuTab
AS 
SELECT XBRADMIN_POS_SKU_TAB.STORENUM AS Store, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY, XBRADMIN_POS_SKU_TAB.LINENUM, Int([CASHIERNUM]) AS SalesID, XBRADMIN_POS_SKU_TAB.ITEM, XBRADMIN_POS_SKU_TAB.MERCH_DEPT, XBRADMIN_POS_SKU_TAB.MERCH_CLASS 
FROM XBRADMIN_POS_SKU_TAB
WHERE (((XBRADMIN_POS_SKU_TAB.STORENUM)<3000) AND ((XBRADMIN_POS_SKU_TAB.TRANSDATE) Between #7/31/2011# And #8/6/2011#) AND ((XBRADMIN_POS_SKU_TAB.ORGID)=1) AND ((XBRADMIN_POS_SKU_TAB.DIVISION)=1) AND ((XBRADMIN_POS_SKU_TAB.TRANSSTAT)="COMPLETE") AND ((XBRADMIN_POS_SKU_TAB.TRANSTYPE)<>"RETURN") AND ((XBRADMIN_POS_SKU_TAB.TRAINING_FLAG)="N") AND ((XBRADMIN_POS_SKU_TAB.VOID_CODE)=0))
ORDER BY XBRADMIN_POS_SKU_TAB.STORENUM, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY;

Open in new window


Look up the appropriate Oracle syntax for make-table queries, and also check out the following links.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm

http://tahiti.oracle.com/


Cheers!
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
The " Int([CASHIERNUM]) " also probably needs changing. Look at "TO_NUMBER()"
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I'm not an Oracle guy, but SQL Server uses a different date format than Access (at least it used to) and would not accept dates parameters formatted as #7/31/2012#.  It has been a while, but I believe the Oracle syntax requires that you replace the # with a '

Author

Commented:
Thanks guys!
I've got it down to this, and am at least getting a different error now:
=========
ODBC--call failed.

[Oracle][ODBC][Ora]ORA-00903: invalid table name
(#903)

OK     Help
=========

CREATE TABLE 2010T_POSSkuTab
AS 
SELECT STORENUM AS Store, TRANSDATE, TRANSNUM, REGNUM, QUANTITY, LINENUM, CASHIERNUM AS SalesID, ITEM, MERCH_DEPT, MERCH_CLASS 
FROM XBRADMIN.POS_SKU_TAB
WHERE (((STORENUM)<3000) AND ((TRANSDATE) Between '7/31/2011' And '8/6/2011') AND ((ORGID)=1) AND ((DIVISION)=1) AND ((TRANSSTAT)="COMPLETE") AND ((TRANSTYPE)<>"RETURN") AND ((TRAINING_FLAG)="N") AND ((VOID_CODE)=0))
ORDER BY STORENUM, TRANSDATE, TRANSNUM, REGNUM, QUANTITY;

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Again, I'm not an Oracle guy, but if you are just trying to return a set of records, try dropping the

CREATE TABLE 2010T_POSSkuTab
AS
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
1. Try a simpler name in the CREATE TABLE xxx. All uppercase. If you want mixed case, you'll have to put the name in quotes.
2. Use '2011JUL31' and '2011AUG06' for the dates. '2011-JUL-31' etc. should actually work with both SQL Server and Oracle.
it seems you have a dot in the table name
...FROM XBRADMIN.POS_SKU_TAB

in you previous postings it was
...FROM XBRADMIN_POS_SKU_TAB
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author

Commented:
Sorry about the abandoning - I figured it out - below is my fix:

=========
When Microsoft Access converts a standard query to SQL Pass-Through, it generally does not take into account what type of database it will be going against and does  not update the SQL Query language accordingly.

To write a SQL Pass-Through query against an Oracle database, you will need to update your SQL to Oracle friendly SQL

=========
EXAMPLES
———
If you were converting the following Query to a SQL Pass-Through query:

SELECT XBRADMIN_POS_SKU_TAB.STORENUM AS Store, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY, XBRADMIN_POS_SKU_TAB.LINENUM, Int([CASHIERNUM]) AS SalesID, XBRADMIN_POS_SKU_TAB.ITEM, XBRADMIN_POS_SKU_TAB.MERCH_DEPT, XBRADMIN_POS_SKU_TAB.MERCH_CLASS INTO 2010T_POSSkuTab
FROM XBRADMIN_POS_SKU_TAB
WHERE (((XBRADMIN_POS_SKU_TAB.STORENUM)<3000) AND ((XBRADMIN_POS_SKU_TAB.TRANSDATE) Between #7/31/2011# And #8/6/2011#) AND ((XBRADMIN_POS_SKU_TAB.ORGID)=1) AND ((XBRADMIN_POS_SKU_TAB.DIVISION)=1) AND ((XBRADMIN_POS_SKU_TAB.TRANSSTAT)="COMPLETE") AND ((XBRADMIN_POS_SKU_TAB.TRANSTYPE)<>"RETURN") AND ((XBRADMIN_POS_SKU_TAB.TRAINING_FLAG)="N") AND ((XBRADMIN_POS_SKU_TAB.VOID_CODE)=0))
ORDER BY XBRADMIN_POS_SKU_TAB.STORENUM, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY;


You would first need to update the fields (dates, remove double quotes on where criteria, etc…) to look like this:

SELECT STORENUM AS Store, TRANSDATE, TRANSNUM, REGNUM, QUANTITY, LINENUM, CASHIERNUM AS SalesID, ITEM, MERCH_DEPT, MERCH_CLASS
FROM XBRADMIN.POS_SKU_TAB
WHERE (((STORENUM)<3000) AND ((TRANSDATE) Between '31-jul-2011' And '6-aug-2011') AND ((ORGID)=1) AND ((DIVISION)=1) AND ((TRANSSTAT)='COMPLETE') AND ((TRANSTYPE)<>'RETURN') AND ((TRAINING_FLAG)='N') AND ((VOID_CODE)=0))
ORDER BY STORENUM, TRANSDATE, TRANSNUM, REGNUM, QUANTITY;

Author

Commented:
Please see my final comment, and award points to fomoruto

Thanks!

Author

Commented:
It really ended up only being about query syntax.
The SQL Language MSAccess converted the query to for pass-through, was inaccurate when going against Oracle.

I corrected the syntax and all was well -
See my final comment for answer/notes.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Just so you understand,  Access does not "convert" the SQL in your query when you convert it to a pass-through query, you have to do that.