How to make a function in DB2 ibm to sum fields 01 to 06 in month 5? 01-07 in month6 and so on...
We have a database that stores the bank account of our clients like this:
|client|c01|c02|c03|c04|d01|d02|d03|d04| |a |3€ |5€ |4€ |0€ |-2€|-1€|-4€| 0€|
This is the structure of the database when we are in the month 3. In month 4 would be:
|client|c01|c02|c03|c04|c05|d01|d02|d03|d04|d05| |a |3€ |5€ |4€ |2€ |0€ |-2€|-1€|-4€|-2€| 0€|
take attention on the c05 and d05.
The database auto-updates adds those columns. Because of this changing in the columns I can't get the sum of c01, c02, c03, c04, d01, d02, d03, d04 easily. I was thinking of making a function that checks the current month and makes a loop in order to select and sum those columns without errors.
If you have a better idea to do it, you are welcome. But the main question is how to make a function that is able to sum a variable number of columns?
There's something about adding a column every month that bugs me. I know it can be a valid OLAP strategy as selective de-normalization, but it just feels wierd here. Usually with these kinds of things, the entire width is specified, if for no other reason than to avoid ALTER TABLE statements. I don't know enough about what you're storing there to give a recommendation otherwise, but I guess I just prefer fully normalized structures.
Having done similar endeavours, your best bet will be to use dynamic sql. You can place that inside your stored procedure, PREPAREing and EXECUTEing as normal.