| Author |
Topic |
|
silver_atlima
Starting Member
4 Posts |
 Posted - 2004-03-29 : 21:13:27
|
| Looks like this is a cast/convert syntax question but not sure.Using sql server 2000 as my back end and access reports as my front end. I have a stored procedure that I am writing which extracts information from the server which in turn will feed the access front end. The report requires that the data be numeric and one of the fields needs 2 decimal points. In sql server that data is stored as a varchar. Here's what I'm doing...am I close or totally off? (I am also pivoting the values too)SELECT Time, Date, MAX(CASE key WHEN '1' THEN cast(Value as real) END) AS Value1, MAX(CASE key WHEN '2' THEN cast(Value as real) END) AS Value2,FROMetc etc...Please help?! ThanxP.S. This is my first post. Sorry if I'm not following any rules...hehee...:) |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-03-29 : 22:24:23
|
If you are trying to convert a varchar value that has no decimal point into a value with a decimal point by using CAST or CONVERT, that's not going to work. quote:
The report requires that the data be numeric and one of the fields needs 2 decimal points.
I will assume you mean it needs two digits after the decimal, because I can't imagine a number with two decimal points. If you want to return a number like that, you would select SUBSTRING(fldName,1,LEN(fldName) - 2) + '.' + RIGHT(fldName,2) to convert a value such as 12345 into 123.45BTW, stuff like this (formatting numbers etc.) should usually be done on the front end, such as in the report. Any special reason you won't?Sarah Berger MCSD |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-29 : 22:50:31
|
| You could also take the data and do this:All you need to do to show numeric data with two decimals is to CAST(Value AS DECIMAL(7,2)) You can choose to make the 7 any size you want greater than 2. I'm not really clear on the rest of what you are trying to accomplish though. It seems like you would want something else in that case statement. What if the key is not 1 or 2, what are you going to show then?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-03-30 : 15:48:05
|
| Derrick, CAST will not work because the numbers after the decimal will always be .00To my understanding, what this poster wants is like a crude way to format character data into a money format e.g '12345' as 123.45, and CAST will return 12345.00Sarah Berger MCSD |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-30 : 18:41:52
|
| I see what you were saying. I'm just not sure that I'm reading the same thing. It would be interesting if they replied back though wouldn't it. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-03-30 : 21:39:41
|
On the other hand, even though CAST will not work to format the data right, the poster needs to use CAST as an intermediate step if they want to use MAX because otherwise the numbers will be sorted alphabetically and the MAX will be number whose 1'st character is the highest in 0-9 order. Like this code:create table #T (val char(10))insert #T values('100')insert #T values('200')insert #T values('300')insert #T values('9')select max(val) from #Tdrop table #TThe returned value will be 9.Sarah Berger MCSD |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-30 : 21:50:53
|
| How do you read minds so well? I'm still confused how you know all this.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-04-01 : 15:36:43
|
See poster's query. It uses MAX. Besides, yes, I have ESP. Would you like to know the winning number for the next powerball? It's your's for $1000. Sarah Berger MCSD |
|
|
|
|
cast/convert