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*********************@z14g2000cwz.googlegro ups.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******@melange-inc.com> wrote in message
news:11*************@corp.supernews.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*********************@z14g2000cwz.googlegro ups.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

[--CELKO--]

1) Do not use the proprietary MONEY data type and its weird math in
your schema.;You do Google ALL proprietary data types before you decide
to destroy data integrity, portability, etc. in your schem?

2) Then you did wake up in the middle of a freshman class, so you woudl
know that display is NEVER done in the database? You do know that in a
tiered archtecture requires that display is done in the front end? Do
this in the front end, where it is supposed to be!!!

3) Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files; there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless. YOu have not learend the most basic concepts of
RDBMS.

Your whole mental/logical model is **totally wrong** and you need to
stop programming because you are dangerously ignorant. My opinion is
based on 20+ years of teaching SQL, writing standards, six books, and
being paid to fix thing like this.

[pb648174]

CELKO, you need to save yourself some time and just setup an auto
routine that emails new users as they come into the SQL group and tells
them they are all retards.

--CELKO-- wrote:

1) Do not use the proprietary MONEY data type and its weird math in
your schema.;You do Google ALL proprietary data types before you decide
to destroy data integrity, portability, etc. in your schem?

2) Then you did wake up in the middle of a freshman class, so you woudl
know that display is NEVER done in the database? You do know that in a
tiered archtecture requires that display is done in the front end? Do
this in the front end, where it is supposed to be!!!

3) Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files; there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless. YOu have not learend the most basic concepts of
RDBMS.

Your whole mental/logical model is **totally wrong** and you need to
stop programming because you are dangerously ignorant. My opinion is
based on 20+ years of teaching SQL, writing standards, six books, and
being paid to fix thing like this.