"Steve Kass" wrote:

> In addition to David's suggestion, a solution is to eliminate the bad
> conversion using CASE:
>
> .... CONVERT(MONEY, CASE WHEN ISNUMERIC(B) THEN B ELSE '0' END) ...
>
> This should work assuming no B values that convert to some numeric type but
> not to MONEY.
>
> Steve Kass
> Drew University
>
> "IanT" <IanT@discussions.microsoft.com> wrote in message
> news:7696EBE4-48AC-44DB-916A-2742C5AF7BBD@microsoft.com...
> > Is this a Bug?
> >
> > In SQL server 2000 service pack 3a,
> > I am having problems with the convert function which is causing reports to
> be unreliable even after testing.
> >
> > The only difference between Part 1 and Part 2 is a primary key but part 2
> gives a convert error.
> >
> > Furthermore,
> > Adding about 20 rows more to Part 1 creates a convert error.
> >
> > Furthermore,
> > Removing any rows from Part 2 hides the convert error.
> >
> > ---------------------------------
> > -- PART 1
> > ---------------------------------
> > --
> > -- No key, Nothing in A to join
> > -- => No convert error.
> > --
> > CREATE TABLE #A ( A varchar (15))
> > CREATE TABLE #B ( B varchar (15) NOT NULL)
> > INSERT INTO #B VALUES ( 'a' )
> > INSERT INTO #B VALUES ( 'a' )
> > INSERT INTO #B VALUES ( 'a' )
> > INSERT INTO #B VALUES ( 'a' )
> > GO
> > SELECT A, SUM(CONVERT(MONEY, B))
> > FROM #A JOIN #B ON A = B
> > GROUP BY A
> > GO
> > DROP TABLE #A
> > DROP TABLE #B
> > GO
> >
> >
> > --------------------------------
> > -- Part 2
> > --------------------------------
> > --
> > -- Primary key #A, Nothing in A to join
> > -- => Convert error.
> > --
> > CREATE TABLE #A ( A varchar (15), CONSTRAINT [WPK] PRIMARY KEY ( A ))
> > CREATE TABLE #B ( B varchar (15) NOT NULL)
> > INSERT INTO #B VALUES ( 'a' )
> > INSERT INTO #B VALUES ( 'a' )
> > INSERT INTO #B VALUES ( 'a' )
> > INSERT INTO #B VALUES ( 'a' )
> > GO
> > SELECT A, SUM(CONVERT(MONEY, B))
> > FROM #A JOIN #B ON A = B
> > GROUP BY A
> > GO
> > DROP TABLE #A
> > DROP TABLE #B
> > GO
> >
>
>