How to make a generated column function of other columns?

[Manuel Jaen]

Hi everybody,

I'm starting up with those generated columns of the DB2, but all the examples that I come up are simple, and as far as I've read the thing I want to do is not possible. Can someone give me some light?

I have a table MPAGOS with several columns (PP, SUCUR, PR, MODIMP, FECHAE, JEFA,.....) the key in this case are PP and SUCUR columns. I would like to have a generated column (or similar) that is handle by the DB2 not by the application with row number for each value of the key columns. What I have understand is that the generated column is a numeric value that is related to the complete table, for example if the table has 35 rows, the generated column will have 35 different values despicte the keys that table will have.

Let me put a graphical example:

Currently,

PP SUCUR PR MODIMP FECHAE JEFA

345 PM_1 US 67,9 20041028 PM
345 PM_2 RG 2,01 20051214 PM
345 PM_3 FG 3,87 20061204 PM
346 M DF 2,19 20060227 M
346 H_1 RF 0,98 20060604 H
347 BI KK -0,98 20070104 BI

What I like to have is a third column named NUMSEC that is a generated colum with the secuential values for each key, as follows.

PP SUCUR NUMSEC PR MODIMP FECHAE JEFA

345 PM_1 001 US 67,9 20041028 PM
345 PM_2 002 RG 2,01 20051214 PM
345 PM_3 003 FG 3,87 20061204 PM
346 M 001 DF 2,19 20060227 M
346 H_1 002 RF 0,98 20060604 H
347 BI 001 KK -0,98 20070104 BI

The main reason to do this is because I don't trust the current data quality and also, I think the posibility of repeated keys although improbable is a posibility.


Thanks a lot to all in advance.

[kalexin]

Hi everybody,

I'm starting up with those generated columns of the DB2, but all the examples that I come up are simple, and as far as I've read the thing I want to do is not possible. Can someone give me some light?

I have a table MPAGOS with several columns (PP, SUCUR, PR, MODIMP, FECHAE, JEFA,.....) the key in this case are PP and SUCUR columns. I would like to have a generated column (or similar) that is handle by the DB2 not by the application with row number for each value of the key columns. What I have understand is that the generated column is a numeric value that is related to the complete table, for example if the table has 35 rows, the generated column will have 35 different values despicte the keys that table will have.

Let me put a graphical example:

Currently,

PP SUCUR PR MODIMP FECHAE JEFA

345 PM_1 US 67,9 20041028 PM
345 PM_2 RG 2,01 20051214 PM
345 PM_3 FG 3,87 20061204 PM
346 M DF 2,19 20060227 M
346 H_1 RF 0,98 20060604 H
347 BI KK -0,98 20070104 BI

What I like to have is a third column named NUMSEC that is a generated colum with the secuential values for each key, as follows.

PP SUCUR NUMSEC PR MODIMP FECHAE JEFA

345 PM_1 001 US 67,9 20041028 PM
345 PM_2 002 RG 2,01 20051214 PM
345 PM_3 003 FG 3,87 20061204 PM
346 M 001 DF 2,19 20060227 M
346 H_1 002 RF 0,98 20060604 H
347 BI 001 KK -0,98 20070104 BI

The main reason to do this is because I don't trust the current data quality and also, I think the posibility of repeated keys although improbable is a posibility.


Thanks a lot to all in advance.

use the rowid data type or a sequence.