Issue
I am validating data and trying to find if there are any values in a single column (allowed_amount) with more than 2 decimal places (24.1145678, 234.444, -1234.09012).
with t1 as (
select (allowed_amount - round(allowed_amount,2)) as ck
from export_core_report_client_output
where runid = '0c7c2d34-6cc3-43b0-ae4b-4bd8f4bddfb0'
)
select min(ck) as min, max(ck) as max from t1
Solution
One option would be to use this formula:
SELECT
num,
CASE WHEN 100*num - CAST(100*num AS int) > 0 THEN 'yes' ELSE 'no' END AS field
FROM yourTable;
For example, for the value 24.1234
, the above formula computes:
2412.34 - 2412 = 0.34 > 0
But for 24.12
, we get:
2412 - 2412 = 0
Answered By - Tim Biegeleisen Answer Checked By - David Marino (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.