Display Money Type using + and -

[ZRexRider]

This may be an easy question but I've been reading for about a half
hour and experimenting without results.

I simply want the results of my query to display a specific field that
is typed "money" using + and -

The program that consumes the data expects + on positive numbers and -
on negative. I was hoping to do it in the view instead of processing
the results with the VB application that interogates the DB.

Thanks

[SQL]

It should be done in VB
but you can do it like this

select case field when < 0 then '-' + convert(varchar ,field) else '+' +
convert(varchar ,field) end MoneyField from TableA

I don't know whatt you want to display for 0.00
http://sqlservercode.blogspot.com/

[David Portas]

Why would you do this in the database? And why are you using a MONEY
column in your table?

--
David Portas
SQL Server MVP
--

[ZRexRider]

Yeah.... I guess you're right - it should be done in VB.

I was trying to massage the output from the server rather than expect
each developer (who may be using different tool) from "rolling their
own" routines.

Thanks

[ZRexRider]

Hi,

MONEY is the type that was used in the database table - I have no
choice with that. I was attempting to do this in the database because
I wanted to store the methods used in "massaging" this data in a
central location. It's just in a view now and maybe I would have moved
it to a parameterized stored procedure later.

So now I'll query from VB, massage the data in VB. Then in 2 years
when somebody re-writes the application using C# for an internet
application they will have to re-write the code instead of just calling
a nicely centralized routine within the database.

I honestly don't mean to offend you but why would you respond to a post
with questions like that?

[David Portas]

No offence. I was trying to help but first I needed more information.
There could have been more than one reason why you'd want to do this -
for example you could have had a requirement to integrate the data with
an external application.

The reason I ask about MONEY in particular is that the problems with
using that datatype in calculations may sometimes be overlooked. Take a
look at the following example. Be aware of the rounding issue when you
develop calculations against the data and think carefully about the
implications before you use MONEY.

DECLA RE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result

Result:

money_result numeric_result
--------------------- ---------------------
2949.0000 2949.8525

(1 row(s) affected)

--
David Portas
SQL Server MVP
--

[ZRexRider]

Thanks - that is good information. I would have never guessed that -
however I may be stuck with this data type since I haven't investigate
why it's being used or where I would break something if I changed the
type to decimal.

You've convinced me though - I will never use Money for a datatype.

Cheers

[Mike Reigler]

Why is this the case? From the definition of MONEY in BOL I would expect
the same result.

Mike Reigler

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

No offence. I was trying to help but first I needed more information.
There could have been more than one reason why you'd want to do this -
for example you could have had a requirement to integrate the data with
an external application.

The reason I ask about MONEY in particular is that the problems with
using that datatype in calculations may sometimes be overlooked. Take a
look at the following example. Be aware of the rounding issue when you
develop calculations against the data and think carefully about the
implications before you use MONEY.

DECLA RE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result

Result:

money_result numeric_result
--------------------- ---------------------
2949.0000 2949.8525

(1 row(s) affected)

--
David Portas
SQL Server MVP
--

[Mike Reigler]

Nevermind. After some more digging I realized what was happening when MONEY
/ MONEY. Thanks.

Mike Reigler

"Mike Reigler" <mr******@melan ge-inc.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Why is this the case? From the definition of MONEY in BOL I would expect
the same result.

Mike Reigler

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

No offence. I was trying to help but first I needed more information.
There could have been more than one reason why you'd want to do this -
for example you could have had a requirement to integrate the data with
an external application.

The reason I ask about MONEY in particular is that the problems with
using that datatype in calculations may sometimes be overlooked. Take a
look at the following example. Be aware of the rounding issue when you
develop calculations against the data and think carefully about the
implications before you use MONEY.

DECLA RE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result

Result:

money_result numeric_result
--------------------- ---------------------
2949.0000 2949.8525

(1 row(s) affected)

--
David Portas
SQL Server MVP
--

[xAvailx]

Hi:

As others have replied, the formatting should probably stay on the
front end.
If you are looking at centralizing some of the process/format, look
into an object oriented solution. This is offtopic for this newsgroup,
but you could create a money class with a formatting function that can
do the + and - formatting for you. All the developers can then use this
class instead of having to roll out their own routines. This solution
will still encapsulate the logic in one place and you don't have to
polute the database with formatting.

HTH,
BZ