That worked but now I have another question.
When I updated the code and query the database I got the results I was looking for but one small little problem. I now have extra space in my string and I cant get rid of it. If UNIT_NUM had value everything works fine but if there is no UNIT_NUM adds extra space in my concatenate string.
UNIT_NUM no value:
10170 Kermode Court("Double Space")LAS VEGAS, NV 89178
7570 West Flamingo Avenue #255 LAS VEGAS, NV 89147
-- PROPERTY ADDRESS
RTRIM(TRANS.ADDRESS_NUM) + ' ' + RTRIM(TRANS.ADDRESS) + ' ' +
CASE WHEN ISNULL(TRANS.UNIT_NUM, '') <> '' THEN '#' + RTRIM(TRANS.UNIT_NUM) ELSE '' END +' '+
RTRIM(TRANS.CITY) + ', ' + RTRIM(TRANS.STATE) + ' ' + RTRIM(TRANS.ZIP_CODE)
Thanks for the help again...