Select distinct
p.scode, 
unit.scode,
case av.hattributename when 7 then av.sValue Else '' end,
p.sAddr2
, p.sCity
, p.sState
, p.sZipCode
, case av.hattributename when 8 then av.sValue Else '' end
, case av.hattributename when 34 then av.sValue Else '' end
, PROPBUT8.DUPSTATUS
, case av.hattributename when 5 then av.sValue Else '' end
, PROPBUT13.ACQUISITIONDATE
, PROPBUT13.ACQUISITIONPRICE
, PROPBUT13.ALLINACTUAL
, PROPBUT13.ALLINSTABLE
, PROPBUT13.STABLEDT
, tenant.sRent
, Case tenant.iStatus When 0 Then 'Current'  When 1 Then 'Past'  When 2 Then 'Future'  When 3 Then 'Eviction'  When 4 Then 'Notice'  When 5 Then 'Vacant'  When 6 Then 'Applicant'  When 7 Then 'Cancelled'  When 8 Then 'Wait list'  When 9 Then 'Denied'  Else ' '  End
, PROPBUT8.CITYTAX
, PROPBUT8.COUNTYTAX
, PROPBUT8.REFINANCEDSTATUS
, PROPBUT8.DATEREFINANCED
, PROPBUT8.FINANCELENDER
From  Property p  
Inner Join unit ON p.hMy = unit.hProperty
Left Outer Join PROPBUT13 ON p.hMy = PROPBUT13.hCode  
Left Outer Join PROPBUT8 ON p.hMy = PROPBUT8.hCode  
Inner Join Tenant ON p.hMy = tenant.hProperty
inner join attributexref attr on p.hmy = attr.hFileRecord and attr.iFileType = 3
inner join attributevalue av on attr.hattributevalue = av.hmy
Where  1= 1