Good day Everyone.
As you probably guess I am not the best in Oracle but I have to perform a task where I require to compare data in one table for the same data in an other table but the data I require to compare is a CLOB in the first table and a number in an other table.
So on my table 1 the CLOB value is actually a number that we can compare to integer in SQL Server and we have a valid number that should match that number in a different table.
I need to find out what value exist in that CLOB table that does not exist in my other table.
To do that I would like to convert the CLOB data to number to match the proper value and than see if the value does exists in the other table.
I tried to run the following script to insert the columns I want to compare into a temporary table that would have the values as NUMBER instead of CLOB data.
CREATE TABLE TEMP_TABLE AS(SELECT id, entity_id, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
FROM MYTABLE
WHERE NAME = 'LINKID');
and I get the following error:
Error starting at line : 6 in command -
CREATE TABLE TEMP_REG AS(SELECT id, entity_id, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
FROM MYTABLE
WHERE NAME = 'LINKID')
Error report -
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
Would anyone have an idea as to how I could do that?
If I could simply convert the value and then compare it as a number that would be better than having to export the data to an other table but it would give me more flexibility once converted.
Thanks in advance and any help would be appreciated.
As you probably guess I am not the best in Oracle but I have to perform a task where I require to compare data in one table for the same data in an other table but the data I require to compare is a CLOB in the first table and a number in an other table.
So on my table 1 the CLOB value is actually a number that we can compare to integer in SQL Server and we have a valid number that should match that number in a different table.
I need to find out what value exist in that CLOB table that does not exist in my other table.
To do that I would like to convert the CLOB data to number to match the proper value and than see if the value does exists in the other table.
I tried to run the following script to insert the columns I want to compare into a temporary table that would have the values as NUMBER instead of CLOB data.
CREATE TABLE TEMP_TABLE AS(SELECT id, entity_id, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
FROM MYTABLE
WHERE NAME = 'LINKID');
and I get the following error:
Error starting at line : 6 in command -
CREATE TABLE TEMP_REG AS(SELECT id, entity_id, CAST(CAST(DATA AS VARCHAR(200)) AS NUMBER(10)) AS DOCNUMBER
FROM MYTABLE
WHERE NAME = 'LINKID')
Error report -
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
Would anyone have an idea as to how I could do that?
If I could simply convert the value and then compare it as a number that would be better than having to export the data to an other table but it would give me more flexibility once converted.
Thanks in advance and any help would be appreciated.
