SSRS奇怪报错Could not update a list of fields for the quer.

今天遇到一个奇怪的问题，SSRS我觉得是个半成品，很多东西都搞不了。写了一段SQL，本来SQL写法都有点怪了，如下

 

WITH TMP_A AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY L1_WorkOrderType,L2_TaskCode ORDER BY L4_QTY)CC FROM

(SELECT

B.B1_APP_TYPE_ALIAS AS L1_WorkOrderType,

WO.TASK_CODE AS L2_TaskCode,

WO.COST_ITEM AS L3_CostItem,

SUM(WO.COST_QUANTITY) AS L4_QTY,

MAX(WO.COST_UNIT_TYPE) AS L5_UOM

FROM B1PERMIT B

LEFT JOIN

GWORK_ORDER_COSTING WO

on b.SERV_PROV_CODE = WO.SERV_PROV_CODE

AND B.B1_PER_ID1 = WO.B1_PER_ID1

AND B.B1_PER_ID2 = WO.B1_PER_ID2

AND B.B1_PER_ID3 = WO.B1_PER_ID3

WHERE (B.B1_ALT_ID = 'WO-2013-00145' or B.B1_ALT_ID = 'WO-2013-00142')

AND B.SERV_PROV_CODE = 'ARAPAHOE'

GROUP BY B.B1_APP_TYPE_ALIAS,WO.TASK_CODE,WO.COST_ITEM)T1),

TMP_B AS

(SELECT *,ROW_NUMBER() OVER(PARTITiON BY L1_WorkOrderType,L2_TaskCode ORDER BY L6_PartsQTY)CC

FROM

(select

b.B1_APP_TYPE_ALIAS as L1_WorkOrderType,

p.TASK_CODE as L2_TaskCode,

P.PART_DESCRIPTION AS L8_Description,

SUM(p.QUANTITY) as L6_PartsQTY,

MAX(P.UNIT_OF_MEASUREMENT) AS L7_PartsUOM

FROM B1PERMIT B

LEFT JOIN

GPART_TRANSACTION P

on b.SERV_PROV_CODE = p.SERV_PROV_CODE

AND B.B1_PER_ID1 = p.B1_PER_ID1

AND B.B1_PER_ID2 = p.B1_PER_ID2

AND B.B1_PER_ID3 = p.B1_PER_ID3

WHERE (B.B1_ALT_ID = 'WO-2013-00145' or B.B1_ALT_ID = 'WO-2013-00142')

AND B.SERV_PROV_CODE = 'ARAPAHOE'

and p.TRANSACTION_TYPE = 'ISSUE'

GROUP BY b.B1_APP_TYPE_ALIAS,

p.TASK_CODE,

P.PART_DESCRIPTION) T2)

SELECT COALESCE(A.L1_WorkOrderType,B.L1_WorkOrderType),COALESCE(A.L2_TaskCode,B.L2_TaskCode),A.L3_CostItem,A.L4_QTY,A.L5_UOM,B.L6_PartsQTY,B.L7_PartsUOM,B.L8_Description

FROM TMP_A A FULL OUTER JOIN TMP_B B

ON A.L1_WorkOrderType = B.L1_WorkOrderType

AND A.L2_TaskCode = B.L2_TaskCode

AND A.CC = B.CC

大致意思就是TMP_A表与TAMP_B表关联条件是TYPE和CODE,且两张表需要全连接，即TAMP_B中的678字段需要对应的填冲到TAMP_A表中，所以我构造了一个CC字段，分组后再给一个序号也作为其连接条件才能成功的将678接到345后面，否则它会产生迪卡尔积的样子。

 

当一切就绪后，放到SSRS中以后，以上脚本直接放到数据库当中无任何问题，但是在SSRS中就出错了，如下

 

经过几次测试结果是COALESCE(A.L1_WorkOrderType,B.L1_WorkOrderType),COALESCE(A.L2_TaskCode,B.L2_TaskCode) 字段没取别名引起的，我呸。

再来SSRS其实经常会出现类似的错误，可能多嵌套几层就会导致无法运行了，离水晶报表感觉还是差好远的。

今天星期三啦，一个星期又过去一半多了，哦耶~明儿个深圳又要下雨啦，凉快啦~，字里好热，希望家里也能下雨啊~我娘在家好热的说~啊哦~

要下班啦，哦耶~

 

转载于:https://www.cnblogs.com/medci/p/3228340.html