Forum Post: Re: odbc IsNumeric function

[steve pittman]

There is a bug in the OE sql replace() function, recently reported by one of our support people, when the replacement string is the empty string (0 bytes string). In this bug, if the search string matches, no replacement occurs. Apparently, this bug has existed at least back to 102b. We cannot currently state when a fix for this problem will be delivered. It is considered a significant bug. This bug would seem to account for some of the difficulties you are seeing in your trying to resolve your problem. hope this helps, ....steve pittman [OE sql software development architect] On 7/23/2014 11:30 AM, scottemick wrote: RE: odbc IsNumeric function Reply by 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' Stop receiving emails on this subject. Flag this post as spam/abuse.

Continue reading...