Home » SQL & PL/SQL » SQL & PL/SQL » union error
union error [message #399128] Mon, 20 April 2009 18:13 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

create table emp(id number,name varchar2(20),sal number)
/
create table stg_emp(id1 number,id number,name varchar2(20),hd date)
/
insert into emp values(1,'one',100)
/
insert into stg_emp values(2,2,'two',sysdate)
/


i am givng the following query using union, as i want to see both the rows in the result

SQL> select id1,id,name,hd,to_char(null) as "sal" from stg_emp
  2  union
  3  select to_char(null) as "id1",id,name,to_char(null) as "hd",sal from emp;
select id1,id,name,hd,to_char(null) as "sal" from stg_emp
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


but i have used to_char(null) to handle this, then why am i getting thie error?
Re: union error [message #399129 is a reply to message #399128] Mon, 20 April 2009 18:16 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>why am i getting thie error?
ORA-01790: expression must have same datatype as corresponding expression
Number is not same as TO_CHAR
Re: union error [message #399137 is a reply to message #399129] Mon, 20 April 2009 20:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In Oracle NULL is already varchar2 (it must have some datatype):

SQL> create or replace view v_null_tst as
  2    (select 'abc' col1, null col2, 123 col3 from dual);

View created.

SQL> descr v_null_tst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(3)
 COL2                                               VARCHAR2
 COL3                                               NUMBER

SQL>


If you want to force a specific datatype (commonly a requirement in unions) you can do things like this:

SQL> create or replace view v_null_tst2 as
  2    (
  3    select 'abc' col1, to_number(null) col2, to_date(null) col3 from dual
  4    UNION all
  5    select null, 999, sysdate from dual);

View created.

SQL> descr v_null_tst2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(3)
 COL2                                               NUMBER
 COL3                                               DATE

SQL>
Previous Topic: versioning
Next Topic: First time user (Merged 3)
Goto Forum:
  


Current Time: Mon Dec 05 07:00:24 CST 2016

Total time taken to generate the page: 0.14517 seconds