Hello group,
My sql statement for a transaction:
BEGIN TRANSACTION;
INSERT INTO
EXPERIMENT(study_id,genechip_id,sample_id,mat_id,hyb_id,exp_aim)VALUES((SELECT
study_id from study where study_author = 'Georgantas
RW 3rd and Civin CI'),(SELECT genechip_id from
genechip where genechip_array = 'U133B'),(SELECT
sample_id FROM sample WHERE sample_name ='Human stem
cells'),(SELECT mat_id FROM material_manip where
mat_treatment_type = 'ES H1-1a'),(select hyb_id from
experiment where exp_id =
currval('experiment_exp_id_seq')),'In this experiment
ES H1-1a sample was used test on U133B chip ');
INSERT INTO
PLATFORM(platform_filename,platform_file_loc,exp_id)VALUES('RGeo-ESHSC-H1ES-1a-U133B','\/home\/database\/bob\/data\/es\/RGeo-ESHSC-H1ES-1a-U133\/RGeo-ESHSC-H1ES-1a-U133B',(currval('experiment_exp_id_seq')));
INSERT INTO
CEL(exp_id,platform_id,affy_x,affy_y,affy_mean,affy_stdv,affy_npixels)VALUES((currval('experiment_exp_id_seq')),(SELECT
platform_id from platform where platform_filename =
'RGeo-ESHSC-H1ES-1a-U133B'),0,0,125.0,18.8,25);
Error:
bob=> \i
/home/database/bob/dbsqls/torun/ESHSC-H1ES/test.sql
BEGIN
INSERT 132600232 1
INSERT 132600233 1
psql:/home/database/bob/dbsqls/torun/ESHSC-H1ES/test.sql:4:
ERROR: more than one row returned by a subquery used
as an expression
psql:/home/database/bob/dbsqls/torun/ESHSC-H1ES/test.sql:5:
ERROR: current transaction is aborted, commands
ignored until end of transaction block
psql:/home/database/bob/dbsqls/torun/ESHSC-H1ES/test.sql:6:
ERROR: current transaction is aborted, commands
ignored until end of transaction block
psql:/home/database/bob/dbsqls/torun/ESHSC-H1ES/test.sql:7:
ERROR: current transaction is aborted, commands
ignored until end of transaction block
bob=> rollback;
ROLLBACK
Analysis:
First two statements got executed.
However in the third I have
currval('experiment_exp_id_seq'), why is it troubling
now when I already defined in experiment statement
above it.
can any one please suggest some solution.
thank you.
K
__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/