CONCAT Numeric & Character Data

[MFisher]

Hi, I am trying to build a description field from various fields concatenated together.

I want to check 2 numeric fields. If they are both > 0 , then do division, Else replace with a blank.
I can replace with a 0 , but can't get the syntax correct to replace with a non numeric blank.

XUNIT is 5,0 XTBG is 6,0 Largest value of XTBG divided by XUNIT is 3,000

Trying to CAST the division result as VARCHAR gives me an error that I am truncating data
, CAST(( CASE WHEN XUNIT > 0 and XTBG > 0 THEN CAST(XTBG/XUNIT as VARCHAR(5) ) ELSE ' ' END ) as VARCHAR(5) )

This works, only if I put in a 0 as ELSE, but I don't want a 0 to display.
, CAST(( CASE WHEN XUNIT > 0 and XTBG > 0 THEN CAST(XTBG/XUNIT as DEC(5,0) ) ELSE cast(0 as varchar(1)) END ) as VARCHAR(5) )

If I put a blank space as ELSE, I get ++++++++++ in the result
, CAST(( CASE WHEN XUNIT > 0 and XTBG > 0 THEN CAST(XTBG/XUNIT as DEC(5,0) ) ELSE ' ' END ) as VARCHAR(5) )

I'll keep trying, TIA

[B.Hauser]

Try this:

Case When xUnit > 0 and xtbg > 0
Then Cast(Dec(XTBG/XUnit, 9, 3) as VarChar(10))
Else ''
End

Or this:

Case When xUnit > 0 and xtbg > 0
Then Cast(Cast(XTBG/XUnit as Dec(9, 3)) as VarChar(10))
Else ''
End

Birgitta

[MFisher]

Thanks....Again !