Putting 'NA' in a Money Datatype Column

Hello,
Can anyone solve this prob...
BACKGROUND:
I have a column in my query that is calculated from money datatypes. To
avoid the divide by zero error I created a nice inline case statement that
places something else in that field instead. It works great with any number
of my choice. For example I can put 100 everywhere that this calculated
field would've had a divide by zero error. HOWEVER, I want to put the word
'NA' in that field when this happens. But I can't because the datatype is
wrong:
ERROR:
Hence the error:Implicit conversion from data type char to money is not
allowed.
PROBLEM:
So is there a way to legally cast or convert so that I can put 'NA' in this
field instead of a number like zero or a hundred?
Here is my attempt so far (doesn't work):
CASE WHEN [expression] = 0 THEN CAST('NA' AS CHAR(2)) ELSE [other
expression]
Thanks.
--
Alex A.You will need to cast the entire field as varchar.
Another option would be to return NULL when the expression = 0 and have the
presentation tier (Crystal Reports, Excel, ASP.NET, etc.) display NULL as
NA.
"Alex A." <AlexA@.discussions.microsoft.com> wrote in message
news:12E0480D-DB52-4664-A2E2-F9D335FE5BB5@.microsoft.com...
> Hello,
> Can anyone solve this prob...
> BACKGROUND:
> I have a column in my query that is calculated from money datatypes. To
> avoid the divide by zero error I created a nice inline case statement that
> places something else in that field instead. It works great with any
> number
> of my choice. For example I can put 100 everywhere that this calculated
> field would've had a divide by zero error. HOWEVER, I want to put the
> word
> 'NA' in that field when this happens. But I can't because the datatype is
> wrong:
> ERROR:
> Hence the error:Implicit conversion from data type char to money is not
> allowed.
> PROBLEM:
> So is there a way to legally cast or convert so that I can put 'NA' in
> this
> field instead of a number like zero or a hundred?
> Here is my attempt so far (doesn't work):
> CASE WHEN [expression] = 0 THEN CAST('NA' AS CHAR(2)) ELSE [other
> expression]
> Thanks.
> --
> Alex A.
>|||WKidd,
Thanks for the post...
I can't cast the whole field because if it is not a divide by zero situation
I need the dollar amount to calculate... But I like the Null idea. Anyone
have the syntax in mind for that? Can I just put the word NULL after the
THEN statement?
"WKidd" wrote:

> You will need to cast the entire field as varchar.
> Another option would be to return NULL when the expression = 0 and have th
e
> presentation tier (Crystal Reports, Excel, ASP.NET, etc.) display NULL as
> NA.
> "Alex A." <AlexA@.discussions.microsoft.com> wrote in message
> news:12E0480D-DB52-4664-A2E2-F9D335FE5BB5@.microsoft.com...
>
>|||Look up hopw to use a NULL in SQL. But I would also get rid of the
MONEY datatype.
The MONEY datatype has rounding errors. Using more than one operation
(multiplication or division) on money columns will produce severe
rounding errors. A simple way to visualize money arithmetic is to place
a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY,
@.money_result MONEY,
@.float_result FLOAT,
@.all_floats FLOAT;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.Retult3 = (CAST(@.my_part AS FLOAT)
/ CAST( @.total_amt AS FLOAT))
* CAST(FLOAT, @.gross_amtAS FLOAT);
SELECT @.money_result, @.float_result, @.all_floats;
END;
@.money_result = 13525.09 -- incorrect
@.float_result = 13525.0885 -- incorrect
@.all_floats = 13530.5038673171 -- correct, with a -5.42 error|||For divisions, I usually write:
SELECT A / NULLIF(B, 0)
This will yield NULL if B is 0, because Anything / NULL yields NULL.
Regarding the precision of the money data type, you may want to do an
implicit (or an explicit) conversion to the decimal (or the float) data
type, like this:
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SELECT CAST((1. * @.my_part / @.total_amt) * @.gross_amt AS money)
The correct result would have been 13530.5038673170731707317073170(...)
but converted back to the money datatype it is 13530.5039
Razvan