February 5, 2013 at 2:12 pm
I get this error when using this function:
CREATE FUNCTION dbo.[PSBPR_FormatData] (@DataValue DECIMAL(15,4),
@DataType NCHAR(1))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @FormattedData VARCHAR(20)
SELECT @FormattedData = CASE WHEN @DataType = 'B' --Currency in Billions
THEN '$' + SPACE(7 - LEN(@DataValue)) + @DataValue
WHEN @DataType = 'C' --Currency
THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR(20),@DataValue,1),1, PATINDEX('%.%',CONVERT(VARCHAR(20),@DataValue,1))-1)))
+ SUBSTRING(CONVERT(VARCHAR(20),@DataValue,1),1, PATINDEX('%.%',CONVERT(VARCHAR(20),@DataValue,1))-1)
--WHEN @DataType = 'N' --Number
--THEN SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
--WHEN @DataType = 'P' --Percentage
--THEN CONVERT(VARCHAR,CAST(100.0 * @DataValue AS DECIMAL(10,1))) + '%'
--WHEN @DataType = 'T' --Currency in Thousands
--THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)))
-- + SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
ELSE CONVERT(VARCHAR,@DataValue) --'D' = Days, 'H' = 'Hours,
END
RETURN @FormattedData
END
I know the issue occurs when @DataType = 'C' (probably in other types as well, but if I can get 'C' working...)
I pulled the offending code in the function out and put it in a query:
SELECT '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1))-1)))
+ SUBSTRING(CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1))-1) FormattedTarget,
CONVERT(DECIMAL(15,4),[Target]) ConvertedTarget, [Target] ActualTarget
FROM PSBPR_Tree T
WHERE CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')
THEN 'NOT'
ELSE T.Target
END != 'NOT'
AND DataType = 'C'
The code works as desired in the query:
FormattedTargetConvertedTargetActualTarget
$ 500500.0000500
$ 500500.0000500
$ 2,0002000.00002000
I have limited what gets passed to the function to the exact same values as the above query:
SELECT CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')
THEN 'NOT'
ELSE dbo.PSBPR_FormatData(T.Target, T.DataType)
END FormattedTarget
FROM PSBPR_Tree T
WHERE CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')
THEN 'NOT'
ELSE T.Target
END != 'NOT'
AND DataType = 'C'
and get the error message: Error converting data type varchar to numeric
I have altered the function to verify the [Target] values pass to the function and implicitly convert to DECIMAL(15,4).
Any help will be much appreciated.
February 5, 2013 at 4:09 pm
Code below should take care of the 'C' type.
The 'B' type is attempting to concatenate chars and numerics, which will always cause a conversion error, so it definitely needs changed too. I don't know what format you wanted for Billions so I didn't change that code.
CREATE FUNCTION dbo.[PSBPR_FormatData] (@DataValue DECIMAL(15,4),
@DataType NCHAR(1))
RETURNS VARCHAR(20)
AS
BEGIN
RETURN (
SELECT
CASE WHEN @DataType = 'B' --Currency in Billions
THEN '$' + SPACE(7 - LEN(@DataValue)) + @DataValue --<<--!! will always cause conversion error!!
WHEN @DataType = 'C' --Currency
THEN '$' + RIGHT(SPACE(10) + LEFT(CONVERT(varchar(20),CAST(@DataValue AS money),1), LEN(CONVERT(varchar(20),CAST(@DataValue AS money),1)) - 3), 10)
ELSE CONVERT(VARCHAR,@DataValue) --'D' = Days, 'H' = 'Hours,
--WHEN @DataType = 'N' --Number
--THEN SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
--WHEN @DataType = 'P' --Percentage
--THEN CONVERT(VARCHAR,CAST(100.0 * @DataValue AS DECIMAL(10,1))) + '%'
--WHEN @DataType = 'T' --Currency in Thousands
--THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)))
-- + SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
END
)
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 6, 2013 at 5:29 am
Thanks for your response.
I made the change you suggested and still get the conversion error.
February 6, 2013 at 5:43 am
T.Target isn't one of the numeric datatypes. You're only trapping one string value from being bassed in to the function, are you sure there are not more? Have you tried declaring the @DataValue parameter as VARCHAR and testing/converting within the function?
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
February 6, 2013 at 5:52 am
ChrisM@Work (2/6/2013)
T.Target isn't one of the numeric datatypes. You're only trapping one string value from being bassed in to the function, are you sure there are not more? Have you tried declaring the @DataValue parameter as VARCHAR and testing/converting within the function?
Thanks for your response.
T.Target gets converted to decimal(15,4) when passed to the function.
I have verified this conversion happens by modifying the function to give back the result without doing any work:
SELECT @FormattedData = @DataValue
Not sure what you mean by only trapping one string value. The values being passed result in an error in the function, but not in the query.
Yes, I have tried declaring @DataValue as VARCHAR.
February 6, 2013 at 6:01 am
DougG (2/6/2013)
ChrisM@Work (2/6/2013)
T.Target isn't one of the numeric datatypes. You're only trapping one string value from being bassed in to the function, are you sure there are not more? Have you tried declaring the @DataValue parameter as VARCHAR and testing/converting within the function?Thanks for your response.
T.Target gets converted to decimal(15,4) when passed to the function.
I have verified this conversion happens by modifying the function to give back the result without doing any work:
SELECT @FormattedData = @DataValue
Not sure what you mean by only trapping one string value. The values being passed result in an error in the function, but not in the query.
Yes, I have tried declaring @DataValue as VARCHAR.
CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')
If column T.Target has values of 'TBD', what other character data does it contain which cannot be implicitly converted to decimal(15,4) - and would raise the error message you are seeing?
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
February 6, 2013 at 6:06 am
ChrisM@Work (2/6/2013)
CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')If column T.Target has values of 'TBD', what other character data does it contain which cannot be implicitly converted to decimal(15,4) - and would raise the error message you are seeing?
None, but I have also limited what gets passed to the function and query to the three values: 500, 500, 2000
February 6, 2013 at 6:20 am
Try using APPLY for prototyping this. When you're done, converting to an inline function is little more than copy & paste:
SELECT
t.*,
x.*,
y.*,
z.*
FROM PSBPR_Tree t
CROSS APPLY (
SELECT
DataValue = CAST(t.[Target] AS DECIMAL(15,4)),
DataType = 'C'
) x
CROSS APPLY (
SELECT vcDataValue = CONVERT(VARCHAR(20),x.DataValue,1)
) y
CROSS APPLY (
SELECT FormattedData = CASE
WHEN x.DataType = 'B' --Currency in Billions
THEN '$' + SPACE(7 - LEN(x.DataValue)) + x.DataValue
WHEN x.DataType = 'C' --Currency
THEN '$' + SPACE(10 - LEN(SUBSTRING(vcDataValue,1, PATINDEX('%.%',vcDataValue)-1)))
+ SUBSTRING(vcDataValue,1, PATINDEX('%.%',vcDataValue)-1)
--WHEN @DataType = 'N' --Number
--THEN SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
--WHEN @DataType = 'P' --Percentage
--THEN CONVERT(VARCHAR,CAST(100.0 * @DataValue AS DECIMAL(10,1))) + '%'
--WHEN @DataType = 'T' --Currency in Thousands
--THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)))
-- + SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
ELSE CONVERT(VARCHAR,DataValue) --'D' = Days, 'H' = 'Hours,
END
) z
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
February 6, 2013 at 6:35 am
No good.
Same error on the final cross apply.
When I comment out the last cross apply, I do get results for t, x, and y.
February 6, 2013 at 6:39 am
DougG (2/6/2013)
No good.Same error on the final cross apply.
When I comment out the last cross apply, I do get results for t, x, and y.
Have another read of Scott's post.
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
February 6, 2013 at 6:44 am
ChrisM@Work (2/6/2013)
Have another read of Scott's post.
I tried his suggested format in your code as well.
February 6, 2013 at 7:01 am
Like this?
SELECT
t.*,
x.*,
y.*,
z.*
FROM (SELECT Target = 100) t
CROSS APPLY (
SELECT
DataValue = CAST(t.[Target] AS DECIMAL(15,4)),
DataType = 'C'
) x
CROSS APPLY (
SELECT vcDataValue = CONVERT(VARCHAR(20),x.DataValue,1)
) y
CROSS APPLY (
SELECT FormattedData = CASE
WHEN x.DataType = 'B' --Currency in Billions
THEN '$' + SPACE(7 - LEN(x.DataValue)) + CAST(x.DataValue AS VARCHAR(20))
WHEN x.DataType = 'C' --Currency
THEN '$' + SPACE(10 - LEN(SUBSTRING(vcDataValue,1, PATINDEX('%.%',vcDataValue)-1)))
+ SUBSTRING(vcDataValue,1, PATINDEX('%.%',vcDataValue)-1)
--WHEN @DataType = 'N' --Number
--THEN SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
--WHEN @DataType = 'P' --Percentage
--THEN CONVERT(VARCHAR,CAST(100.0 * @DataValue AS DECIMAL(10,1))) + '%'
--WHEN @DataType = 'T' --Currency in Thousands
--THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)))
-- + SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
ELSE CONVERT(VARCHAR,DataValue) --'D' = Days, 'H' = 'Hours,
END
) z
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
February 6, 2013 at 7:42 am
Okay, so maybe I am a little dense.
I guess what I missed from Scott's post was that all of the "THEN"s will be evaluated even if it does not pass the "WHEN" or
if a previous WHEN evaluated TRUE.
First, I reordered the CASE statement placing WHEN @DataType = 'C' ahead of WHEN @DataType = 'B': No change
Then, I commented out the WHEN @DataType = 'B' so all I was left with was WHEN @DataType = 'C' and ELSE: voila, no error.
Seems a bit silly, but it is what it is.
Thanks to both of you for your help.
February 7, 2013 at 1:24 am
Hi there
I had this frustrationg error on a query as well, all though the dataset I was using for a join didnt even contain anything other than numbers.
I got it solved by using WHERE ISNUMERIC(<value>) = 1
Don't know if it would help in your case, but it might be worth a shot.
nano
February 7, 2013 at 1:36 am
DougG (2/6/2013)
...I guess what I missed from Scott's post was that all of the "THEN"s will be evaluated even if it does not pass the "WHEN" orif a previous WHEN evaluated TRUE....
This error is raised by the query parser, before any evaluation of 'THEN's
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply