Integer function in snowsql

Question

I am trying to run this query in snowflakes, but I keep having the error below. Can someone please tell me what I am doing wrong. I am trying to get the integer value for the ages.

SELECT DISTINCT Target_quikvalf.MPOLICY
FROM Target_quikvalf
WHERE (((Target_quikvalf.MPOLICY) Like '%N') 
   AND ("DATE" (Target_quikvalf.MISSUE) Between '5/1/2020' And '5/31/2020')
   AND (as_integer((Target_quikvalf.MAGE) / 5)*5))


Error Message:
SQL compilation error: invalid type [FLOAT] for parameter 'AS_INTEGER(variantValue...)'

Answer 1

The placement of where you are applying the function is the issue. Here we are converting the data type before applying the mathematical calculations.

You'll need to cast the FLOAT to INT can cast it like so:

SELECT DISTINCT Target_quikvalf.MPOLICY
FROM Target_quikvalf
WHERE (((Target_quikvalf.MPOLICY) Like '%N') 
AND ("DATE" (Target_quikvalf.MISSUE) Between '5/1/2020' AND '5/31/2020') 
AND ((cast(Target_quikvalf.MAGE as int) / 5) * 5);

As an alternative, we can try converting the FLOAT to a number using TO_NUMBER. If Target_quikvalf.MAGE column was a variant data type, then you'd be able to run it like so (in the example below, we are forcing the converted float number to not have a tenths place and can support ages below 1000 since people can be over 100 years old):

SELECT DISTINCT Target_quikvalf.MPOLICY
FROM Target_quikvalf
WHERE (((Target_quikvalf.MPOLICY) Like '%N') 
AND ("DATE" (Target_quikvalf.MISSUE) Between '5/1/2020' AND '5/31/2020') 
AND ((to_number(Target_quikvalf.MAGE, 3, 0) / 5) * 5);

Answer 2

SELECT as_integer(5.1);

generates

SQL compilation error: invalid type [NUMBER(2,1)] for parameter 'AS_INTEGER(variantValue...)'

so don't use a function that is intended to "parsing variant data" to truncate you number, instead

cast

SELECT 5.1::int;

gives:

5.1::INT
5

given you are not getting the value scaled as you expect, let break it down:

SELECT 68 as age
    , age/5 as float_scale
    , float_scale::int as cast_age
    , round(float_scale,0) as round_age
    , trunc(float_scale,0) as trunc_age
    , trunc_age * 5 as scaled_back;

gives:

AGE FLOAT_SCALE CAST_AGE    ROUND_AGE   TRUNC_AGE   SCALED_BACK
68  13.600000   14          14          13          65

casting, and rounding appear to not be what you want, thus TRUNC is the key here

thus your code should be

(TRUNC(Target_quikvalf.MAGE / 5) *5)