select from a sequence and from a table in the same statement

gimme_this_gimme_that

The following statement doesn't work and I need a tip.

Here is a draft:

This selects data from the z table.
And fetches the next value for r_id_seq and creates a table named b.
select z.r_name,b.r_id from
(select r_name from z.r_lookup where r_id=821 ) z
right outer join values
(select nextval for r_id_seq from sysibm.sysdummy1) as b(r_id)

There is nothing to select on in a ON clause, there is a single tuple
in z and in b, and both are returned.

Here is where I'm going with this:

insert into new_table (r_name,r_id) values (

select z.r_name,b.r_id from
(select r_name from z.r_lookup where r_id=821 ) z
right outer join values
(select nextval for r_id_seq from sysibm.sysdummy1) as b(r_id)

)

Jul 12 '07 #1

Serge Rielau

gi*******************@yahoo.com wrote:

insert into new_table (r_name,r_id) values (

select z.r_name,b.r_id from
(select r_name from z.r_lookup where r_id=821 ) z
right outer join values
(select nextval for r_id_seq from sysibm.sysdummy1) as b(r_id)

)

INSERT INTO news_table(r_name,r_id)
SELECT r_name, next value for r_id_seq from z.r_lookup where r_id=821

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Jul 12 '07 #2

gimme_this_gimme_that

Works great.

Thanks Serge.

Jul 12 '07 #3