"Waz" <keevers@idl.com.au> wrote in message
news:405fb656$0$29320$c30e37c6@lon-reader.news.telstra.net...
> Hello,
>
> I have a problem with an sql script doing a conversion
>
> for example
> select CONVERT(decimal(25,2), amount) as amount from mytable
>
> The problem arises when there is a null or blank value for the column as
> expected it bombs out.
> I cant change the data in the table as it is too late for that.
>
> Does anyone know how to somehow substitute a zero for when i come across
> null or blank values and not do the conversion - within that query
> i haven't much time left and my google searches have returned nothing of
> interest yet although i will keep trying
>
> Thanks very much in advance for any help
>
> Warren
>
>
>
>
>

"Tenaya" <ct@ct.ct> wrote in message
news:uEduWvLEEHA.3980@TK2MSFTNGP09.phx.gbl...
> Warren,
>
> Perhaps the following will be of value to you:
>
> create table mytable (amount varchar (10) NULL)
> go
>
> insert into mytable values ('1.0')
> insert into mytable values ('')
> insert into mytable values (NULL)
> go
>
> select cast(case amount when '' then '0' else amount end as decimal(25,2))
> as amount from mytable
>
> Chief Tenaya
>
>
> "Waz" <keevers@idl.com.au> wrote in message
> news:405fb656$0$29320$c30e37c6@lon-reader.news.telstra.net...
> > Hello,
> >
> > I have a problem with an sql script doing a conversion
> >
> > for example
> > select CONVERT(decimal(25,2), amount) as amount from mytable
> >
> > The problem arises when there is a null or blank value for the column as
> > expected it bombs out.
> > I cant change the data in the table as it is too late for that.
> >
> > Does anyone know how to somehow substitute a zero for when i come across
> > null or blank values and not do the conversion - within that query
> > i haven't much time left and my google searches have returned nothing of
> > interest yet although i will keep trying
> >
> > Thanks very much in advance for any help
> >
> > Warren
> >
> >
> >
> >
> >
>
>

>-----Original Message-----
>Hello,
>
>I have a problem with an sql script doing a conversion
>
>for example
> select CONVERT(decimal(25,2), amount) as amount from
mytable
>
>The problem arises when there is a null or blank value
for the column as
>expected it bombs out.
>I cant change the data in the table as it is too late for
that.
>
>Does anyone know how to somehow substitute a zero for
when i come across
>null or blank values and not do the conversion - within
that query
>i haven't much time left and my google searches have
returned nothing of
>interest yet although i will keep trying
>
>Thanks very much in advance for any help
>
>Warren
>
>
>
>
>
>.