Cast problem

[hedgracer]

I have a Cast problem. Specifically a Cast done after a Cast problem. Here is the code:

DELETE FROM [Strategy_Runner_Import]
WHERE [Account] IS NULL

DELETE FROM [Strategy_Runner_Import]
WHERE [Account] = 'Account'

UPDATE [Strategy_Runner_Import]
SET [Fees] = REPLACE(LTRIM(RTRIM([Fees])), '$', '')




SELECT trades FROM [Strategy_Runner_Import]
GROUP BY [trades]


DECLARE @tempSR TABLE
(
UName VARCHAR(50),
Act VARCHAR(50),
Ibee VARCHAR(50),
SRp VARCHAR(50),
Trades NUMERIC(18,0),
Typ VARCHAR(50),
Fe DECIMAL(18,2)
)

INSERT INTO @tempSR(
UName,
Act,
Ibee,
SRp,
Trades,
Typ
)
SELECT A.Username, A.Account, A.IB, A.SRPrice,
CAST(A.Trades AS NUMERIC(18,0)) AS Trades,
A.TYPE
FROM [Strategy_Runner_Import] A

UPDATE @tempSR
SET [Fe] = CAST(A.Fees AS DECIMAL(18,2))
FROM [Strategy_Runner_Import] A INNER JOIN @tempSR B
ON A.[Username] = B.UName AND A.[Account] = B.Act AND
A.[IB]=B.Ibee AND A.[Trades] = B.[Trades]


The problem is the update query. The update query throws an error "Msg 8114, Level 16, State 5, Line 41
Error converting data type varchar to numeric." What is so puzzling is that the Fe in the table variable is Decimal and not Numeric. I get the same error if I try to do the decimal cast in the insert query. Can someone give me an idea why this is happening? BTW, I have inspected the contents of the
Fees column in Strategy_Runner_Import and there are no non-numeric characters.

 

[RiverGuy]

Decimal and Numeric are equivalent.

Try and see if this returns anything:

SELECT * FROM [Strategy_Runner_Import] WHERE ISNUMERIC(Fees) = 0

If that returns anything, you have some bad data.

 

[hedgracer]

The query from RiverGuy returned nothing (which I thought it would). I am sure this is not a bad data situation.

 

[RiverGuy]

Then it might not have anything to do with your cast. It might be in your join. Run this as an example.

DECLARE @t1 TABLE (Col1 VARCHAR(50))
DECLARE @t2 TABLE (Col1 DECIMAL(18,0))

INSERT INTO @t1 SELECT '1'
INSERT INTO @t1 SELECT '1x'

INSERT INTO @t2 SELECT 1
INSERT INTO @t2 SELECT 2

SELECT a.*
FROM @t1 a
INNER JOIN @t2 b ON a.Col1 = b.Col1