Joining two tables keeps returning invalid number ORA-01722

Question

I am trying to return a list of customer names with the number of orders that have been placed. I have tried this a number of different ways, but there are no common columns between the two tables where "quantity" and "firstname" are.

When trying to pull from just the "CUSTOMER" and "ORDERDETAIL" tables, it returns ORA-01722 "Invalid Number". I will point out that I am very new to this, and also very behind where I am supposed to be in learning. Below are the tables.

CREATE TABLE customer (
customerID NUMBER PRIMARY KEY,
firstName VARCHAR2 (30) NOT NULL,
lastName VARCHAR2 (30) NOT NULL,
address VARCHAR2 (60),
city VARCHAR2 (30),
state VARCHAR2(20),
zipCode CHAR (5),
country VARCHAR2 (50),
phone CHAR (10),
email VARCHAR2 (30) NOT NULL UNIQUE,
username VARCHAR2 (20) NOT NULL UNIQUE,
password VARCHAR2 (20) NOT NULL
);

CREATE TABLE orderInfo (
orderID NUMBER PRIMARY KEY,
customerID NUMBER REFERENCES customer (customerID),
orderDate DATE,
shipDate DATE
orderDetailID NUMBER REFERENCES orderDetail (orderDetailID)
);

CREATE TABLE orderDetail (
orderDetailID NUMBER PRIMARY KEY,
orderID NUMBER REFERENCES orderInfo (orderID),
productID VARCHAR2 (10) REFERENCES product (productID),
price NUMBER (7,2),
quantity NUMBER CHECK (quantity > 0)
);

CREATE TABLE product (
productID VARCHAR2 (10) PRIMARY KEY,
categoryID VARCHAR2 (10) REFERENCES categoryInfo (categoryID),
productName VARCHAR2 (40),
productDescription VARCHAR2 (100),
unitPrice NUMBER,
picture BLOB
);

CREATE TABLE categoryInfo (
categoryID VARCHAR2 (10) PRIMARY KEY,
categoryName VARCHAR (50),
description VARCHAR2 (100)
);

Seems like you forgot to post the query. Please edit your question and include it. — sticky bit

APC APC · Accepted Answer · 2018-08-12T17:21:35

When trying to pull from just the "CUSTOMER" and "ORDERDETAIL" tables, it returns ORA-01722 "Invalid Number".

Well that rather depends on how you write the join. Although, as you say in your post, they have no columns in common, so any join will be wrong.

I am trying to return a list of customer names with the number of orders that have been placed.

All you actually need to answer this assignment would seem to be the CUSTOMER names and the number of Orders per Customer. Which means counting the number of ORDERINFO records. Like this:

select c.customerID ,
       c.firstName,
       c.lastName,
       count(o.orderid) no_of_orders
from orderinfo o
     join customer c
     on c.customerID = o.customerID  
group by c.customerID ,
       c.firstName,
       c.lastName
/

Joining two tables keeps returning invalid number ORA-01722

1 Answers