asked on

Need syntax for simple select from two tables in Oracle

I have this MySQL SQL statement that works fine:

select a.name,a.subTypeName, a.model, a.ver, b.ver, a.eosc, a.eoswmr, a.eorfa, a.ldos, a.eosd 
from inv2 a, modvendorver b 
where a.model = b.model;

Open in new window

But that same code in Oracle gives:

Line 1, ORA-00904: "b.model": invalid identifier

And when I change it to:

select "inv2.name", "inv2.subTypeName", "inv2.model", "inv2.ver", "modvendorver.ver"
from "inv2"
inner join "modvendorver"
on "inv2.model" = "modvendorver.model"

Open in new window

Oracle gives:
Line 4, ORA-00904: "modvendorver.model": invalid identifier

I've tried several other statements using full, left and right join . . . all give me the same error. But I cannot find what I am doing wrong.

Any thoughts?

Steve

slightwv (䄆 Netminder)

Don't use double quotes in Oracle.

>>Line 1, ORA-00904: "b.model": invalid identifier

The syntax looks good from a quick scan. Verify the modvendorver table in Oracle has a model column.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Jennings

ASKER

Never mind . . . I am new to Oracle Application Express, and it turns out that the syntax is quirky. This is what works:

select "inv2"."name" as "name",
"inv2"."subTypeName" as "subTypeName",
"inv2"."vendor" as "vendor",
"inv2"."model" as "model",
"inv2"."ver" as "ver",
"modvendorver".MVER as MVER
from "modvendorver" "modvendorver",
"inv2" "inv2"
where "model" = "MMODEL"

Steve Jennings

ASKER

You can have the points "slightwv" . . .thanks for responding. As I mentioned, this is development in Oracle Application Express. Shoot me for failing to mention that in the original post. APEX adds the double quotes and the quirky syntax.

johnsone

That isn't quirky syntax. That is someone didn't design your database correctly. They had to specifically create all those objects with lower case names rather than the default of all upper case.

slightwv (䄆 Netminder)

>>APEX adds the double quotes and the quirky syntax.

Yes, what johnsone said.

In Oracle double quotes FORCES case sensitivity and is a REALLY REALLY bad thing to do.

By default Oracle makes everything upper case in the data dictionary.

Instead of:
create table bob (col1 char(1));

Then you NEVER need double quotes. You could do: select * from BoB; or select * from bOb;

, someone did:
create table "bob" ("col1" char(1));

Now you are ALWAYS forced to use double quotes FOREVER.

Steve Jennings

ASKER

. . . it's Oracle Application Express specific syntax, johnsone. It's all translated before it hits the data base.

Thanks,
Steve

Steve Jennings

ASKER

UGH . . . that means it was probably ME. I just created the tables and the column values and chose the "Preserve Case" button. So I likely shot myself in the foot.

Thanks for pointing that out. I am brand new to Oracle and only moderately skilled with MySQL.

Steve

slightwv (䄆 Netminder)

>> I just created the tables and the column values and chose the "Preserve Case" button.

Yep. Don't do that... ;)

I would strongly suggest you rebuild. It will save you MANY headaches down the road.