用了多次unpivot函数后就报错
如下语句查询不会报错
with CTE as(
select
bo.ORGNAME as ORGNAME,
t.xiaoshou as 权益前金额,
t.xiaoshou*(bo.HOLDSTAKE/100) as 权益后金额
from (
select CONTRACTTYPE,orgid,CONTRACTCODE,xiaoshou,jidu from LC_LOANCONTRACT unpivot (xiaoshou for jidu in (LOANAMOUNT,USEDAMOUNT, BACKAMOUNT)) where STATUS=1) t
left join BS_ORGANIZATION bo on t.orgid=bo.id
where ORGLEVELCODE like '0000100006%')
select a.ORGNAME from cte a
但是再套一层unpivot后。。。用表别名就报错了,如下语句就报错了。。。。
with CTE as(
select
bo.ORGNAME as ORGNAME,
t.xiaoshou as 权益前金额,
t.xiaoshou*(bo.HOLDSTAKE/100) as 权益后金额
from (
select CONTRACTTYPE,orgid,CONTRACTCODE,xiaoshou,jidu from LC_LOANCONTRACT unpivot (xiaoshou for jidu in (LOANAMOUNT,USEDAMOUNT, BACKAMOUNT)) where STATUS=1) t
left join BS_ORGANIZATION bo on t.orgid=bo.id
where ORGLEVELCODE like '0000100006%')
select a.ORGNAME from cte a unpivot (xiaoshou_b for jidu_b in (权益前金额,权益后金额))
但是如果把最后的查询a.ORGNAME 改为*那么不会报错。是正确的。。这个是什么原因