Tuesday, May 12, 2009

Using Decode in Hibernate

I needed to decode the values from a couple of columns in a row and compute a new value as a formula property for that row.
I found this to be very simple to do, I added the following as the formula for the required column:

decode(forecast_end_month, null, 0, forecast_end_year, null, 0, end_month, null, 0, end_year, null, 0, months_between(to_date(forecast_end_month || '/' || forecast_end_year, 'mm/yyyy'), to_date(end_month || '/' || end_year, 'mm/yyyy')))

So simple !

Just to give you an idea, the computed value of the new column is 0 if either of the columns forecast_end_month, forecast_end_year, end_month or end_year are null. Otherwise it finds the number of months in between the two set of dates and returns that number.


Related Posts Plugin for WordPress, Blogger...