Hello,
here is the problem I'm having with HSQLDB (1.7.2 alpha T):

I have three tables:

1/ one describing a set of person:

create table person (
id integer primary key, -- internal code
name varchar(20)        -- external code
)

2/ one that contains a subset of persons with more informations:

create table external (
name varchar(20), -- external code
birthday date
)

3/ and the last is the same but with internal codes:

create table internal (
name integer, -- internal code
birthday date
)

giving the data:
insert into person values (1,'jhon');
insert into person values (2,'jim');
insert into person values (3,'bob');

and:
insert into external values ('bob',now);
insert into external values ('jhon',now);
insert into external values ('jim',now);
insert into external values ('bob',now);
insert into external values ('julia',now);


I would like to convert the data in the external table into the internal table:
i.e. expected result in table internal:

 name | birthday
 3    | 12-02-2003
 1    | 12-02-2003
 2    | 12-02-2003
 3    | 12-02-2003
null  | 12-02-2003

So I thought the following statement would do it:
1/ insert into internal (name,birthday) (select (select p.id from person p where 
p.name=ext.name), ext.birthday from external ext);

but it gives me the following error: Unexpected token: ( in statement [insert into 
internal (name,birthday) (]

I tried with:
2/ insert into internal (name,birthday) select (select p.id from person p where 
p.name=ext.name), ext.birthday from external ext;

but then I get:
Column count does not match in statement [insert into internal (name,birthday) values 
(select (select p.id from person p where p.name=ext.name),
ext.birthday from external ext)]

And at last:
3/ insert into internal (name,birthday) select (select p.id from person p where 
p.name=ext.name), ext.birthday from external ext;

gives me:
Single value expected

What can I do?

Thanks for any comment :-),
Loïc

ps: 1/ works well under Oracle (replacing now with sysdate)







This message and any attachments (the "message") is intended solely for the addressees 
and is confidential. 
If you receive this message in error, please delete it and immediately notify the 
sender. Any use not in accord with 
its purpose, any dissemination or disclosure, either whole or partial, is prohibited 
except formal approval. 
The internet can not guarantee the integrity of this message. BNP PARIBAS (and its 
subsidiaries) shall (will) not 
therefore be liable for the message if modified. 

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le "message") sont etablis a 
l'intention exclusive de ses 
destinataires et sont confidentiels. Si vous recevez ce message par erreur, merci de 
le detruire et d'en avertir 
immediatement l'expediteur. Toute utilisation de ce message non conforme a sa 
destination, toute diffusion 
ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. 
L'internet ne permettant pas 
d'assurer l'integrite de ce message, BNP PARIBAS (et ses filiales) decline(nt) toute 
responsabilite au titre de ce 
message, dans l'hypothese ou il aurait ete modifie.



-------------------------------------------------------
This SF.net email is sponsored by: SF.net Giveback Program.
Does SourceForge.net help you be more productive?  Does it
help you create better code?  SHARE THE LOVE, and help us help
YOU!  Click Here: http://sourceforge.net/donate/
_______________________________________________
hsqldb-developers mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

Reply via email to