Oracle developer. ORA-01722 Invalid Number when using UNION-CodePudding

I have this problem:

I have two tables: Table A and Table B, with the same column called PK_COLUMN that in both tables are numeric(8, 0).

I want to make this union:

Select 
    CAST(TRIM(TABLEA.PK_COLUMN) AS INT) 
FROM TABLE A 
UNION 
Select 
    CAST(TRIM(TABLEB.PK_COLUMN) AS INT) 
FROM TABLE B

And I got the next error code:

ORA-01722 Invalid number.

The problem is that when I execute the both parts of the union separately they work perfectly!

CodePudding user response：

As Dominik Klug stated, one of the values in pk_column can not be converted to an INT. You are not getting an error when running the queries individually because you are not scanning all of the rows, just a subset of what is returned first. If you run queries like

Select DISTINCT CAST(TRIM(TABLEA.PK_COLUMN) AS INT) 
FROM TABLEA;

Select DISTINCT CAST(TRIM(TABLEA.PK_COLUMN) AS INT) 
FROM TABLEB;

you will be able to identify which table has problematic records.

If you are on Oracle 12.2 or higher, you can use the VALIDATE_CONVERSION function to identify which values are failing to convert to an INT with queries like the ones below.

Select *
FROM TABLEA
WHERE VALIDATE_CONVERSION(TABLEA.PK_COLUMN as NUMBER) = 0;

Select *
FROM TABLEB
WHERE VALIDATE_CONVERSION(TABLEB.PK_COLUMN as NUMBER) = 0;