December 16, 2016 at 2:47 pm
First I want to apologize - this entire situation is the vendor's fault, and not something we can change.
I have a table where, depending on the value in one column - DSC_TYP - the value in a separate column is either an int, or a decimal value stored as an int (this is the vendor's doing!).
The specific situation I've got is converting the decimal values stored as int's back to decimal before doing math operations with them.
To give you a specific example, the value 9.95 is stored as 995 in this column. A direct conversion to decimal gives me 995.00 - which is incorrect, based on the vendor's documentation.
I had started on a rather ugly and unwieldy CASE statement to evaluate the value in the DSC_TYP column, and then, using a combination of LEN, SUBSTR and other voodoo attempt to convert it to decimal properly. Unfortunately there was an incident and all my test code was lost.
So, before I tried to recreate it all, I thought I would ask if anyone here has encountered the same type of problem, and if they found a better way to handle it.
December 16, 2016 at 2:57 pm
a case statement is going to lock you into one datatype, so i would simply use money(= Decimal18,4)
and do the math in a case statement;
wouldn't something like this do the job?
--just some fake sample data: the work is at the bottom!
;WITH MyCTE([DSC_TYP],[VAL])
AS
(
SELECT 'D',8676 UNION ALL
SELECT 'I',17759 UNION ALL
SELECT 'I',21879 UNION ALL
SELECT 'D',21880 UNION ALL
SELECT 'I',21881 UNION ALL
SELECT 'I',21882 UNION ALL
SELECT 'D',21883 UNION ALL
SELECT 'D',21884 UNION ALL
SELECT 'I',21972 UNION ALL
SELECT 'I',21973
)
SELECT *,
CASE
WHEN [DSC_TYP]='D'
THEN CONVERT(MONEY,[VAL]) / 100
ELSE CONVERT(MONEY,[VAL])
END AS ValToDecimal
FROM MyCTE;
DSC_TYPVALValToDecimal
D867686.76
I1775917759.00
I2187921879.00
D21880218.80
I2188121881.00
I2188221882.00
D21883218.83
D21884218.84
I2197221972.00
I2197321973.00
Lowell
December 16, 2016 at 3:01 pm
I can't say I've encounter it before, but I would have thought this is quite simple to resolve.
Without the table this is guess work, but would this not work?
ValueColumn /
CASE DSC_TYP WHEN 'Pennies' THEN 100
WHEN 'Integer' THEN 100
ELSE 1 END
Assuming that DSC_TYP has a value of pennies or Integer means that it is in the format 995, rather than 9.95.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 16, 2016 at 3:05 pm
I swear I tried this at one point, and was getting a stored value of 90 returned as 90.00 - but I just tried it again, and the results are perfect.
This probably means that I shouldn't code on Fridays.
Thank you!
December 16, 2016 at 3:05 pm
N.B. (12/16/2016)
First I want to apologize - this entire situation is the vendor's fault, and not something we can change.I have a table where, depending on the value in one column - DSC_TYP - the value in a separate column is either an int, or a decimal value stored as an int (this is the vendor's doing!).
The specific situation I've got is converting the decimal values stored as int's back to decimal before doing math operations with them.
To give you a specific example, the value 9.95 is stored as 995 in this column. A direct conversion to decimal gives me 995.00 - which is incorrect, based on the vendor's documentation.
I had started on a rather ugly and unwieldy CASE statement to evaluate the value in the DSC_TYP column, and then, using a combination of LEN, SUBSTR and other voodoo attempt to convert it to decimal properly. Unfortunately there was an incident and all my test code was lost.
So, before I tried to recreate it all, I thought I would ask if anyone here has encountered the same type of problem, and if they found a better way to handle it.
I assume you have a fixed number of decimals or a column where that number is specified. Because if you don't, how do you now the 995 reprisents the 9.95 and not 99.5.
Whit the fixed number you can "convert" the int to a decimal by dividing by POWER(10.0, #) where # is the number of decimals.
Sample: SELECT 995/POWER(10.0, 2)
December 16, 2016 at 3:07 pm
HanShi (12/16/2016)
N.B. (12/16/2016)
First I want to apologize - this entire situation is the vendor's fault, and not something we can change.I have a table where, depending on the value in one column - DSC_TYP - the value in a separate column is either an int, or a decimal value stored as an int (this is the vendor's doing!).
The specific situation I've got is converting the decimal values stored as int's back to decimal before doing math operations with them.
To give you a specific example, the value 9.95 is stored as 995 in this column. A direct conversion to decimal gives me 995.00 - which is incorrect, based on the vendor's documentation.
I had started on a rather ugly and unwieldy CASE statement to evaluate the value in the DSC_TYP column, and then, using a combination of LEN, SUBSTR and other voodoo attempt to convert it to decimal properly. Unfortunately there was an incident and all my test code was lost.
So, before I tried to recreate it all, I thought I would ask if anyone here has encountered the same type of problem, and if they found a better way to handle it.
I assume you have a fixed number of decimals or a column where that number is specified. Because if you don't, how do you now the 995 reprisents the 9.95 and not 99.5.
Whit the fixed number you can "convert" the int to a decimal by dividing by POWER(10.0, #) where # is the number of decimals.
Sample: SELECT 995/POWER(10.0, 2)
I know because of the vendor documentation regarding the value in that column and how it's stored. Basically they got lazy and decided to store decimals and percents in the same column - and because all the percentages are whole numbers, they just made it an int instead of a decimal and leave trailing zeros on the percents.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply