SQL Casting invalid data type

0
votes

I have the following tables:

EMPLID=INTEGER, F_NAME=VARCHAR, M_NAME=VARCHAR, SALARY_GRADE_ID=VARCHAR

Employee Table

GRADE_ID=VARCHAR, GRADE_AMOUNT=INTEGER

Salary Table

My query:

SELECT L_Name, Grade_Amount, CAST(AVG(Grade_Amount) AS DECIMAL(0,2)) AS "Total by Last Name" FROM 
Employee INNER JOIN Salary_Grades ON Employee.Salary_Grade_id = Salary_Grades.Grade_Id 
GROUP BY L_Name, Grade_Amount;

Is met with the error:

SQL compilation error: invalid data type specification (2>0)

When I set the decimal specification to (2,2), the query generates the error

Number out of representable range: type FIXED[SB1]{nullable}, value 70000.000000

What is actually happening here? What is wrong with the CAST() statement?

1
sqlsnowflake-cloud-data-platform

1 Answers

3
votes

The definition of decimal has two components:

  • "precision" is the total number of decimal places in the number.
  • "scale" is the number to right of the decimal point.

With a precision of 0, you are saying no number.

If you only want two digits to the right of the decimal point, use decimal(2, 2).

That seems dangerous to me. You cannot represent 1.00, which might be a possible value. So the most conservation I would use is decimal(3, 2) for your specific query.

Based on your error, you probably want at least decimal(9, 4). But why bother with counting? Just use something big enough like (20, 4).