sql - Case condition does not return desirable results -

here query:

select accounttitle, case     when sourcedocdr < 1         replace(cast(sourcedocdr int), 0, '')     else sourcedocdr end 'debit', case     when sourcedoccr < 1         replace(cast(sourcedoccr int), 0, '')     else sourcedoccr end 'credit'  tblaccounting_gl month(postingdate) = month(getdate()) group accounttitle, sourcedocdr, sourcedoccr; 

result else statement:

+----------------------------------+---------+--------+ |          account title           |  debit  | credit | +----------------------------------+---------+--------+ | accounts payable                 | 0.00    | 100.00 | | accounts receivable -vat         | 0.00    | 300.00 | | cash in bank bpi mia road  - php | 2600.00 | 0.00   | +----------------------------------+---------+--------+ 

result without else statement:

+----------------------------------+-------+--------+ |          account title           | debit | credit | +----------------------------------+-------+--------+ | accounts payable                 |       | null   | | accounts receivable -vat         |       | null   | | cash in bank bpi mia road  - php | null  |        | +----------------------------------+-------+--------+ 

the above query should replace 0 or negative values blank. however, still returns actual value. if remove else statement, that's time when 0 or negative values replaced blanks. however, in case, values greater 0 not returned also. above query seems perfect me don't know why not work.

this happens because else part, return values gets converted decimal (or whatever data type sourcedocdr has). remember in case expression, if return values have different data types, converted datatype higher data type precedence.

from case documentation:

returns highest precedence type set of types in result_expressions , optional else_result_expression.

to achieve desired result, have cast results of case expression varchar:

select     accounttitle,     case         when sourcedocdr < 1  ''                 else cast(sourcedocdr varchar(max))     end 'debit',     case         when sourcedoccr < 1 ''         else cast(sourcedoccr varchar(max))     end 'credit' tblaccounting_gl      month(postingdate) = month(getdate()) group      accounttitle, sourcedocdr, sourcedoccr;