467,179 Members | 1,167 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,179 developers. It's quick & easy.

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

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
  • viewed: 1799
Share:
2 Replies
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
Works great.

Thanks Serge.
Jul 12 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by php newbie | last post: by
6 posts views Thread by Apaxe | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.