Error converting data type varchar to numeric

[lornab]

Hi

I wonder if anyone can help - I think the answer is simple but it's been a really long day and I need to get this done!!

My select statement goes like this...


SELECT
client,
apar_id,
ext_inv_ref,
case when amount ='' then '0' else amount end,
currency,
convert (varchar,payment_date,112),
voucher_no,
convert (varchar,voucher_date,121)

from acuhistr
where client = 'SD'
and account = '9299'

I get the error: Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I get why it's saying it, but I cannot work out how to convert the 'blank' into numeric or the numeric into varchar! Any ideas muchly appreciated

[ck9663]

What's the data type of your amount column? What do you mean by this line:

 
case when amount ='' then '0' else amount end
 
 

--- CK

[lornab]

Hiya

The amount column is a numeric I believe. What I am saying in the case statement is if the amount is blank then populate the result with 0 or 0.00 or else put the result amount in.

I have just rerun the query, and I have actually got .000 in my result! I think my problem actually lies in getting it to print the 0.00 or.000 in my ARW that I am putting the sql into. This is my result so far:
SD 29963 888 299.000 DKK 20091001 3000000 2009-10-01 00:00:00.000
SD 29963 444 .000 DKK 20091001 3000002 2009-10-01 00:00:00.000

Thanks anyway, but I am going to have to look at my report that I am using the sql in rather than the sql itself....I think it's going to be another long day :)

[ck9663]

By default, numeric data stores NULL, not blank. If what you're trying to do is to return 0 or 0.00 instead of NULL, use the ISNULL() function instead.

Happy Coding!!!

--- CK