The next version of Grooper - Grooper 2021 - will be entering beta soon! If you want to get a head start on some of our exciting new features, check out the  article over Smart PDFs  on the Grooper Wiki!

Data Validation: Rounding values in an SQL query

kylesouzakylesouza Posts: 146 ✭✭
edited November 2020 in The Astronauts (Q&A)
I am trying to find a value in a table where, once it is rounded rounded, matches an extracted value, once it has been rounded as well.  I cannot simply change my Data Column Value Type to d2, because the commas cause an error since there are no commas in the lookup table AND I need the full value of the UNIT PRICE cell for a different calculation.  I tried a few things, the closest of which I think is:
SELECT * FROM I4L_MasterPriceList WHERE ([Item Number]=@ITEM)AND (ROUND([Price 1], 2)=ROUND(@UNIT_PRICE, 2))

As a workaround I tried creating a second column, ROUNDED PRICE, where I used a calculated expression to round the value from UNIT PRICE to the second decimal, and changing my SQL statement to:
SELECT * FROM I4L_MasterPriceList WHERE [Item Number]=@ITEMAND ROUND([Price 1], 2)=@ROUNDED_PRICE
I'm Still getting a "Lookup produced no results." issue flag though.

I may be able to add another column to the table with a rounded value.  But before I do, is there a better way than adding columns in the table and in Grooper?  I feel like there must be...

UPDATE:  As a work around, I added a column to my Grooper table that retrieves the rounded value of [Price 1] from the database table and am comparing that to the value of ROUNDED PRICE.  I could hide those columns, but then how do I see when there are errors?
Kyle Souza
Data Wizard
P&P Oil & Gas Solutions


Sign In or Register to comment.