I have an issue where i require to compare data from a CLOG column where a specific data is stored and compare this with a different table. In order to do that I would like to first export the data I want and save it in a different table as a NUMBER value so that I can compare the data properly.
The issue I have is to insert the data in the new created table.
Here is the code I used to try getting the data into the other table.
1- I created the table I want the data into.
CREATE TABLE EXPORT_TABLE (ID NUMBER (30,0), ENTITYID NUMBER(30,0), DATANUMBER NUMBER(30,0));
Than I created the sql to grab the data from the table including the CLOB column as follow:
INSERT INTO EXPORT_TABLE (ID, ENTITYID, DATANUMBER)
VALUES (SELECT ID, ENTITYID, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DATANUMBER
WHERE NAME = 'LINKID');
I receive the following error:
Error starting at line : 7 in command -
INSERT INTO EXPORT_TABLE (SYSTEM_ID, ENTITYID, DOCNUMBER)
VALUES (SELECT system_id, entityid, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
WHERE NAME = 'LINKID')
Error at Command Line : 8 Column : 9
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
If I run the select statement I do get what I want to import but for some reason it does not get added to my table.
What did I missed here.
Answer by CharlesB ·
if it was that simple I would simply do select data from that column where it does not exist in the other table column but I still have to convert it as the data I am looking for in the other table is a NUMBER value. I cannot compare a CLOB data directly to a number value.
Answer by anthony.green ·
Not an oracle person, but typically you wouldn't want the values clause, you would just straight insert select
INSERT INTO EXPORT_TABLE (ID, ENTITYID, DATANUMBER) SELECT ID, ENTITYID, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DATANUMBER FROM TABLEWHERE NAME = 'LINKID';