Invalid number for table and primary key

Question

I am creating a table for license, which is made up of 2 attributes from one table and one from the other. The license type and customer is in the main customer table and License_ID in another, as only certain types of customers can have a License_id.

I want to create a License table which has a primary key of License number and then the types and customer_ids for that License number.

This error occurs when trying to create the table, I am trying to do it so when the table is created it only takes the License_number's which are not 0/null. I also want to make the License_number the primary key, as well as the foreign key and the other 2 attributes foreign keys.

How can I do this?

CREATE TABLE License AS SELECT
DISTINCT License_number, License_type, cust_id
FROM yoda_60
WHERE License_number !=0;

Error report -
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

You need to show the types of the columns in the source table. — chrisis
add the two table structure and small data so that Query can be made to create the required table with required attribute. For mySql users mysql documentation dev.mysql.com/doc/refman/5.6/en/create-table-select.html is helpful — jeetendra Mandal

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-08-10T11:35:50

Presumably License_Number is a string. So, put the comparison in single quotes:

WHERE License_number <> '0';

What is happening is that the comparison to a number is causing all License_Numbers to be converted to numbers -- and some are not numeric. Hence, the error.

This will also filter out NULL values, but you can be explicit:

WHERE License_number <> '0' AND License_number IS NOT NULL;

Invalid number for table and primary key

1 Answers