原sql为
select
floorEntry.ffloor as floor,build.fname_l2 as build,case when buildingProperty.fname_l2 = '业主自营' then buildingProperty.fname_l2 else '非业主自营' end as property ,
sum(room.froomarea) as tnArea,count(*) as roomCount
from
T_SHE_Room room left join T_SHE_Building build on room.fbuildingid=build.fid
left join T_SHE_BuildingFloorEntry floorEntry on room.fbuildingfloorid=floorEntry.fid
left join T_SHE_BuildingProperty buildingProperty on buildingProperty.fid = room.fbuildingpropertyid
group by floorEntry.ffloor,build.fname_l2,buildingProperty.fname_l2 order by 1,2,3
排查后发现case语句中T_SHE_BuildingProperty的fname_l2字段的定义为NVARCHAR2类型的
与‘非业主自营’不匹配,于是将sql改为如下,至此,问题解决
select
floorEntry.ffloor as floor,build.fname_l2 as build,case when buildingProperty.fname_l2 = '业主自营' then to_char(buildingProperty.fname_l2) else '非业主自营' end as property ,
sum(room.froomarea) as tnArea,count(*) as roomCount
from
T_SHE_Room room left join T_SHE_Building build on room.fbuildingid=build.fid
left join T_SHE_BuildingFloorEntry floorEntry on room.fbuildingfloorid=floorEntry.fid
left join T_SHE_BuildingProperty buildingProperty on buildingProperty.fid = room.fbuildingpropertyid
group by floorEntry.ffloor,build.fname_l2,buildingProperty.fname_l2 order by 1,2,3