 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / April 2006Sequence number for a Batch
Hi, I have a situation where i have multiple batch and the each batch are sequence numbers. For each batch, the number should start with 1. For example: Col.no1 Col.no2 ------ ------- batch1 1 batch1 2 batch1 3 batch 2 1 batch 2 2 batch 3 1 batch4 1 ..... ..... Is there a easy way to do it in DB2 while creating a table? Identity column in DB2 simply creates numbers in sequence. Please advise Jignesh > Hi, > [quoted text clipped - 22 lines] > Please advise > Jignesh Are your batches finite? You could create a sequence/batch and write a function doing dynamic SQL to use the appropriate sequence. Otherwise you could use thr ROW_NUMBER() OVER(PARTITION BY <batchcolumn>) OLAP function possibly offset by the max number for the batch before the insert. Cheers Serge  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Hi Serge, Nopes, my batch are infinite. I created a sequence but it just increments the sequence number even after a having a new batch. Please advise. Jignesh > Are your batches finite? You could create a sequence/batch and write a > function doing dynamic SQL to use the appropriate sequence. [quoted text clipped - 5 lines] > Cheers > Serge Can someone show some example of inserting records with sequence number in a batch? regards Jignesh > Hi Serge, > [quoted text clipped - 18 lines] > > DB2 Solutions Development > > IBM Toronto Lab > Can someone show some example of inserting records with sequence number > in a batch? How does your batch processing look like. Can you paraphrase with pseudo code? SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Serge, Here's the description of table. Table: receipt_check c1: receipt_id char(11) not null , c2: receipt_seq int not null generated by default as identity (start with 1, increment with 1, .........), c3: receipt_dt timestamp, c4: receipt_user_nm varchar(35) Records in the table receipt_check looks something like below: c1 c2 --- ----- 111-22-3333 1 111-22-3333 2 111-33-4444 3 111-33-4444 4 111-33-4444 5 222-44-5555 6 I want to create a new column called c5 (for grouping c1 and c2). And the table/records should display something like below: c1 c2 c5 (new column) --- ----- -------------------- 111-22-3333 1 1 111-22-3333 2 2 111-33-4444 3 1 111-33-4444 4 2 111-33-4444 5 3 222-44-5555 6 1 Any help is greatly appreciated. regards Jignesh > Serge, > [quoted text clipped - 38 lines] > 222-44-5555 6 > 1 OK, this will populate your new column: UPDATE (SELECT c5, row_number(partition by c1 order by c2) as rn FROM receipt_check) AS U SET c5 = rn; To maintain it (I assume you only do INSERT....) you will need: CREATE TRIGGER trg1 BEFORE INSERT ON receipt_check REFERENCING NEW AS n FOR EACH ROW SET n.c5 = (SELECT COUNT(1) + 1 FROM receipt_check WHERE n.c1 = c1) assuming single row insert. For multi row insert you will need to replace +1 with the scratchpad counter function in sqllib/samples/c Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Hi Serge, I am little confused so let me display the entire scenario one more time. Here's real scenario. I have table called 'validation' in one database. And the columns are as follows: receipt_id char(11) not null, receipt_seq integer not null, payee_num char(5), payment_amt decimal(16), email_addr varchar(35), payee_addr1 varchar(35), payee_addr2 varchar(35), payee_addr3 varchar(30) The table validation already has data and it looks something like this: receipt_id receipt_seq payee_num ------------- --------------- ------------- 001100AB 1 12341 001100AB 1 23232 001100AB 1 65432 001100BA 1 23424 001100BA 1 64352 001100BB 1 23627 001100BB 1 90201 002130CC 1 89292 002530DA 1 67782 Now, i wanted to load the data from table validation into a new table called validation_new with new column (receipt_num) in the new table but in another database. The new column (receipt_num) in a new table (validation_new) will look like this: receipt_id char(11) not null, receipt_seq integer not null, receipt_num integer not null, payee_num char(5), payment_amt decimal(16), email_addr varchar(35), payee_addr1 varchar(35), payee_addr2 varchar(35), payee_addr3 varchar(30) And the record should look something like this: receipt_id receipt_seq receipt_num payee_num ------------- --------------- --------------- ------------- 001100AB 1 1 12341 001100AB 1 2 23232 001100AB 1 3 65432 001100BA 1 1 23424 001100BA 1 2 64352 001100BB 1 1 23627 001100BB 1 2 90201 002130CC 1 1 89292 002530DA 1 1 67782 So, how to load the data from the flat file with new receipt_num? Any help would be of greatly appreciated. Thanks Jignesh > > Serge, > > [quoted text clipped - 53 lines] > Cheers > Serge Simply import into the new table and set the column to a default value. Then run the UPDATE statement I provided to populate the column. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Hi Serge, Appreciate all your kind replies. It looks simple, but still i m facing some problem while importing data. >From the previous mail, I have created a new column with 'default 0'. While i import from flat file (of del), the first row is getting inserted, while other records are rejected by violating primary key constraints and getting SQL0803N. Any idea wat is causing this? thanks Jignesh > Simply import into the new table and set the column to a default value. > Then run the UPDATE statement I provided to populate the column. > > Cheers > Serge > Hi Serge, > [quoted text clipped - 7 lines] > > Any idea wat is causing this? I assume your new PK includes teh new column? Of course that won't work until teh update is doen. So, leave the PK away, import, update, then ALTER TABLE ADD PRIMARY KEY. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab altast, it work the way i want. Here's what i did. 1.Created a temp table. 2.loaded the data into the temp table. 3. exported the data from the temp table with rownumber () over ... 4. created new table with the new table. (without the PK) 5. loaded the data into the new table with the selected column (leaving all not null columns) 6. updated the new column (receipt_num) with the update with partition by and order by. 7. created pk. Thanks for your help. Regards Jignesh Will the batches ever have items removed? If they are, will they need reordering? You may need a FUNCTION to reorder everything for you. If gaps in the sequence to not matter, a SEQEUNCE can be used. B. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.