I have table with a Varchar field. See structure.
IMAGE_KEY VARCHAR2(32 BYTE)
DOC_TYPE VARCHAR2(8 BYTE)
DOC_KEY VARCHAR2(256 BYTE)
LAST_UPDT DATE
UPDT_USER VARCHAR2(6 BYTE)
BLK_HANDLE VARCHAR2(16 BYTE)
DOC_KEY_ID NUMBER(15,0)
FE_ID NUMBER(15,0)
I want to create a view and convert the field Doc_Type which is varchar to a Number field in the view using the sql scripts.
SELECT IMAGE_KEY,
DOC_TYPE,
cast(DOC_KEY as NUMBER(15)) as DOC_KEY ,
LAST_UPDT,
UPDT_USER,
BLK_HANDLE,
DOC_KEY_ID,
FE_ID
FROM rdo
and
SELECT IMAGE_KEY,
DOC_TYPE,
TO_NUMBER (TRIM (DOC_KEY)) as DOC_KEY ,
LAST_UPDT,
UPDT_USER,
BLK_HANDLE,
DOC_KEY_ID,
FE_ID
FROM rdo
I get the following error when I extract data from the views created by any of the scripts above.
An error was encountered performing the requested operation:
ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number. Vendor code 1722
How do I successfully convert the varchar field to a Number field using a create view statement.
