By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,752 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
Works great.

Thanks Serge.
Jul 12 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.