Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Numbering in DB2 Column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Wed Nov 19, 2008 2:30 pm    Post subject: Numbering in DB2 Column
Reply with quote

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:
Code:

100   ABCD
500   BCDE
786   CDEF....


Table has following declaration.
Code:

SER_NO     EMP_NO    EMP_NAME


When I execute with the above file with 3 records, data has to be inserted into the table as shown below.

Code:

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


Here SER_NO has to be incremented whenever a row will be inserted.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Nov 19, 2008 3:52 pm    Post subject:
Reply with quote

KSK,

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

SUSHANTH
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Wed Nov 19, 2008 4:59 pm    Post subject: Reply to: Numbering in DB2 Column
Reply with quote

Hi KSK,taking forward what Sushanth has suggested you may use the following example:
Code:
CREATE TABLE EMP (SER_NO SMALLINT GENERATED ALWAYS AS IDENTITY, EMP_NO INTEGER, EMP_NAME CHAR(6) );

INSERT INTO EMP (EMP_NO ,EMP_NAME) VALUES (1234,ABC);
INSERT INTO EMP (EMP_NO ,EMP_NAME) VALUES (5678,XYZ);

SELECT * FROM EMP;

SER_NO  EMP_NO  EMP_NAME
1            1234       ABC
2            5678       XYZ

Hope this helps.
Cris
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Wed Nov 19, 2008 5:47 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Vivek_Vasudevamurthy

New User


Joined: 19 Sep 2008
Posts: 9
Location: Bangalore,Karnataka

PostPosted: Thu Nov 20, 2008 12:22 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Thu Nov 20, 2008 1:17 pm    Post subject:
Reply with quote

Vivek ,

foe incrementing with 10 you should go like this

Code:


CREATE TABLE Tablename
   (SER_NO INTEGER NOT NULL
           GENERATED ALWAYS AS IDENTITY
           START WITH 10
           INCREMENT BY 10);
Back to top
View user's profile Send private message
Vivek_Vasudevamurthy

New User


Joined: 19 Sep 2008
Posts: 9
Location: Bangalore,Karnataka

PostPosted: Thu Nov 20, 2008 3:42 pm    Post subject:
Reply with quote

thank you Prasanth
Back to top
View user's profile Send private message
lenygold

New User


Joined: 22 Oct 2008
Posts: 3
Location: baltimore

PostPosted: Fri Nov 21, 2008 5:51 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us