Alan Lawhon
lawhonac at hiwaay.net
Thu Oct 13 06:35:06 CDT 2011
I've played around with this one to the point where it's driving me nuts. The code snippet follows (along with two records from the result set) followed by my question. NOTE: Base table fields "InvoiceTotal, PaymentTotal, and CreditTotal" are all of data type (money, not null) USE AP GO DECLARE @TEN_PERCENT AS money; DECLARE @INVOICE_PLUS_TEN AS money; SELECT InvoiceNumber AS Number, CONVERT(varchar, InvoiceTotal, 1) AS [Invoice Total], -- -- The three following fields are all alias calculated fields. -- CONVERT(varchar, STR(InvoiceTotal * 0.10, 10, 2), 1) AS TEN_PERCENT, CONVERT(varchar, STR((InvoiceTotal + (InvoiceTotal * 0.10)), 10, 2), 1) AS INVOICE_PLUS_TEN, -- CONVERT(varchar, (InvoiceTotal - (PaymentTotal + CreditTotal)), 1) AS [Balance Due] -- FROM Invoices WHERE InvoiceTotal - (PaymentTotal + CreditTotal) > 1000 ORDER BY InvoiceTotal DESC Here are two records from the result set: Number InvoiceTotal TEN_PERCENT INVOICE_PLUS_TEN Balance Due 1 P-0608 20,551.18 2055.12 22606.30 19,351.18 2 0-2436 10,976.06 1097.61 12073.67 10,976.06 OK, here's my question. In the display output for the TEN_PERCENT and INVOICE_PLUS_TEN calculated fields, why do those values display without the comma character while the values for the InvoiceTotal and Balance Due fields both display with the comma? (In other words, the "style = 1" argument in the CONVERT function seems to work just fine when converting base table [money] fields to varchar data type, but not when attempting to convert a calculated alias field value.) This is nitpicking (I know), but I get wrapped around the axle on things like this. Alan C. Lawhon