无法绑定由多个部分组成的标识符
--4.药房发药单
SELECT
B.BCK01, A.BillNO, A.DeptID
, CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称
, B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo
, B.Quantity AS Quantity4, B.PurchasePrice AS PurchasePrice4, B.PurchaseAmount AS PurchaseAmount4
, K.PresellPrice AS PresellPrice4, K.PresellPrice * B.Quantity AS PresellAmount4
, B.VAJ33 AS VAJ33A, B.VAJ38 AS VAJ38A
, 0 AS Quantity5, 0 AS PurchasePrice5, 0 AS PurchaseAmount5
, 0 AS PresellPrice5, 0 AS PresellAmount5
, 0 AS VAJ33B, 0 AS VAJ38B
, A.AuditingDate, A.Assessor
, A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark
, Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15
, K.ProduceDate, K.ExpiryDate
,F.VAA05
INTO #TMP_DPD
FROM DPD1 A join DPD2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID
join VAA1 F ON F.VAA01 = B.VAA01
JOIN BBY1 Y ON Y.BBY01 = B.BBY01
left join BAX1 Q ON Q.BAX01 = Y.BAX01
join BAG1 G ON G.BBY01 = B.BBY01
left join BBX1 X ON X.BBX01 = G.BBX01
left join BBT1 T ON T.BBX01 = X.BBX01
left join BDO1 O ON O.BDO01 = X.BDO01
JOIN DPK1 K ON K.ID = B.DPK_ID
WHERE B.BCK01 = @PharmacyId
and B.Quantity > 0
and A.AuditingDate BETWEEN @BeginDate AND @EndDate
AND Y.BBY05 like @BBY05 + '%'
UNION ALL
SELECT
B.BCK01, A.BillNO, A.DeptID
, CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称
, B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo
, B.Quantity AS Quantity4, B.PurchasePrice AS PurchasePrice4, B.PurchaseAmount AS PurchaseAmount4
, K.PresellPrice AS PresellPrice4, K.PresellPrice * B.Quantity AS PresellAmount4
, B.VAJ33 AS VAJ33A, B.VAJ38 AS VAJ38A
, 0 AS Quantity5, 0 AS PurchasePrice5, 0 AS PurchaseAmount5
, 0 AS PresellPrice5, 0 AS PresellAmount5
, 0 AS VAJ33B, 0 AS VAJ38B
, A.AuditingDate, A.Assessor
, A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark
, Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15
, K.ProduceDate, K.ExpiryDate
,F.VAA05
FROM DPD1_2 A join DPD2_2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID
join VAA1 F ON F.VAA01 = B.VAA01 JOIN BBY1 Y ON Y.BBY01 = B.BBY01
left join BAX1 Q ON Q.BAX01 = Y.BAX01
join BAG1 G ON G.BBY01 = B.BBY01
left join BBX1 X ON X.BBX01 = G.BBX01
left join BBT1 T ON T.BBX01 = X.BBX01
left join BDO1 O ON O.BDO01 = X.BDO01
JOIN DPK1 K ON K.ID = B.DPK_ID
WHERE B.BCK01 = @PharmacyId
and B.Quantity > 0
and A.AuditingDate BETWEEN @BeginDate AND @EndDate
AND Y.BBY05 like @BBY05 + '%'
--5.药房退药单
UNION ALL
SELECT
B.BCK01, A.BillNO, A.DeptID
, CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称
, B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo
, 0, 0, 0, 0, 0
, 0, 0
, -B.Quantity, B.PurchasePrice, -B.PurchaseAmount, K.PresellPrice, -K.PresellPrice * B.Quantity
, B.VAJ33 AS VAJ33B, B.VAJ38 AS VAJ38B
, A.AuditingDate, A.Assessor
, A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark
, Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15
, K.ProduceDate, K.ExpiryDate
,F.VAA05
FROM DPD1 A join DPD2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID
join VAA1 F ON F.VAA01 = B.VAA01JOIN BBY1 Y ON Y.BBY01 = B.BBY01
Left join BAX1 Q ON Q.BAX01 = Y.BAX01
join BAG1 G ON G.BBY01 = B.BBY01
left join BBX1 X ON X.BBX01 = G.BBX01
left join BBT1 T ON T.BBX01 = X.BBX01
left join BDO1 O ON O.BDO01 = X.BDO01
JOIN DPK1 K ON K.ID = B.DPK_ID
WHERE B.Quantity < 0 AND B.BCK01 = @PharmacyId
and A.AuditingDate BETWEEN @BeginDate AND @EndDate
AND Y.BBY05 like @BBY05 + '%'
UNION ALL
SELECT
B.BCK01, A.BillNO, A.DeptID
, CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称
, B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo
, 0, 0, 0, 0, 0
, 0, 0
, -B.Quantity, B.PurchasePrice, -B.PurchaseAmount, K.PresellPrice, -K.PresellPrice * B.Quantity
, B.VAJ33 AS VAJ33B, B.VAJ38 AS VAJ38B
, A.AuditingDate, A.Assessor
, A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark
, Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15
, K.ProduceDate, K.ExpiryDate
,F.VAA05
FROM DPD1_2 A join DPD2_2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID
join VAA1 F ON F.VAA01 = B.VAA01JOIN BBY1 Y ON Y.BBY01 = B.BBY01
Left join BAX1 Q ON Q.BAX01 = Y.BAX01
join BAG1 G ON G.BBY01 = B.BBY01
left join BBX1 X ON X.BBX01 = G.BBX01
left join BBT1 T ON T.BBX01 = X.BBX01
left join BDO1 O ON O.BDO01 = X.BDO01
JOIN DPK1 K ON K.ID = B.DPK_ID
WHERE B.Quantity < 0 AND B.BCK01 = @PharmacyId
and A.AuditingDate BETWEEN @BeginDate AND @EndDate
AND Y.BBY05 like @BBY05 + '%'
SELECT A.BBY04 AS 药品_编号,F.VAA05 AS 姓名, A.BBY05 AS 药品_品名, A.BBY06 AS 药品_规格, A.Unit AS 单位
, A.BatchNo AS 产品批号, A.DeptID AS 部门ID, A.部门名称, A.AuditingDate AS 记帐时间
, A.Quantity4 AS 药房发药单_数量, A.PresellAmount4 AS 药房发药单_零价金额
, A.Quantity5 AS 药房退药单_数量, A.PresellAmount5 AS 药房退药单_零价金额
, A.Quantity4 - A.Quantity5 AS 实际发药_数量
, A.PresellAmount4 - A.PresellAmount5 AS 实际发药_零价金额
, A.BBE02 AS 产地, A.BAP02 AS 剂型, A.BAX03 AS 收入项目, A.BDO03 AS 药品分类
, A.BAG15 AS 批准文号, A.ProduceDate AS 生产日期, A.ExpiryDate AS 有效期至
, A.DPK_ID AS 批次号, A.Assessor AS 记帐人, A.BillNO AS 单号, A.Accepter AS 领药人, A.Dispenser AS 批准人
, A.Remark AS 备注
FROM #TMP_DPD A
WHERE 部门名称 LIKE @BCK03 + '%'
DROP TABLE #TMP_DPD
红色字体语句是我加的，为什么一加上就报“无法绑定由多个部分组成的标识符” 这个错？
[解决办法]
楼主把最后那个单独的查询不要放进来，直接看最上边的查询。
- SQL code
--为了方便，我把带变量的条件去掉了SELECT B.BCK01, A.BillNO, A.DeptID , CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称 , B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo , B.Quantity AS Quantity4, B.PurchasePrice AS PurchasePrice4, B.PurchaseAmount AS PurchaseAmount4 , K.PresellPrice AS PresellPrice4, K.PresellPrice * B.Quantity AS PresellAmount4 , B.VAJ33 AS VAJ33A, B.VAJ38 AS VAJ38A , 0 AS Quantity5, 0 AS PurchasePrice5, 0 AS PurchaseAmount5 , 0 AS PresellPrice5, 0 AS PresellAmount5 , 0 AS VAJ33B, 0 AS VAJ38B , A.AuditingDate, A.Assessor , A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark , Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15 , K.ProduceDate, K.ExpiryDate ,F.VAA05INTO #TMP_DPDFROM DPD1 A join DPD2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join VAA1 F ON F.VAA01 = B.VAA01 JOIN BBY1 Y ON Y.BBY01 = B.BBY01 left join BAX1 Q ON Q.BAX01 = Y.BAX01join BAG1 G ON G.BBY01 = B.BBY01left join BBX1 X ON X.BBX01 = G.BBX01left join BBT1 T ON T.BBX01 = X.BBX01left join BDO1 O ON O.BDO01 = X.BDO01JOIN DPK1 K ON K.ID = B.DPK_IDWHERE B.Quantity > 0 UNION ALLSELECT B.BCK01, A.BillNO, A.DeptID , CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称 , B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo , B.Quantity AS Quantity4, B.PurchasePrice AS PurchasePrice4, B.PurchaseAmount AS PurchaseAmount4 , K.PresellPrice AS PresellPrice4, K.PresellPrice * B.Quantity AS PresellAmount4 , B.VAJ33 AS VAJ33A, B.VAJ38 AS VAJ38A , 0 AS Quantity5, 0 AS PurchasePrice5, 0 AS PurchaseAmount5 , 0 AS PresellPrice5, 0 AS PresellAmount5 , 0 AS VAJ33B, 0 AS VAJ38B , A.AuditingDate, A.Assessor , A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark , Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15 , K.ProduceDate, K.ExpiryDate,F.VAA05FROM DPD1_2 A join DPD2_2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join VAA1 F ON F.VAA01 = B.VAA01 JOIN BBY1 Y ON Y.BBY01 = B.BBY01 left join BAX1 Q ON Q.BAX01 = Y.BAX01join BAG1 G ON G.BBY01 = B.BBY01left join BBX1 X ON X.BBX01 = G.BBX01left join BBT1 T ON T.BBX01 = X.BBX01left join BDO1 O ON O.BDO01 = X.BDO01JOIN DPK1 K ON K.ID = B.DPK_IDWHERE B.Quantity > 0--5.药房退药单UNION ALLSELECT B.BCK01, A.BillNO, A.DeptID , CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称 , B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo , 0, 0, 0, 0, 0 , 0, 0 , -B.Quantity, B.PurchasePrice, -B.PurchaseAmount, K.PresellPrice, -K.PresellPrice * B.Quantity , B.VAJ33 AS VAJ33B, B.VAJ38 AS VAJ38B , A.AuditingDate, A.Assessor , A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark , Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15 , K.ProduceDate, K.ExpiryDate,F.VAA05FROM DPD1 A join DPD2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join VAA1 F ON F.VAA01 = B.VAA01 JOIN BBY1 Y ON Y.BBY01 = B.BBY01Left join BAX1 Q ON Q.BAX01 = Y.BAX01join BAG1 G ON G.BBY01 = B.BBY01left join BBX1 X ON X.BBX01 = G.BBX01left join BBT1 T ON T.BBX01 = X.BBX01left join BDO1 O ON O.BDO01 = X.BDO01JOIN DPK1 K ON K.ID = B.DPK_IDWHERE B.Quantity < 0 UNION ALLSELECT B.BCK01, A.BillNO, A.DeptID , CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 WHERE BCK1.BCK01 = A.DeptID) END 部门名称 , B.VAA01, B.VAJ01, B.BBY01, Y.BBY04, Y.BBY05, Y.BBY06, B.Unit, B.PackSize, B.BatchNo , 0, 0, 0, 0, 0 , 0, 0 , -B.Quantity, B.PurchasePrice, -B.PurchaseAmount, K.PresellPrice, -K.PresellPrice * B.Quantity , B.VAJ33 AS VAJ33B, B.VAJ38 AS VAJ38B , A.AuditingDate, A.Assessor , A.Accepter, A.Dispenser, A.Sender, A.Ratifier, A.TradeID, B.DPK_ID, B.Remark , Y.BBE02, T.BAP02, Q.BAX03, O.BDO03, G.BAG15 , K.ProduceDate, K.ExpiryDate,F.VAA05FROM DPD1_2 A join DPD2_2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join VAA1 F ON F.VAA01 = B.VAA01 JOIN BBY1 Y ON Y.BBY01 = B.BBY01Left join BAX1 Q ON Q.BAX01 = Y.BAX01join BAG1 G ON G.BBY01 = B.BBY01left join BBX1 X ON X.BBX01 = G.BBX01left join BBT1 T ON T.BBX01 = X.BBX01left join BDO1 O ON O.BDO01 = X.BDO01JOIN DPK1 K ON K.ID = B.DPK_IDWHERE B.Quantity < 0/*****************************编译显示：命令已成功完成！