September 13, 2019 at 12:09 am
Hi:
I have some values in Amount column as
0.00
1619.70
-62.20
34.40
0.00
0.00
23.00
-345.23
Where ever there is 0.00 I want it to be replaced as 0 instead of 0.00. The output in this case should be:
0
1619.70
-62.20
34.40
0
0
23.00
-345.23
This SQL does nothing -
SUM(CASE
WHEN TYPE = 'ABC'
THEN CAST(REPLACE(AMOUNT, 0.00, 0) AS NUMERIC(16,2))
ELSE 0
END) 'Debit'
Thanks!
September 13, 2019 at 12:43 am
What are you displaying this in? Excel/SSRS and similar tools offer formatting options to take care of this for you.
SSMS is a query tool, not a presentation tool.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2019 at 6:02 pm
You missed one of the major points in modern programming since the 1970s. SQL and other client/server-based architectures depend on tiers. SQL works in the database tier of your architecture. this tier maintains data integrity, consistency and makes the resource available. Have you ever been to an auto parts store? You go to the counter and you ask the clerk at the desk to fill out your shopping list and return it to you in a basket. That's what the database layer does with data. The result of that is then returned to a presentation layer (actually, it could be several presentation layers). The database layer doesn't care about the presentation layer. In fact, the whole goal is to have them as loosely coupled as possible. You've confused these two layers! This puts you about 50 years behind the curve in your programming skills and you really need to catch up 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply