Dividing Numbers

Mike Sheehan ●

Hi Stefan,

Thanks for reaching out. This has actually been logged by BMC themselves before and it turns out that This is related to how the values are being handled by the BMC driver (we have not been able to replicate on other dbs).

To use a simplified example, the integer 20 divided by the integer 7 will return 2. If these were treated as floats, the float 2.8 would be returned, but they're being treated as integers in BMC's driver. The SQL statement from Yellowfin is sending out 20/7 no matter what. In MySQL for instance, the SQL statement that Yellowfin sends returns 2.8571... because it recognizes these as float values. If we were now to put ".0" after these integers, then it forces the driver to recognize 20/7 as float values and return the desired result.

In terms of accomplishing this when referencing a column itself in a calculated field, considering the values are recognized as Integers by default, I'd guess this normally would be done by drafting a Freehand SQL CAST statement to convert the values to float values... Something like CAST(SUM(sl.parts) AS FLOAT), but I don't think Smart Reporting allows its users to draft Freehand SQL statements. As such, if you have further trouble with this though, please reach out to your BMC contact to assist further as they should have a better understanding of how their driver works and I'd guess they'd already know how to get around this, if possible given the Smart Reporting setup.

Please let me know if you have any further questions.

Regards,

Mike

1

Mike Sheehan ● ●

Hi Stefan,

Thanks for reaching out. This has actually been logged by BMC themselves before and it turns out that This is related to how the values are being handled by the BMC driver (we have not been able to replicate on other dbs).

To use a simplified example, the integer 20 divided by the integer 7 will return 2. If these were treated as floats, the float 2.8 would be returned, but they're being treated as integers in BMC's driver. The SQL statement from Yellowfin is sending out 20/7 no matter what. In MySQL for instance, the SQL statement that Yellowfin sends returns 2.8571... because it recognizes these as float values. If we were now to put ".0" after these integers, then it forces the driver to recognize 20/7 as float values and return the desired result.

In terms of accomplishing this when referencing a column itself in a calculated field, considering the values are recognized as Integers by default, I'd guess this normally would be done by drafting a Freehand SQL CAST statement to convert the values to float values... Something like CAST(SUM(sl.parts) AS FLOAT), but I don't think Smart Reporting allows its users to draft Freehand SQL statements. As such, if you have further trouble with this though, please reach out to your BMC contact to assist further as they should have a better understanding of how their driver works and I'd guess they'd already know how to get around this, if possible given the Smart Reporting setup.

Please let me know if you have any further questions.

Regards,

Mike

Reply URL

2

Mike Sheehan ●

Hi Stefan,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you and I suspect I've covered your query, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

Reply URL

1

Stefan ●

I'm sorry for not replaying to your post.

I've talked to BMC support and they are currently looking into it on their side, but it seems to be that they 'convert' (for the lack of better word) any division result to integer so they cut off everything after decimal point.

Thank you for your time,

Mike.

URL

1

Mike Sheehan ●

Hi Stefan,

No worries! Ah, that does seem familiar now that you mention it, but thanks for informing us!

Regards,

Mike

URL

1

Larry Beasley ●

SQL will always convert division by integer as an integer. For example:

select 5/2

Result: 2

select 5/2.00

Returns a floating point or 2.500000

Somewhere in your calculation you need to include a decimal point. In your case, you need incorporate a decimal here:

((Unutar 24 sata (ukupno)) * 1.00) / ((Ukupno Incidenata (Ukin)) * 1.00)

or you can add 0.00

SQL will always convert division by integer as an integer. For example:

select 5/2

Result: 2

select 5/2.00

Returns a floating point or 2.500000

Somewhere in your calculation you need to include a decimal point. In your case, you need incorporate a decimal here:

((Unutar 24 sata (ukupno)) * 1.00) / ((Ukupno Incidenata (Ukin)) * 1.00)

or you can add 0.00

URL

2

Mike Sheehan ●

Hi Larry,

Thanks for providing additional input. I think you may be correct about this solution. Due to the clients' restrictions, users on that system cannot script Freehand SQL statements, but it may very well be the case that their solution is to draft a Simple calculated field statement and multiply each fields' values by 1.00 to get the answer they're looking for. It may be helpful for others if you can confirm this though, Stefan!

Thanks,

Mike

URL

1

Stefan ●

I tried doing this from the beginning hopping that forcing a value (in this case value with float/double type) would make it cast to said values type.

Unfortunately this did not give a correct result so that idea was dropped and I contacted this community.

Thanks for the idea thou. :)

URL

1

Mike Sheehan ●

Hi Stefan,

Thanks for the response!

Regards,

Mike

URL

Dividing Numbers

Replies (2)