SQL Help Please

[tkosel]

I have created a pass through query to get some data from a Oracle Database.
The SQL in that query appears below.

SELECT STATION.STN_NAME, PRODUCT.PROD_ID, PRODUCT.PROD_NAME, SETUP.SETUP_ID,
SETUP.SETUP_NAME, V_DATA.PIECE_NMBR, CHAR_SETUP.CHAR_NAME, V_DATA.VAR_VALUE
FROM ((((PRODUCT INNER JOIN SG_DATA ON PRODUCT.PROD_ID = SG_DATA.PROD_ID)
INNER JOIN SETUP ON SG_DATA.SETUP_ID = SETUP.SETUP_ID) INNER JOIN V_DATA ON
SG_DATA.SG_ID = V_DATA.SG_ID) INNER JOIN CHAR_SETUP ON V_DATA.CHAR_ID =
CHAR_SETUP.CHAR_ID) LEFT JOIN STATION ON SG_DATA.STN_ID = STATION.STN_ID
WHERE (((PRODUCT.PROD_ID) = 1403125760) And ((SETUP.SETUP_ID) = 1454899200))
ORDER BY V_DATA.PIECE_NMBR, CHAR_SETUP.CHAR_NAME;

When I run it, it tells me that :

ODBC Call Failed

[Oracle][ODBC][Ora]ORA-00928: missing SELECT keyword
(#928)

As far as I can tell, my SQL statement is correct, can anyone out there tell
me why it is not working? I have a similar Access Query using Linked tables
to the same database that works just fine. I have other similar pass through
queries to the same Oracle database that also work just fine. I am stuck on
this one! HELP!!!!

 

[Stefan Hoffmann]

hi,

I have created a pass through query to get some data from a Oracle Database.
The SQL in that query appears below.

SELECT STATION.STN_NAME, PRODUCT.PROD_ID, PRODUCT.PROD_NAME, SETUP.SETUP_ID,
SETUP.SETUP_NAME, V_DATA.PIECE_NMBR, CHAR_SETUP.CHAR_NAME, V_DATA.VAR_VALUE
FROM ((((PRODUCT INNER JOIN SG_DATA ON PRODUCT.PROD_ID = SG_DATA.PROD_ID)
INNER JOIN SETUP ON SG_DATA.SETUP_ID = SETUP.SETUP_ID) INNER JOIN V_DATA ON
SG_DATA.SG_ID = V_DATA.SG_ID) INNER JOIN CHAR_SETUP ON V_DATA.CHAR_ID =
CHAR_SETUP.CHAR_ID) LEFT JOIN STATION ON SG_DATA.STN_ID = STATION.STN_ID
WHERE (((PRODUCT.PROD_ID) = 1403125760) And ((SETUP.SETUP_ID) = 1454899200))
ORDER BY V_DATA.PIECE_NMBR, CHAR_SETUP.CHAR_NAME;

Looks like Jet SQL, not PL/SQL.

If you don't have an PL/SQL editor, I'm using http://sqltools.net/.

Maybe removing all the parentheses works.


mfG
--> stefan <--

 

[Larry Linson]

Looks like Jet SQL, not PL/SQL.
If you don't have an PL/SQL editor, I'm using
http://sqltools.net/.

Maybe removing all the parentheses works.

To add to what Stefan wrote, and implied -- perhaps you were not aware that
a passthrough query has to be written in the SQL dialect of the database
engine to which it is being passed. The "passthrough" means to ODBC to not
translate the SQL, but to "pass it through completely unmodified". In your
case, the SQL must be Oracle's "dialect". Jet SQL differs more from Standard
SQL than do most server databases.

Larry Linson
Microsoft Office Access MVP