Gary Kjos
garykjos at gmail.com
Thu Oct 13 12:02:16 CDT 2011
Preface this answer by saying I don't do a lot of SQL Server queries. In your convert statements you are converting the output of the calculated fields to varchar. Varchar wouldn't seem to be a format that includes formatting like dollar signs and commas or a specific number of decimals like something like MONEY type would. If you replaced the varchar with money would you get the result you are looking for? On the two fields in question you also have a STR in there which would convert the money type fields to a string before the addition. That would then remove the money type formatting as well wouldn't it? If you remove that str function you would also get the similar format result as on the balance due. Why do you need the convert and the str functions in there at all for those fields? They would appear to be numeric values that you are converting to some kind of non-numeric format and then doing the mathematic functions on them. GK On Thu, Oct 13, 2011 at 6:35 AM, Alan Lawhon <lawhonac at hiwaay.net> wrote: > 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 > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- Gary Kjos garykjos at gmail.com