insert into using union with null values

[crystalhelp]

This is a short example of my code:

insert into table3(a,b,c,d,e,f,g)
select h,i,j,null,null,null,k
from table 1
union
select null,null,null,l,m,n,o
from table 2

If I run each insert statement individually, it works. But if I run them together in a union I get the error: ORA-01790: expression must have same datatype as corresponding expression. I double-checked and the datatypes are the same between the 3 tables.

I have isolated the problem. If I put column names in where the nulls are in the second select statement it runs without problem. So, obviosly nulls aren't allowed in the second select statement. But I want nulls inserted in the third table, so how can I write this?

I know this is a weird question, but I've written these type of insert statements in Sybase before, so I'm sure you can in Oracle, I just have to figure out what the trick is.

 

[stefanhei]

Hi.
You could create a view representing your unioned selects and the insert selecting from that view as a workaround.

Stefan

 

[sem]

Try to use use "typed" nulls: to_char(null), to_date(null) Regards, Dima

 

[cakani]

Try to execute union first without the insert and column by column.
1.
select h
from table 1
union
select null
from table 2
2.
select h,i
from table 1
union
select null,null
from table 2
3.
...
Now you in position to find problem.
Please describe tables.

 

[cakani]

Try to change null with '' ( empty string )

 

[jee]

The problem is that NULL doesn't have a data type, so you get the error message about the datatypes not matching between the parts of the union. Both of the solutions provided by SEM and CAKANI will work and give the same result:

insert into table3(a,b,c,d,e,f,g)
select h,i,j,'','','',k
from table 1
union
select '','','',l,m,n,o
from table 2

where '' is two single quotes

or
insert into table3(a,b,c,d,e,f,g)
select h,i,j,to_char(null),to_char(null),to_char(null),k
from table 1
union
select to_char(null),to_char(null),to_char(null),l,m,n,o
from table 2

assuming that your columns are all CHAR or VARCHAR2

 

[crystalhelp]

I haven't tried it yet, but I'm sure it will work. Thanks, I didn't expect so many great replies.