写报表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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

😉😐😡😈🙂😯🙁🙄😛😳😮:mrgreen:😆💡😀👿😥😎😕

验证码 *