Going to be answering my own question here, but I wanted to post this to get some attention in case anyone else ever comes across this.
Within the BusinessObjects Data Services Designer, I have two decimal(36, 10) values, and I'd like to divide one by the other. In order to account for divide-by-zero situations, I have to first check if the denominator is zero, so I end up with an ifthenelse
statement like the following:
ifthenelse(Query.Denominator = 0, 0, Query.Numerator / Query.Denominator)
When I execute my job, however, I end up always getting 0 or 1, rather than a decimal value.
An ifthenelse
statement is interesting, in that it takes its data type from the second parameter. In the code example above, it assumes the correct data type is the data type of the parameter "0", which happens to be an integer.
When the ifthenelse
receives a value from the "else" part, it converts it to an integer. So a value of 0.356 becomes 0 and 0.576 becomes 1.
The trick is to cast the "0" to a decimal(36, 10):
ifthenelse(Query.Denominator = 0, cast(0, 'Decimal(36, 10)'), Query.Numerator / Query.Denominator)