先看以下两个语句:
select decode(grouping(subsidiaryid),1,'all_amount',subsidiaryid)
,serviceproviderid,sum(amount)
from settlement where billingcycleid=44
group by rollup(serviceproviderid,subsidiaryid)select decode(grouping(subsidiaryid),0,subsidiaryid,'all_amount')
,serviceproviderid,sum(amount)
from settlement where billingcycleid=44
group by rollup(serviceproviderid,subsidiaryid)现在疑问是,为什么第一句是执行成功的,而第二句是错误的,到底是什么原因???
select decode(grouping(subsidiaryid),1,'all_amount',subsidiaryid)
,serviceproviderid,sum(amount)
from settlement where billingcycleid=44
group by rollup(serviceproviderid,subsidiaryid)select decode(grouping(subsidiaryid),0,subsidiaryid,'all_amount')
,serviceproviderid,sum(amount)
from settlement where billingcycleid=44
group by rollup(serviceproviderid,subsidiaryid)现在疑问是,为什么第一句是执行成功的,而第二句是错误的,到底是什么原因???
首先提示的错误是“ORA_01722:无效数字”。
对于4楼提到的错误,不存在,因为改了用以下的方法:
select decode(grouping(subsidiaryid),1,'all_amount',0,subsidiaryid)
,serviceproviderid,sum(amount)
from settlement where billingcycleid=44
group by rollup(serviceproviderid,subsidiaryid) select decode(grouping(subsidiaryid),0,subsidiaryid,1,'all_amount')
,serviceproviderid,sum(amount)
from settlement where billingcycleid=44
group by rollup(serviceproviderid,subsidiaryid)
照样,第一个成功,第二个失败。
谢谢楼上几位。