Need syntax for simple select from two tables in Oracle

Steve Jennings
Steve Jennings used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Might also want to port to using ANSI syntax:
select a.name,a.subTypeName, model, a.ver, b.ver, a.eosc, a.eoswmr, a.eorfa, a.ldos, a.eosd 
from inv2 a join modvendorver b using(model);

Open in new window

Steve JenningsSr Manager Cloud Networking Ops

Author

Commented:
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"
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Steve JenningsSr Manager Cloud Networking Ops

Author

Commented:
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.
johnsoneSenior Oracle DBA

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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 JenningsSr Manager Cloud Networking Ops

Author

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

Thanks,
Steve
Steve JenningsSr Manager Cloud Networking Ops

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>> 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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial