Wednesday, August 14, 2019

More about SQL Sequences

sequences view, select next value, select previous value

Last week I wrote a post introducing what SQL Sequences are. Having had a chance to "play" with them some more I wanted to write about what I have discovered.

Before I go into any examples I need to have some Sequences to "play" with:

CREATE SEQUENCE MYLIB.BY_ONE
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_TEN
START WITH 10
INCREMENT BY 10
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_100
START WITH 100
INCREMENT BY 100
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_THREE
START WITH 30
INCREMENT BY 3
NO MAXVALUE
NO CYCLE ;

Wednesday, August 7, 2019

Using a SQL Sequence to renumber a column

sql sequence to provide a sequentail number to a sql statement

The title sounds a bit strange, but so is the subject of this post. There is a thing in Db2 for i that I can create called a Sequence. A Sequence uses the rules I give it to return to me a sequential value I can use to update column in a table.

I am sure it will become a bit clearer when I give my example.

I have ten students who have completed taken a course. At the end of the course the students take a test. The results of this test are contained in a SQL table I created, along with their name.