Direct One
asked on
MS SQL Statement to make amount field all Zero's when the field is null
I have this statement to pad amounts with zero's and change the dollar amount from negative to positive.
When a check is voided, it is a null amount for the dist_amt. How do we populate just zero's when the amount is null included in this statement? Also, if there is a better way to write this entire statement, please advise as well. Thanks in advance.
RIGHT ('000000000000' + CONVERT (varchar, REPLACE(CONVERT (varchar, dbo.APDISFIL_SQL.dist_amt) , '.', '') * - 1), 12)
When a check is voided, it is a null amount for the dist_amt. How do we populate just zero's when the amount is null included in this statement? Also, if there is a better way to write this entire statement, please advise as well. Thanks in advance.
RIGHT ('000000000000' + CONVERT (varchar, REPLACE(CONVERT (varchar, dbo.APDISFIL_SQL.dist_amt)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
maybe my post is not correct, what is your data type for dbo.APDISFIL_SQL.dist_amt
give some samples, positive, negative, null etc...
give some samples, positive, negative, null etc...
ASKER
worked great. thanks
more like
RIGHT ('000000000000' + REPLACE(CONVERT(varchar(13), coalesce(-(dbo.APDISFIL_SQL.dist_amt),0)), '.', ''), 12)
I am assuming dbo.APDISFIL_SQL.dist_amt is float or some other numeric format
then why you are converting it to char and multiply by -1?
maybe you want this
RIGHT ('000000000000' + REPLACE(CONVERT (varchar, abs(isnull(dbo.APDISFIL_SQ
first, set to 0 if it is null, then get abs value(remove negative), then convert it to varchar, replace ., pad with 0 to make it 12 char...