CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Rekha Rao
Rekha Rao

Reputation: 13

Error in Inner Joins

I am trying to practice inner joins but in vain.I am getting table does not exist error while executing inner join statements even though I have created tables and data are present

Kindly help me with this.Also, it shows Customers is disconnected from rest of the join graph.

I have tried using double quotes for table names but then it gives different error as

ORA-00904: "CUSTOMERS"."CUSTOMER_ID": invalid identifier
00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

SELECT
    Orders.OrderID,
    Customers.CustomerName
FROM
    Orders
    inner join Customers on Orders.Customer_Id = Customers.Customer_Id;

Its Oracle 11g and SYS is the owner.

Upvotes: 0

Views: 228

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

The problem could be due to your create table statement,which contains double quoted table names, that are not all in uppercase. Remember, oracle always stores table names and column names in uppercase by default, unless you have used double quoted strings with varying case in the create/Alter statements.Using the whole table names to refer the columns should be avoided, as that is the reason for invalid identifier error in your case.

So, either rename the table from "Customers" to CUSTOMERS and "Orders" to ORDERS if you can, or use quotes for table names in your select query along with appropriate aliases .

SELECT
    o.OrderID,
    c.CustomerName
FROM
    "Orders" o
    inner join "Customers" c on o.Customer_Id = c.Customer_Id;

Also, as @horse has already suggested, create table in any sample schema like HR, SCOTT etc and not in SYS.

Upvotes: 1

Related Questions