Oracle SQL Statement “Missing Keyword”

Topic

New Reply

Hoping what’s missing is obvious to skilled SQL writers. I’ve cut the statement down to the bare minimum and am scratching my head . . . :

SELECT ID as “Case ID”
FROM T_CASE
INNER JOIN T_INVESTIGATION
SELECT ID as “INVESTIGATION ID”
FROM T_INVESTIGATION
WHERE T_CASE.ID=T_INVESTIGATION.ID;

Returns:
[INDENT]Could Not Run Query

java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword
SELECT ID as “Case ID” FROM T_CASE INNER JOIN T_INVESTIGATION SELECT ID as “INVESTIGATION ID” FROM T_INVESTIGATION WHERE T_CASE.ID=T_INVESTIGATION.ID;[/INDENT]

Help?

TIA

Ron

Reply | Quote

Replies

Yeah, what is going wrong is that you are trying to perform a join (which is one statement), as two statements. Any SQL parser is going to choke on the syntax you’re using.

Try this instead:

SELECT T_C.ID As “Case/Investigation ID”
FROM T_CASE As T_C
INNER JOIN T_INVESTIGATION As T_I
On T_C.ID=T_I.ID;

I’ve taken the liberty of moving your Join criteria out of the Where clause. You can put the Join criteria in a Where but it’s a better practice to use the dedicated On clause for this purpose.

Reply | Quote

Thank you! I’m a hack, I can “usually” read a query, but can’t write one 100% on my own and, at least for me, the even simplistic tutorials jump too deep too quick. So I ask for help LOL, I copied the syntax straight from a tutorial site. Go figure.

The application I’m working in goes from a very limited set of canned query options to 100% SQL statements, without the option of seeing the code written using the canned options.

Thanks again, I expect this won’t be my last request for help.

Reply | Quote

Did you understand what I did, and how it corrected the problem with the original query?

Your query started off well. However immediately after your join, you had the following:

SELECT ID as “INVESTIGATION ID”
FROM T_INVESTIGATION

These 2 lines were like alien interlopers, trampling all over the local SQL vegetation! They had to go. The rest was a matter of tidying up and organizing.

For instance I implemented aliases for the two tables involved. You don’t have to use aliases but nearly everyone does. This is particularly helpful when you have fields from two different tables with the same name, as you do. The aliases are used in place of the table names. The standard practice is to shorten the table names down to some small but still recognizable marker.

Reply | Quote

I caught the aliases, but didn’t know the term or the “Why.” The sequence I don’t understand. I would have thought the alias would have to be defined prior to the “FROM” or the statement would need to be:
FROM T_CASE As T_C.ID
INNER JOIN T_INVESTIGATION As T_I.ID

Sadly, when I got into work and tried the code, I get a new error:

Could Not Run Query

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
SELECT T_C.ID As “Case/Investigation ID” FROM T_CASE As T_C INNER JOIN T_INVESTIGATION As T_I On T_C.ID=T_I.ID;

From my reading, and “/” shouldn’t work, and yes, it didn’t work.

Reply | Quote

Partial success! Figured out how to look at table contents, very enlightening, many static look up tables. Then I found where to look at the SQL written in the canned report builder ( didn’t have that option untill I received additional permissions yesterday). That provided the join I was struggling with, and the joins to the look-up tables! I would never have gotten anything with what I “thought” I had as the foreign key because even though there is an “ID” in each table, that isn’t the actual foreign key. The title changes in the different tables.

And learned about using aliases, though my earlier question stands.

Hopefully I can now use ISNULL to get somewhere . . . not an option in the caned report builder.

Thanks again!

Reply | Quote

It’s certainly possible that the slash character (/) might cause a problem.

However I would note that your latest error message is about command termination. That terminator is the semicolon, which is valid so far as I know. It certainly is valid in Microsoft SQL Server. Nevertheless with this error message, the first thing I would try is removing that semicolon. It is possible that your query environment does something funky like automatically adding a semicolon, thus resulting in a double semicolon with the syntax we used. Or maybe the semicolon really is totally banned in Oracle.

The limited Oracle experience I have revealed that Oracle strictly followed ANSI SQL standards. SQL Server extended those standards quite a bit. This showed up most strongly, in the systems I used, in object name length restrictions I encountered with Oracle.

Reply | Quote

It’s certainly possible that the slash character (/) might cause a problem.

However I would note that your latest error message is about command termination. That terminator is the semicolon, which is valid so far as I know. It certainly is valid in Microsoft SQL Server. Nevertheless with this error message, the first thing I would try is removing that semicolon. It is possible that your query environment does something funky like automatically adding a semicolon, thus resulting in a double semicolon with the syntax we used. Or maybe the semicolon really is totally banned in Oracle.

The limited Oracle experience I have revealed that Oracle strictly followed ANSI SQL standards. SQL Server extended those standards quite a bit. This showed up most strongly, in the systems I used, in object name length restrictions I encountered with Oracle.

The slash character is fine. I have had some limited cases where a terminating semicolon caused an error, but I have only observed this when using Microsoft Access as a client, with Pass-Through (P/T) queries to an Oracle 11g Release 2 database.

The problem is that in Oracle you can optionally use the “AS” keyword when aliasing columns, but not tables. Change your query to this:

select t_c.id as “Case/Investigation ID”
from t_case t_c
join t_investigation t_i
on t_c.id=t_i.id;

Also, the word “inner” is optional. I removed it.

Reply | Quote

Got past that problem, I was trying to kill an un-needed Join and was screwing up what was needed. Sadly the SQL didn’t pick up the variables, so I’m hacking my way through that. I believe that should go in a new thread, so unless I’m back to this error, will move on.

Thanks again,

Ron

Reply | Quote

Best of luck.

Reply | Quote

Thanks, I had that error too. On one query it needed the semicolon, on another I had to take it off. I had a “Blinding Flash of the Obvious” as was said in my former profession. I threw together a couple of quick tables in Access, set up the join the way I needed it, grabbed the SQL, pasted it into the Oracle and got what I needed. In the query anyway. That’s the one where I had to drop the semicolon. Now I’m trying to actually use the data I got. . . of all things the report writer won’t use the !@#$ query :mad:. And I can only get their report tool to build rows, not joined columns! And of course I can’t talk to the vendor directly! Arggggg We are playing with the application roles to see why I can’t get the reports I’d built earlier that can’t be filtered the way I need them but as a starting point to get around the report tool. Even though I have two roles, can only exercise one at a time. I wasn’t invited to be part of the beta testing, so I’m just :rolleyes: at this point.

Reply | Quote

Now I’m trying to actually use the data I got. . . of all things the report writer won’t use the !@#$ query :mad:. And I can only get their report tool to build rows, not joined columns!

The “report writer”? Are you talking about the report wizard in MS Access? Or are you using some other application, since you make reference to “the vendor”?

Reply | Quote

. . . Or are you using some other application, since you make reference to “the vendor”?

Sorry. It’s a MicroPact application built around Oracle. There are 20 odd tables, but the basic query tool only does one table, same with the report tool. Rows of a single table. But it doesn’t include the foreign key so I can’t export the tables and just drop them into Access. So now I have the SQL query, but can’t get the application to use it for a report so can’t export(I need to learn to be able to not need to use the word “so”).

Reply | Quote

I’m not familiar with MicroPact. My guess is that they are exposing a “super view” (query), sans your foreign keys, instead of just exposing a single table out of 20 tables. Will they provide the credentials needed to connect a different client to the Oracle database, even if it is just read only?

If “yes”, you could probably set up the required pass-through query in MS Access, taking advantage of the powerful reporting tool that is part of Access. If “no”, you could still export the de-normalized “table” (query or view) results to Access and use that as a starting point. But, if you want your report all within the MicroPact application, then you are probably SOL.

Reply | Quote

The sequence I don’t understand. I would have thought the alias would have to be defined prior to the “FROM”…

The import of this statement initially escaped me. Yet you have actually noticed something non-trivial about SQL and the order of statement processing.

When you take classes on SQL theory and implementation, a lot of the practical stuff amounts to memorization of the allowed query syntax. You memorize the statement forms and use them that way, end of story. The most important and canonical of all the statements is the Select statement because everyone uses it. Usually they teach you that one first.

The allowed Select syntax uses the following clauses, and in this order too:

Code:

Select…
From…
Where…
Group by…
Having…
Order by…
Offset…

The statement thus reads like human language, specifically English. However, and this is not commonly taught, the statement is not processed in this order. Believe it or not the From clause is processed first, just like you wondered about.

I never knew this until I read the following article:

Logical Query Processing: What It Is And What It Means to You

The money quote from this article is this:

The first curious thing to observe about logical query processing is that the order in which you type the main query clauses is different than the order in which they get logically interpreted.

I’ve been programming for 30 years and I never knew this…

Reply | Quote