sql – How to explicitly set the type for NULL value in SELECT?

Question:

There is a table with columns COL_1 number, COL_2 number .

Full request:

SELECT * FROM TABLE_1

And there is a view:

CREATE VIEW V1 AS
    SELECT NULL AS COL_1, TABLE_2.COL AS COL_2 FROM TABLE_2

I want to combine them:

SELECT * FROM TABLE_1
union all
SELECT * FROM V1

And I get an error – the expressions do not match, expected NULL , in the view is of type varchar2 . But I didn't ask it to be text.

How to explicitly set the type?

Answer:

It is necessary to bring the columns to the same type. For example for:

create table table_1 (col_1 number(10), col_2 number);
create table table_2 (col number);

it will be like this:

CREATE VIEW V1 AS
SELECT CAST (NULL AS number(10)) AS COL_1, TABLE_2.COL AS COL_2 FROM TABLE_2
;

The CAST function will convert NULL , by default VARCHAR2(0) , to the data type specified in the second operand.

The same result can also be achieved with the to_number(null) function, but only if there is no need for a length constraint (so-called non-constrained type ). That is, for example:

cast (null as number(10)) ... -- user_tab_cols.data_precision=10     
to_number (null) ...          -- user_tab_cols.data_precision=null