December 7, 2008 at 11:34 am
Hi,
I'm writing a stored proc to display credit/debit values. These values are of type decimal.
I want to be able to display an empty field where a credit is made and vice versa. For example:
Debit Credit
1000
200
2000
2500
1500
The problem is that the 'Amount' filed in my table that stores these values is a decimal(8,2). So when I use ISNULL(Amount, ' '), if 'Amount' is NULL then it seems that ISNULL function expects an argument to be of the same type as 'Amount'. Is that correct?
If so, how do I replace NULL with an empty string?
thanks,
Paul
December 7, 2008 at 11:41 am
CAST or CONVERT the decimal amount to varchar and then you can use an empty string with ISNULL.
select isnull(convert(varchar(10),amount),'') as amount
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 7, 2008 at 12:31 pm
Use STR function.
It's perfect for displaying decimal values and its output is a string.
_____________
Code for TallyGenerator
December 7, 2008 at 1:03 pm
Sergiy's right. STR is often a better choice, but it depends on what you're going to do with your output.
If you're going to use STR, be sure to use the optional parameters to specify the number of decimals, or else STR will return the rounded integer portion of the amount. Another difference is that STR will return a string padded with blanks on the left.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 7, 2008 at 6:19 pm
Bob Hovious (12/7/2008)
Another difference is that STR will return a string padded with blanks on the left.
In most cases it's actually an advantage.
Most of our customers require numeric values to be padded with zeros on the left. Simply by replacing spaces in STR output with zeros we get just that.
_____________
Code for TallyGenerator
December 8, 2008 at 10:06 am
Most of our output goes to either reporting services, spreadsheets, or user interfaces that handle the formatting. The only raw SQL reports are ad-hoc. I was just making him aware of some of the differences... and warning him not to just put STR(AMT) unless he wanted rounding.
Are you always so picky when someone agrees with you? 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 8, 2008 at 2:10 pm
I'm not picky at all.
Especially when someone agrees with me 😉
Just wanted to show how to use "a bug" as "a feature".
😎
_____________
Code for TallyGenerator
December 8, 2008 at 2:29 pm
Who said it was a bug? 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 8, 2008 at 3:02 pm
Bob Hovious (12/8/2008)
Who said it was a bug? 😉
In this topic - nobody. Yet.
But I know at least 2 guys who took my scripts with STR in it and continued with LTRIM followed with something like RIGHT(REPLICATE('0', 10) + ..., 12).
Because that's the way they do it in VB.
😉
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply