May 3, 2013 at 12:08 am
Hi All,
I am trying to sum of amount (data type varchar) column but I am getting error
error converting data type varchar to float.
when I checked the data very carefully I found that there is an amount like C000214534. But when I am removing that varchar amount I am not getting error
select sum(CONVERT(float, ft.amount)) FROM filetransaction ft WHERE ft.fileid <>332 this query gives me the result.
is there any way in sql so that it can sum removing varchar? I do not want to update as everyday the data is inserted from the client's end.
How can I sum those data except the data where varchar type.
Please help!!
May 3, 2013 at 3:07 am
SELECT
SUM(x.FloatAmount)
FROM filetransaction ft
CROSS APPLY (
SELECT FloatAmount = CASE
WHEN ISNUMERIC(ft.amount) = 1 THEN CAST(ft.amount AS FLOAT)
ELSE 0 END
) x
WHERE 1 = 1
NOTE: ISNUMERIC has limitations. Here's[/url] a good reference with workarounds.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 3, 2013 at 3:14 am
Chris solution will work 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 3, 2013 at 3:15 am
Hi
SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
May 3, 2013 at 3:20 am
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 3, 2013 at 3:50 am
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
Filters in queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
Igor Micev,My blog: www.igormicev.com
May 3, 2013 at 3:52 am
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
What is the source for this statement? It's completely wrong.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 3, 2013 at 3:56 am
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
What is the source for this statement? It's completely wrong.
Aham
I now see. It should be:
SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ISNUMERIC(ft.amount)=1
I copied ft.fileid <>332 mistakenly.
Igor Micev,My blog: www.igormicev.com
May 3, 2013 at 3:59 am
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
What is the source for this statement? It's completely wrong.
Aham
I now see. It should be:
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ISNUMERIC(ft.amount)=1
I copied ft.fileid <>332 mistakenly.
Do you want to include the rows where ft.amount cannot be converted to a numeric datatype?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 3, 2013 at 4:01 am
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
What is the source for this statement? It's completely wrong.
Aham
I now see. It should be:
SELECT sum(CONVERT(float, ft.amount))FROM filetransaction ft
WHERE ISNUMERIC(ft.amount)=1
I copied ft.fileid <>332 mistakenly.
Do you want to include the rows where ft.amount cannot be converted to a numeric datatype?
No, but I posted "Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/"
Igor Micev,My blog: www.igormicev.com
May 3, 2013 at 4:03 am
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
<<snip>>Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
What is the source for this statement? It's completely wrong.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 3, 2013 at 4:34 am
Thank u all for your awesome output.
It works fine for me. But the same query when I am pasting in SP it shows me the error
SELECT file_id as bacs_id,
CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
(SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1)
it shows me the error
Msg 8114, Level 16, State 5, Procedure bacs_list, Line 17
Error converting data type varchar to float.
but when I am removing CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id) this portion it is giving me the output. I can not remove exists function
Could u please guide me?
May 3, 2013 at 4:34 am
ChrisM@Work (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
<<snip>>Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
What is the source for this statement? It's completely wrong.
Hi
I admit the source was not good. I examined the issue in more details and yours code
does not works. It reports an converting type of error
SELECT
SUM(x.FloatAmount)
FROM filetransaction ft
CROSS APPLY (
SELECT FloatAmount = CASE
WHEN ISNUMERIC(ft.amount) = 1 THEN CAST(ft.amount AS FLOAT)
ELSE 0 END
) x
WHERE 1 = 1
This works.
SELECT sum(convert(float,ft.amount))
FROM filetransaction ft
WHERE ISNUMERIC(rtrim(ltrim(ft.amount)))=1 and ft.amount not like '%['','',''$'']%'
I used http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
Igor Micev,My blog: www.igormicev.com
May 3, 2013 at 5:12 am
niladri.primalink (5/3/2013)
Thank u all for your awesome output.It works fine for me. But the same query when I am pasting in SP it shows me the error
SELECT file_id as bacs_id,
CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
(SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1)
it shows me the error
Msg 8114, Level 16, State 5, Procedure bacs_list, Line 17
Error converting data type varchar to float.
but when I am removing CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id) this portion it is giving me the output. I can not remove exists function
Could u please guide me?
Can you post the whole query please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 3, 2013 at 8:08 am
ya sure
USE [Kingethelbert]
GO
/****** Object: StoredProcedure [dbo].[bacs_list] Script Date: 05/03/2013 11:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC bacs_list
ALTER PROCEDURE [dbo].[bacs_list]
@company_idINT,
@statusINT,
@INT_USERIDINT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @INT_VIEW_OWN_FILE_ONLY INT = 0
SELECT @INT_VIEW_OWN_FILE_ONLY = view_own_file_only FROM WHERE USER_ID = @INT_USERID
-- *********** Added status column **********-------
IF (@INT_VIEW_OWN_FILE_ONLY = 0)
BEGIN
SELECT file_id as bacs_id,
listname NAME,
filename ,
filetype,
ISNULL((SELECT USER_NAME FROM userlogin WHERE user_id=createdby),'')created_by,
createdby,
validatestatus Modulas_Valid,
CONVERT(VARCHAR(15), createddate, 103) create_date,
CONVERT(DATE,createddate,103) AS Createdt,
(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status
,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
(SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1)
--CAST((SELECT COALESCE(SUM(CONVERT(float, ft.amount))/100.00,0.00) FROM filetransaction ft
--WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('99', 'Z4', 'Z5') AND ISNUMERIC(ft.amount ) = 1) AS DECIMAL(13,2))
ELSE
CAST((SELECT COALESCE(SUM(CONVERT(float, FPSTemp.amount))/100.00,0.00) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code
WHERE FPSTemp.fileid= fb.file_id AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('99', 'Z4', 'Z5')) AS DECIMAL(13,2)) END totcreditamount
,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
CAST((SELECT COALESCE(SUM(CONVERT(float, ft.amount))/100.00,0.00) FROM filetransaction ft
WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('01', '17', '18', '19') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))
ELSE
CAST((SELECT COALESCE(SUM(CONVERT(float, FPSTemp.amount))/100.00,0.00) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code WHERE FPSTemp.fileid= fb.file_id
AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('01', '17', '18', '19')) AS DECIMAL(13,2)) END totdebitamount
FROM filebasic fb WHERE company_id=@company_id AND status=0
ORDER BY file_id DESC
END
ELSE
BEGIN
SELECTfile_id AS bacs_id,
listname NAME,
filename ,
filetype,
ISNULL((SELECT USER_NAME FROM userlogin WHERE user_id=createdby),'')created_by,
createdby,
validatestatus Modulas_Valid,
CONVERT(VARCHAR(15), createddate, 103) create_date,
CONVERT(DATE,createddate,103) AS Createdt,
(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status
,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN CAST((SELECT SUM(CONVERT(FLOAT, ft.amount))/100.00 FROM filetransaction ft
WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('99', 'Z4', 'Z5') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))
ELSE
(SELECT ROUND(SUM(CONVERT(BIGINT, FPSTemp.amount))/100,2) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code
WHERE FPSTemp.fileid= fb.file_id AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('99', 'Z4', 'Z5')) END totcreditamount
,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN CAST((SELECT SUM(CONVERT(FLOAT, ft.amount))/100.00 FROM filetransaction ft
WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('01', '17', '18', '19') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))
ELSE
CAST((SELECT SUM(CONVERT(BIGINT, FPSTemp.amount))/100.00 FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code WHERE FPSTemp.fileid= fb.file_id
AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('01', '17', '18', '19')) AS DECIMAL(13,2)) END totdebitamount
FROM filebasic fb WHERE company_id=@company_id AND status=0 AND createdby = @INT_USERID
ORDER BY file_id DESC
END
END
this is an SP. and when I ran the sp like
exec bacs_list 1,0,175 then it shows me the error.
Please help!
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply