Link to home
Create AccountLog in
Avatar of Direct One
Direct OneFlag for United States of America

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)
ASKER CERTIFIED SOLUTION
Avatar of jorgedeoliveiraborges
jorgedeoliveiraborges
Flag of Brazil image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
there is an issue with code above...
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_SQL.dist_amt,0))), '.', ''), 12)

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...

maybe my post is not correct, what is your data type for dbo.APDISFIL_SQL.dist_amt
give some samples, positive, negative, null etc...
Avatar of Direct One

ASKER

worked great. thanks
Avatar of Lowfatspread
more like

RIGHT ('000000000000' + REPLACE(CONVERT(varchar(13), coalesce(-(dbo.APDISFIL_SQL.dist_amt),0)), '.', ''), 12)

Open in new window