Convert Small Money to Character 2

[teblack]

I have a smallmoney field that sometimes contains a dollar value and sometimes does not. When the value is null I would like to replace the NULL/Blanks on a report with the value of 'N/A'. I have tried a cast and convert command but with no luck.

SELECT 	CASE
WHEN tbl_EPAYTerminationNotice.finalpayamt = 0 THEN null
ELSE tbl_EPAYTerminationNotice.finalpayamt
END finalpayamt

FROM tbl_EPAYTerminationNotice, tbl_EPAYEmployer
WHERE tbl_EPAYEmployer.pk_employerid = tbl_EPAYTerminationNotice.fk_employerid 
[\code]


Thanks in advance for anyone help with this.....



TBlack -

 

[SQLDenis]

SELECT     CASE
WHEN tbl_EPAYTerminationNotice.finalpayamt is null THEN 'N/A'
ELSE convert(varchar,tbl_EPAYTerminationNotice.finalpayamt)
END finalpayamt

FROM tbl_EPAYTerminationNotice, tbl_EPAYEmployer
WHERE tbl_EPAYEmployer.pk_employerid = tbl_EPAYTerminationNotice.fk_employerid

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Couple things...

N/A can only be represented with a string field (varchar, nvarchar, char, etc...) so you will need to convert your finalpayamt field to a varchar.

You can use the IsNull function to return a hrdcoded string whenever the value is null. Like this...

SELECT IsNull(Convert(VarChar(50), tbl_EPAYTerminationNotice.finalpayamt), 'N/A') As finalpayamt
FROM   tbl_EPAYTerminationNotice, tbl_EPAYEmployer
WHERE tbl_EPAYEmployer.pk_employerid = tbl_EPAYTerminationNotice.fk_employerid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[teblack]

Thanks guys, it fixed my problem...

Thanks again for the help.



TBlack -