I'm attempting to insert numeric values into a table based on data from two other tables. These numeric values are associated with strings in the table SL_TERMS. These strings are used in the other table, UPLOAD_TEST, which has the data with which I want to populate PC_TEST.
For example, if UPLOAD_TEST has the string "environment" and its ID is 1 in the SL_TERMS table, I want to insert the value 1 into PC_TEST. Here are two examples of the code so far:
INSERT INTO PC_TEST (TEST_ID, WHAT_ID, DATA_FORM)
SELECT PC_TEST_SEQ.NEXTVAL, t.TERM_ID, e.DATA_FORM FROM SL_TERMS t, UPLOAD_TEST e
WHERE t.TERM_ID IN
(SELECT WHAT FROM UPLOAD_TEST e WHERE e.WHAT = t.TERM_NAME AND t.TERM_NAME = t.TERM_ID);
--TERM_NAME is the field that has the string associated with the needed ID
And
INSERT INTO PC_TEST (TEST_ID, WHAT_ID, DATA_FORM)
SELECT PC_TEST_SEQ.NEXTVAL, t.TERM_ID, e.DATA_FORM FROM SL_TERMS t
INNER JOIN UPLOAD_TEST e
ON e.WHAT = t.TERM_NAME AND t.TERM_NAME = t.TERM_ID; --using join keyword
Both give the ORA-01722: invalid number error. The WHAT_ID and TERM_ID fields are both numeric. Is it possible the strings are not being matched properly? In which case I may need to apply TRIM and/or LOWER functions to the strings from UPLOAD_TEST. Any help/suggestions is appreciated.
e.WHAT and t.TERM_NAME are the same fields with a one to many relationship. So, in SL_TERMS, the value TERM_NAME = 'environment' exists once and so does its associated TERM_ID = 1. It may exist in several records in UPLOAD_TEST.
The flow is therefore e.WHAT (string) --> t.TERM_NAME (string) --> t.TERM_ID (number).
The reason I equated t.TERM_NAME to t.TERM_ID is because I need that associated ID when I call e.WHAT (t.TERM_NAME). I didn't know if this is the correct way to do it (it doesn't seem like it), so if there's a way to call the ID (TERM_ID) from the string (TERM_NAME), that's what I need.
I didn't know if this is the correct way to do it (it doesn't seem like it), so if there's a way to call the ID (TERM_ID) from the string (TERM_NAME), that's what I need.
It isn't the correct way to do it. You don't need to call the ID from the name string; they are in the same row in the table, so the ID is just available once you've identified the row by its ID.
So you can just do:
INSERT INTO PC_TEST (TEST_ID, WHAT_ID, DATA_FORM)
SELECT PC_TEST_SEQ.NEXTVAL, t.TERM_ID, e.DATA_FORM
FROM SL_TERMS t
INNER JOIN UPLOAD_TEST e
ON e.WHAT = t.TERM_NAME;
You're getting the error because when you include the t.TERM_NAME = t.TERM_ID clause Oracle is having to try to convert every identified row's TERM_NAME to a number in order to compare it with the numeric ID. Unless the names consist only of numeric characters, that will cause the error you're seeing. In your example you're implicitly trying to to to_number('environment') = 1, and that implicit to_number() will throw ORA-01722 because 'environment' is not a number.
Arguably Oracle could have chosen to convert the number to a string, but even if it did do that (and it won't - "When comparing a character value with a numeric value, Oracle converts the character data to a numeric value."), the ID and name are still not going to be the same, so you'd get no data instead of an error.