Set based rounding

(0) Share

Report

Posted on by Greg's Mom

Does anyone know a way I can round values using set based operations like insert_recordset or update_recordset?

I have decimal values stored in one table. I need to pull them into another table either as integers or rounded to the whole dollar. If I assign the decimal value to the int field in an insert_recordset, I get compiler warnings that the assignment causes loss of precision. As far as I can tell, insert_recordset and update_recordset do not support method calls so that I can properly round these decimal values to integers. Is there any set based way to deal with this problem? I'd love to avoid looping through every record because I have a ton of records to process and row by row is going to be very slow.

Any suggestion would be greatly appreciated!

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Israel Gonzalez 733 on at

Like (1)

Report

You can use decRound function (msdn.microsoft.com/.../aa499511.aspx) and later convert to int

Was this reply helpful? Yes No
Greg's Mom on at

Like (0)

Report

Thanks for the suggestion, Israel. Can you give an example using insert_recordset or update_recordset? I am not aware of a way to use decRound in these set-based operations. Using while select forupdate, I could use decRound but I'm looking for an approach to round a very large set of data.

Was this reply helpful? Yes No
AX 2012 r3 2,426 on at

Like (0)

Report

update_recordset table setting table.field = decround(decimalfield,0)

join anothertable....

Was this reply helpful? Yes No
Greg's Mom on at

Like (0)

Report

Thanks, Sukrut. I have been researching and trying a variety of approaches and I think this is my best option. I have to loop through the records which is a performance hit but using recordinsertlist helps to make the database transaction very fast.

Was this reply helpful? Yes No
Greg's Mom on at

Like (0)

Report

[quote user="AX 2012 r3"]

update_recordset table setting table.field = decround(decimalfield,0)

join anothertable....

[/quote]

As far as I know update_recordset does not support the use of functions like this. The compiler gives me an error: Illegal use of SETTING expression.

Was this reply helpful? Yes No