[dba-SQLServer] Converting an Alias (Calculated Field) of Money Data Type to Display as varchar

David, Gary:

Success!!  (Thanks for your help.)

Using the CAST function in conjunction with the STR and CONVERT functions to
define the "TEN_PERCENT" and "INVOICE_PLUS_TEN" calculated fields did the
trick.  It's kind of interesting how you can get a syntax error if you use
"AS" when SQL Server is looking for "As" instead.  (SQL Server very picky
...)

Applying CAST and STR to the other three [money] fields wasn't necessary as
those three fields are base table fields already defined as data type
(money, not null).  Application of a straight CONVERT function to those
fields worked.  I agree with whichever one of you who noted that output
formatting should be handled by the front end client.  I shouldn't have
gotten so wrapped around the axle on this, but this was an exercise problem
from the chapter on basic SELECT statement syntax.  (I'm now proceeding on
to the chapter dealing with multi-table joins.)

I'm still in the early chapters of the training book I'm working through.
After a five year "break" from daily programming, my recollection of some of
these topics is rusty.  Later chapters of the book get into very involved
and very intricate discussions of all the SQL Server data types and how to
handle common problems that arise from the interaction of incompatible data
types.

Alan C. Lawhon
         
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
McAfee
Sent: Thursday, October 13, 2011 11:58 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Converting an Alias (Calculated Field) of Money
Data Type to Display as varchar

It is because you are converting them to varchars.

If you leave them as money, you should be fine.

If you want to use the formatting that STR provides, convert it to money
first, like I did in Test2:


DECLARE @InvoiceNumber AS int  SET @InvoiceNumber = 12345
DECLARE @InvoiceTotal AS MONEY SET @InvoiceTotal  = 1999.99
DECLARE @PaymentTotal AS MONEY SET @PaymentTotal  = 20
DECLARE @CreditTotal AS MONEY  SET @CreditTotal   = 5

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, CAST(STR((@InvoiceTotal + (@InvoiceTotal * 0.10)),
10, 2) AS MONEY) ,1) AS INVOICE_PLUS_TEN2,
        CAST(@InvoiceTotal + (@InvoiceTotal * 0.10)AS MONEY) AS
INVOICE_PLUS_TEN_TEST3,
        (@InvoiceTotal + (@InvoiceTotal * 0.10)) AS INVOICE_PLUS_TEN_TEST4,

--
      CONVERT(varchar, (@InvoiceTotal - (@PaymentTotal + @CreditTotal)), 1)
AS [Balance Due]





On Thu, Oct 13, 2011 at 4: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