select a.meansurecode,
substr(last_day(to_date(a.statdate,'yyyy-mm-dd')), 1, 7) as statdate,
a.manualdatavalue,
b.manualdatavalue,
case
when a.datatype>b.datatype then 100-(a.datatype-b.datatype)/100*10
when a.datatype<b.datatype then 100+(b.datatype-a.datatype)/100*10
else a.datatype=b.datatype
end as score
from olap_manualdata a,
olap_manualdata b
where a.meansurecode ='F-PA-008'
and a.datatype='A'
and b.datatype='P'
and a.meansurecode=b.meansurecode
and a.comcode =b.comcode
and substr(a.statdate, 1, 4) = b.statdate;
报错：ERROR: operator does not exist: character varying - character varying
LINE 6: ... when a.datatype>b.datatype then 100-(a.datatype-b.datatyp...
^
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
********** 错误 **********
ERROR: operator does not exist: character varying - character varying
SQL 状态: 42883
指导建议:No operator matches the given name and argument type(s). You may need to add explicit type casts.
字符:224
语句那里错了 ？？？
------解决方案--------------------
else a.datatype=b.datatype 这里有问题，不应该出现=判断，只能换成a的值或b的值
------解决方案--------------------
- SQL code
select a.meansurecode, substr(last_day(to_date(a.statdate,'yyyy-mm-dd')), 1, 7) as statdate, a.manualdatavalue, b.manualdatavalue, case when a.datatype>b.datatype then 100-(a.datatype-b.datatype)/100*10 when a.datatype<b.datatype then 100+(b.datatype-a.datatype)/100*10 else b.datatype end as scorefrom olap_manualdata a, olap_manualdata bwhere a.meansurecode ='F-PA-008' and a.datatype='A' and b.datatype='P' and a.meansurecode=b.meansurecode and a.comcode =b.comcode and substr(a.statdate, 1, 4) = b.statdate;
------解决方案--------------------
else a.datatype=b.datatype 想想就明白了，这个是个查询语句，
a.datatype=b.datatype是赋值语句，赋值语句应该用在Update,insert语句中。
------解决方案--------------------
- SQL code
case when a.datatype > b.datatype then 100 - (a.datatype - b.datatype) / 100 * 10 when a.datatype < b.datatype then 100 + (b.datatype - a.datatype) / 100 * 10 else a.datatype = b.datatype ---这个有问题，else已经表示除了上面两种情况，a.datatype=b.datatype被oracle看成返回部分了 end as score
------解决方案--------------------
楼上说的对呀