select..union all select ..查询时返回ORA-01790报错的解决办法

示例语句：

create view TABLE_VIEW as select BIND_USER_ID,

USER_ID,

SYSTEM_CODE,

AREA_CODE,

CYCLE_ID,

PROVINCE_CODE,

UPDATE_TIME from TABLE1 union all select BIND_USER_ID,

USER_ID,

SYSTEM_CODE,

AREA_CODE,

CYCLE_ID,

PROVINCE_CODE,

UPDATE_TIME from TABLE2;

错误信息：ORA-01790: expression must have same datatype as corresponding expression

报错原因：TABLE1和TABLE2中对应的字段，数据类型不一致。比如TABLE1中的USER_ID数据类型为NUMBER，TABLE2中的USER_ID数据类型为VARCHAR2。

解决办法：建试图时用TO_CHAR，TO_NUMBER等函数转换数据类型。建议使用TO_CHAR。如本例，可以把SQL语句改为

create view TABLE_VIEW as select BIND_USER_ID,

TO_CHAR(USER_ID) as USER_ID ,

SYSTEM_CODE,

AREA_CODE,

CYCLE_ID,

PROVINCE_CODE,

UPDATE_TIME from TABLE1 union all select BIND_USER_ID,

USER_ID,

SYSTEM_CODE,

AREA_CODE,

CYCLE_ID,

PROVINCE_CODE,

UPDATE_TIME from TABLE2;

建议对转换数据类型的字段使用别名，否则如果该字段在第一个SELECT后，会报错

ORA-00998: must name this expression with a column alias