db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nicolas Dufour <nicolas.duf...@neometsys.fr>
Subject Re: Select and null problem
Date Wed, 26 Oct 2005 22:11:21 GMT
Ahh perhaps i didnt explain very well my problem :

I have an error with this query :
insert into foo (id, name, date, remark) select 1234, 'a_text', null, 
remark from foo where id = 1234;
And right here derby is not happy by the null value.

The same if a do this :
select 'test', null from foo;

Because i have a table where the date can be null.

better like that ?

Nicolas

Rajesh Kartha wrote:
> 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:
>
> <sql>
> 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 tab1;
> select * from tab1;
> </sql>
>
> 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 10.1.1.0 - (225786).
>
> Regards,
> Rajesh
>


Mime
View raw message