pass-through INNER JOIN

[dmcionex]

Hello,

I've just started to do pass-through queries in Access and have been having a problem getting the syntax right for SQL statements with inner joins.

Here's an example:

SELECT REPORTER.REPORTER_STATUS.SUMMARY, NETCOOL.JOURNAL.TEXT1, REPORTER.REPORTER_STATUS.CLASS
FROM NETCOOL.JOURNAL INNER JOIN REPORTER.REPORTER_STATUS
ON NETCOOL.JOURNAL.SERIAL = REPORTER.REPORTER_STATUS.SERIAL
WHERE REPORTER.REPORTER_STATUS.CLASS =11000

I get this error:

SQL command not properly ended

I thought it might be because there was no semicolon at the end but I learned early on that in pass-through queries those have to be taken out.

Thanks in advance,

Doug

 

[scriverb]

I don't see anything wrong with the SQL statement. Each SQL language is a little different and I am most familiar with NCR Teradata SQL which has it's own special nuances. But, yours seems to have all the required logical parts.

What I do in a situation like this is start with the simplest of queries and get that to work. Then add a little more. Create just the Select from portion without the inner join which leaves off some of the data I realize. If that works then add the inner join and get that to work. If you start getting the same error you know that is where the problem exists and you can start really looking at that part of the statement. If the inner join works then add on the WHERE statement and see if the error starts to show up.

This is a logical way of analyzing an error that is not very specific.

Let me know how it works out.

Bob Scriver

 

[dmcionex]

Thanks for the response Bob. I did start to break it down into simple statements and eventually found that I wasn't using the correct syntax for Oracle's SQL. I had linked to Oracle tables via ODBC. Here's what worked in the pass through query:

SELECT REPORTER.REPORTER_STATUS.SUMMARY, NETCOOL.JOURNAL.TEXT1, REPORTER.REPORTER_STATUS.CLASS
FROM NETCOOL.JOURNAL, REPORTER.REPORTER_STATUS
WHERE NETCOOL.JOURNAL.SERIAL = REPORTER.REPORTER_STATUS.SERIAL
AND REPORTER.REPORTER_STATUS.CLASS =11000

The 'INNER JOIN' and 'ON' syntax did not need to be used.

Thanks again for the response. It definitely helps to break it down.

Doug

 

[scriverb]

We've all been there and done that. You are not alone.

Bob Scriver