How to solve truncated incorrect integer value in mariadb SQL server

Question

0.00/5 (No votes)

See more:

i have to find the emp_name and salary is increased by 15% AND EXPRESSED AS NO.OF DOLLORS.There are two tables employees and salaries

when i entered a query
it raised an error called truncated integer value:'$99,999'

What I have tried:

i tried the following query

select concat(e.first_name," ",e.last_name)
as 'employee name',
char(s.salary*15/100,'$99,999') as 'No.Of Dollors'
from employees e
join salaries s
on e.emp_no=s.emp_no
group by e.emp_no,s.emp_no;

Posted 29-Dec-19 22:01pm

Oviya Sivakumar

Updated 29-Dec-19 22:32pm

Add a Solution

Comments

Richard MacCutchan 30-Dec-19 4:16am

I can only assume that s.salary is an integer value, so you will lose precision by multiplying by 15/100.

Oviya Sivakumar 30-Dec-19 4:45am

i hv also given format(1.15*s.salary,'$99,999') though its showing same error as truncated integer value:'$99999'

Richard MacCutchan 30-Dec-19 5:34am

If I use;

SELECT FORMAT(1.15 * 33, 'C2');

it produces the correct answer:

$37.95

Oviya Sivakumar 30-Dec-19 5:47am

when i tried the following

select concat(e.first_name," ",e.last_name)as 'employee name',format(s.salary*1.15,'C2') as 'No.Of Dollors'
from employees e
join salaries s
on e.emp_no=s.emp_no group by e.emp_no,s.emp_no;
it shows
Truncated incorrect INTEGER value: 'C2'

Richard MacCutchan 30-Dec-19 5:51am

There is no point repeating the same query when the issue must be connected to your data. Forget about the two names first, and just use a simple query to get the salary value and see what it contains. Then try to format it as a simple currency value using 'C2'. Then try applying the multiplication factor. Brute force never works, simple logical steps are the fastest way to finding a solution.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2019-12-29T22:17:00

Solution 1

Don't use char, use FORMAT: How to Format Numbers as Currency in SQL Server (T-SQL) | Database.Guide[^]

Quote:

SQL

select concat(e.first_name," ",e.last_name)as 'employee name',format(1.15*s.salary,'$99999') as 'No.Of Dollors'
from employees e
join salaries s
on e.emp_no=s.emp_no group by e.emp_no,s.emp_no;

When you are given a link to documentation, read what it says fully...
'$99999' is not a valid format.
When I try FORMAT, it works for me:

SQL

DECLARE @SALARY INT = 1000
SELECT FORMAT (@SALARY * 1.15, '$##,###', 'EN-US')

So try that in your SQL, and change the type of @SALARY to match your DB.
When that works - or gives the same error, which it doesn't for me - show us the exact code that generates the problem, or transfer that into your SQL query and try it there.

I'd suspect that your DB contains "odd values" and probably that s.salary is not a numeric field.

Posted 29-Dec-19 22:17pm

OriginalGriff

Updated 29-Dec-19 23:15pm

v2

Comments

Oviya Sivakumar 30-Dec-19 4:35am

i had used format() instead of char (),though its showing same error as truncated incorrect integer value

OriginalGriff 30-Dec-19 4:49am

And how - exactly - did you use it?
Copy and paste the code.

Oviya Sivakumar 30-Dec-19 5:09am

select concat(e.first_name," ",e.last_name)as 'employee name',format(1.15*s.salary,'$99999') as 'No.Of Dollors'
from employees e
join salaries s
on e.emp_no=s.emp_no group by e.emp_no,s.emp_no;

OriginalGriff 30-Dec-19 5:15am

Answer updated.

Oviya Sivakumar 30-Dec-19 5:28am

select concat(e.first_name," ",e.last_name)as 'employee name',format(s.salary*1.15,'$99,999','EN-US') as 'No.Of Dollors'
from employees e
join salaries s
on e.emp_no=s.emp_no group by e.emp_no,s.emp_no;

though its shows same error as
Truncated incorrect INTEGER value: '$99,999'
and
Unknown locale: 'EN-US'

OriginalGriff 30-Dec-19 5:50am

Read
What
I
Said.

:sigh: