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?

thanks

Answers


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.


Need Your Help

EXC_BAD_ACCESS when loading UIImage in Swift

ios swift uiimage exc-bad-access

I'm quite new to the iOS platform, but am already an experimented Android dev.

Java formatter in Eclipse - change java.lang.Object references to Object, add import statement as necessary

java eclipse formatting rad

I have a lot of generated code (classes from a WSDL) that I would like to clean up. There are references like java.lang.Object and the.very.same.namespace.as.TheGeneratedClass all over. Is there ...