db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <kar...@Source-Zone.Org>
Subject Re: Select and null problem
Date Wed, 26 Oct 2005 22:00:22 GMT
Nicolas Dufour wrote:

> Hi
> I m trying to do a pretty simple insert :
> insert into foo (fields ........) select value1, value2,  ....,  
> field3, field5 from foo where id = x
> Everything work until when a value is equal to NULL, i have this 
> message :
> error: Encountered "null" at line ....
> Ok sql has seen a null well good for it ... but why its an error !?
> Thanks
> Nicolas Dufour

Hi Nicolas,

Is there any stack trace, SQLState etc. ? Can you shed some light on 
the  table schema ? How many rows are you trying to insert ?

Based on your mail I tried  a very very simple example in ij:

connect 'jdbc:derby:testdb;create=true';
drop table tab1;
create table tab1 (id int, col1 char(1), col2 char(2), col3 char(3));
insert into tab1 values (1,'a','aa','aaa');
insert into tab1 values(2,'b','bb','bbb');
insert into tab1(id,col2,col3) values (3,'cc','ccc');
insert into tab1(id,col2,col3) values (4,'dd','ddd');
insert into tab1(id,col1,col2) values (5,'e','ee');
select * from tab1;
insert into tab1(id, col1, col2, col3) select id, col1, col2, col3 from 
select * from tab1;

it worked fine for me and the final select gives me:

ID         |COL1|COL2|COL3
1          |a   |aa  |aaa
2          |b   |bb  |bbb
3          |NULL|cc  |ccc
4          |NULL|dd  |ddd
5          |e   |ee  |NULL
1          |a   |aa  |aaa
2          |b   |bb  |bbb
3          |NULL|cc  |ccc
4          |NULL|dd  |ddd
5          |e   |ee  |NULL

10 rows selected

Correct me, is this similar to what you are trying to do ? Adding the 
where clause like yours also works fine.
I am using  Derby - (225786).


View raw message