using case to conver char to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

using case to conver char to numeric

Author

Topic

Danny4003
Starting Member

40 Posts

Posted - 2007-01-19 : 15:54:11

I was wandering how should the case statement go;
Main Table
Title Days
abcde 3mt

Seperate Table
Title Price
abcde 3.99

Another Table
Title acres
abcde 350

Scenerio is that i want to calculate an overall price
with Days * Price * Acres
But the problem is that Days has "3mt"

How do I accomplish this Task:
Example script is as Follows

Select (MainTable.Days * SeperateTable.Price * AnotherTable.acres)
AS OverallPrice
From MainTable
INNER JOIN SeperateTable.Title = MainTable.Title)
INNER JOIN AnotherTable.Title = MainTable.Title)

Result::
Error converting data type varchar to numeric.

Thanks for the help in Advance
Danny Dubroc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:56:40

That's not the only error. You have a lot of paranthesis too.


Select CAST(MainTable.Days AS NUMERIC(12, 8)) * CAST(SeperateTable.Price AS NUMERIC(12, 8)) * CAST(AnotherTable.acres AS NUMERIC(12, 8))
AS OverallPrice
From MainTable
INNER JOIN SeperateTable.Title = MainTable.Title
INNER JOIN AnotherTable.Title = MainTable.Title

Peter Larsson
Helsingborg, Sweden

Danny4003
Starting Member

40 Posts

Posted - 2007-01-19 : 16:12:11

it throws me the same error about converting data type varchar to numeric

do you think that maybe i have to cast

Danny Dubroc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 16:24:31

Of course! What does 3mt mean? 3 months? 92 days?

Select CAST(replace(MainTable.Days, 'mt', '') AS NUMERIC(12, 8)) * CAST(SeperateTable.Price AS NUMERIC(12, 8)) * CAST(AnotherTable.acres AS NUMERIC(12, 8))
AS OverallPrice
From MainTable
INNER JOIN SeperateTable.Title = MainTable.Title
INNER JOIN AnotherTable.Title = MainTable.Title

Peter Larsson
Helsingborg, Sweden

Danny4003
Starting Member

40 Posts

Posted - 2007-01-19 : 17:05:44

Thanks Peso
that worked

Danny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources