August 26, 2014 at 12:04 pm
((DT_NUMERIC,7,0)REPLACE(["Aggregate Total Space"]," TB","")) * 1024
folks I have a colum "aggregate total space" that comes in as a varchar(50).
The values have word " TB" or " GB" or " MB" in them.
I am trying to remove these words, and convert this into a numeric value for reporting.
The default is GB. So if the data comes in as TB I multiply the result by 1024. if it comes in as MB then I divide it by 1024. If it comes in as a GB i just convert it and let it pass.
When I run this command I get the following error.
[Removing Reference for " GB" [108]] Error: An error occurred while attempting to perform a type cast.
Can somebody help me?
Cheers
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
August 26, 2014 at 12:12 pm
The expression you posted only removes the characters TB. What about GB and MB?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2014 at 12:15 pm
I have similar modules.
Once I have response for this, I can substitute it in the other places.
Thanks for the quick response.
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
August 27, 2014 at 12:57 am
Paresh Motiwala (8/26/2014)
I have similar modules.Once I have response for this, I can substitute it in the other places.
Thanks for the quick response.
Paresh
OK. But if you only remove TB, won't GB or MB still be present in the column, failing your expression?
Maybe you can post some sample data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2014 at 6:49 am
let's just say there is GB. How would you then handle it?
As I alluded before, I have the same exact modules for MB, KB, TB and bytes.
Once I have answer for GB, I can replicate it for rest of the condition.
Thanks in advance for all your help guys.
Cheers.
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply