Numbering in DB2 Column

ksk · Posted: Wed Nov 19, 2008 2:30 pm

Hi,

I have to insert some records into table from the file using COBOL-DB2 program.

can we display number of records in a column in the table using the same SQL insert query?

E.g,

I have 3 fields SER_NO, EMP_NO and EMP_NAME in table and load file has only EMP-NO and EMP-NAME fileds. When I will insert a row into table it has to increase the number and insert into SER_NO column dynamically. Is there any function to perform this?

I/P file:

sushanth bobby · Posted: Wed Nov 19, 2008 3:52 pm

KSK,

You have add column as ROWID or IDENTITY COLUMN. These are the options i think.

SUSHANTH

Cristopher · New User Joined: 31 Jul 2008 Posts: 53 Location: NY

Hi KSK,taking forward what Sushanth has suggested you may use the following example:

ksk · Posted: Wed Nov 19, 2008 5:47 pm

Sushanth/Cris,

Thanks for the information. As I don't have permission to create table, I will ask our DBA to do the same and let you know the results once tested.

Vivek_Vasudevamurthy · Posted: Thu Nov 20, 2008 12:22 pm

hi Sushanth/Cris,

if i want ser_no to incremented by 10 then whats the syntax and if i want that to start with some particular value

for exp

SER_NO ABC XYZ
10 aaaaaa bbbbbbb
20 cccccc ddddddd
30 eeeee fffffffff

like this

first it should start with 10 and it should keep on incrementing be 10 every time

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

Vivek ,

foe incrementing with 10 you should go like this

Vivek_Vasudevamurthy · Posted: Thu Nov 20, 2008 3:42 pm

thank you Prasanth

lenygold · New User Joined: 22 Oct 2008 Posts: 3 Location: baltimore

HOW TO CREATE ROW NUMBERS WITHOUT OLAP,AND ALTER IDENTITY COLUMN USING CTE:

CREATE TABLE TEST_EMPL
(SER_NO INTEGER,
EMP_NO INTEGER,
EMP_NAME CHAR(4));

Here SER_NO has to be incremented whenever a row will be inserted

FIRST INSERT WITH ZERO IN SER_NO COLUMN
2ND RANK THE TABLE USING CTE

1ST RUN:

INSERT INTO TEST_EMPL
VALUES (0,100, 'ABCD'),
(0,500, 'BCDE'),
(0,786, 'CDEF');

SELECT * FROM TEST_EMPL

SER_NO EMP_NO EMP_NAME
----------- ----------- --------
0 100 ABCD
0 500 BCDE
0 786 CDEF

3 record(s) selected.

INSERT INTO TEST_EMPL
WITH T1 (SER_NO,EMP_NO,EMP_NAME) AS
(SELECT * FROM TEST_EMPL
WHERE SER_NO = 0)
SELECT RN + (SELECT MAX(SER_NO) FROM TEST_EMPL),EMP_NO,EMP_NAME FROM T1 A,
TABLE(SELECT COUNT(*) + 1 AS RN FROM T1 B
WHERE A.EMP_NO > B.EMP_NO) ;

DELETE FROM TEST_EMPL
WHERE SER_NO = 0;
SELECT * FROM TEST_EMPL;

SER_NO EMP_NO EMP_NAME
----------- ----------- --------
1 100 ABCD
2 500 BCDE
3 786 CDEF

3 record(s) selected.

2ND RUN:

4 MORE ROWS

INSERT INTO TEST_EMPL
VALUES(0,200, 'AAAA'),
(0,600, 'BBBB'),
(0,890, 'CCCC'),
(0,900, 'DDDD');

INSERT INTO TEST_EMPL
WITH T1 (SER_NO,EMP_NO,EMP_NAME) AS
(SELECT * FROM TEST_EMPL
WHERE SER_NO = 0)
SELECT RN + (SELECT MAX(SER_NO) FROM TEST_EMPL),EMP_NO,EMP_NAME FROM T1 A,
TABLE(SELECT COUNT(*) + 1 AS RN FROM T1 B
WHERE A.EMP_NO > B.EMP_NO) ;

DELETE FROM TEST_EMPL
WHERE SER_NO = 0;
SELECT * FROM TEST_EMPL;

SER_NO EMP_NO EMP_NAME
----------- ----------- --------
1 100 ABCD
2 500 BCDE
3 786 CDEF
4 200 AAAA
5 600 BBBB
6 890 CCCC
7 900 DDDD

7 record(s) selected.