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