Increment DB Table Counter after selecting the value

One Star

Increment DB Table Counter after selecting the value

Hi,
I have a database table (say COUNTER_TABLE) which is one column and one row. This table just stores one integer number.
I have another database table (say Table_2), where the primary key is generated based on this integer value.
My requirement is as follows.
1) Increment COUNTER_TABLE value by one
2) Select the COUNTER_TABLE value and update the Table_2
I am not able to understand how to increment the counter value before every selection.
Could you please help me in this.
Thank you
Achyuth
One Star

Re: Increment DB Table Counter after selecting the value

Hi
Is there a range of COUNTER_TABLE value?
When increment the COUNTER_TABLE, will the COUNTER_TABLE update for each value?
Still tMap with look-up COUNTER_TABLE may be a workaround.
Regards
Pedro
One Star

Re: Increment DB Table Counter after selecting the value

i dont think u need that counter_table if it is just going to be used to get an integer.
You can just simply use Numeric.Sequence("s1",1,1) to increment the counter for every row that comes in for that transaction. For other times you can use the target in a tMap and fetch the max value of that table using aggregator and add Numeric.Sequence("s1",row1.primaryid,1)
One Star

Re: Increment DB Table Counter after selecting the value

Hi All,
Thank you for the reply. But it is not working for me.
This is how i am trying to achieve it. But it is not working. Please find the screen shots attached.
Thank you
Achyuth
One Star

Re: Increment DB Table Counter after selecting the value

Hi,
What is the use of the DW_SYSTEM table?
One Star

Re: Increment DB Table Counter after selecting the value

HI,
1) DW_SYSTEM is the counter table. It has only one row.
2) The primary key for the DW_DG_CONTROL is generated from the DW_SYSTEM table. (Note: In the second image attached DW_DG_CONTROL Primary key is not properly mapped which is wrong. I removed the mapping as it was not working.)
3) Select the key value from DW_SYSTEM update DW_DG_CONTROL with that key value, and then increment DW_SYSTEM key value by one.
Thank you
Achyuth
One Star

Re: Increment DB Table Counter after selecting the value

Just load the field value of your DW_SYSTEM in a context variable and as vital_parsley said, use a Numeric sequence. Numeric.sequence("s1",context.variable,1).
One Star

Re: Increment DB Table Counter after selecting the value

Hi Orel,
Thank you very much for the advice. It works perfectly for me.
I was pulled into some other work so could not concentrate on this issue for some time.
I have one more issue, Once the job is completed, I want to updated the DW_SYSTEM table with the new value.
I am not able to understand how to save the final value and update the table in the end.

Thank you
Achyuth
One Star

Re: Increment DB Table Counter after selecting the value

Hi,
I think you don't need your DW_DG_SYSTEM table if it's just to save the number of rows you have in the AccountActivity table. You should just use SELECT Max(id) from accountActivity. But if you need to keep your job's design, you can update the DW_DG_SYSTEM table in a subjob by using a tOracleInput with SELECT Max(id) from accountActivity as statement.
One Star

Re: Increment DB Table Counter after selecting the value

Hi Orel,
I think I have to maintain the DW_DG_SYSTEM table as that increment number is used in other tables too.
There is one more issue i am facing here.
Every time While reading the values from the PositionActivity table the counter is getting incremented.
But I want to increment the counter ONLY if it is an INSERT.
On UPDATE, I don't want the counter to be incremented.
DG_CONTROL operation can be insert or if record already present UPDATE.
Below is how the value is incremented in tMap
Numeric.sequence("DG_CTL_NUM_CT", context.DG_CTL_NUM_CT, 1);
Achyuth