Oracle怎么老是报12704,java笔记 写报表sql时遇到的ora-12704错误解决

原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