Forum Post: RE: odbc IsNumeric function

[scottemick]

SELECT {fn CAST({fn replace(Netsales,',','')} AS NUMERIC(18,2))} Netsales FROM PUB.eDB_MajorGroupTotals T is not working and SELECT to_number(Netsales) FROM PUB.eDB_MajorGroupTotals T is not working either I get Invalid number string (7498) SELECT to_number(replace(Netsales,',','')) Netsales FROM PUB.eDB_MajorGroupTotals T doesn't work either... This craziness works in sql server against the linked server: create procedure drs.WeekToDateComparableSalesSummary @datestart datetime ,@dateend datetime AS declare @MajorGroupTotals table(EntityId int, Netsales money); insert into @MajorGroupTotals select T.EntityId, Cast(replace(Netsales,',','') as numeric(18,2)) Netsales from CHESTNUTLAND.CORPDB2.PUB.eDB_MajorGroupTotals T where T.dt_StartBusDate BETWEEN @datestart AND @dateend; declare @Entity table(EntityId int,RegionId int,Name varchar(100)); insert into @Entity select E.EntityId, E.RegionId, E.Name FROM CHESTNUTLAND.CORPDB2.PUB.Entity E; declare @Region table(RegionId int, Name varchar(100)); insert into @Region select R.RegionId, R.Name from CHESTNUTLAND.CORPDB2.PUB.Region R; SELECT R.RegionId , R.Name Region , E.EntityId , E.Name Location , sum(T.Netsales) Netsales FROM @Entity E INNER JOIN @MajorGroupTotals T ON E.EntityID = T.EntityID INNER JOIN @Region R ON E.RegionID = R.RegionID GROUP BY R.RegionId , R.Name , E.EntityId , E.Name ORDER BY R.RegionID, E.EntityID exec drs.WeekToDateComparableSalesSummary '07-20-14','07-26-14'

Continue reading...