sqlserver2000+pb :select Error:SQLSTATE = S1093 无效的参数号码

2008-01-25 01:36:21 1.1k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【MySQL-Bug】深入解析ERROR 1093：如何巧妙绕过UPDATE中的自引用表限制本文深入解析MySQL中的ERROR 1093错误，即'You can't specify target table for update in FROM clause'问题。通过分析错误原因，提供多种巧妙绕过限制的解决方案，包括使用中间表、JOIN语法和临时表等，并对比不同方案的性能差异，帮助开发者高效处理自引用表更新问题。阅读详情

我用pb 建立了一个比较复杂的数据窗口，传入4个参数 s_min,s_max,s_jz,s_ck
当变量改成常量没有问题，使用变量就会提示 select Error:SQLSTATE = S1093 无效的参数号码
数据库使用 sqlserver2000
sql语句如下：
select 大类编码,名称,
SUM(期初金额) AS 期初金额,
SUM(自购金额) AS 自购金额,
SUM(收入调整)AS 收入调整,
SUM(收入年累) AS 收入年累,
SUM(生产用料) AS 生产用料,
SUM(让价) AS 让价,
SUM(让价年累) AS 让价年累,
SUM(调整) AS 调整,
SUM(报损) AS 报损,
SUM(发出年累) AS 发出年累,
SUM(期末金额) AS 期末金额
from jcb_dlbm as b LEFT OUTER JOIN
(SELECT LEFT(器材编号, 2) AS 大类, SUM(期初金额) AS 期初金额, 0 AS 自购金额,
0 AS 收入调整, 0 AS 收入年累, 0 AS 生产用料, 0 AS 让价, 0 AS 让价年累, 0 AS 调整, 0 AS 报损,
0 AS 发出年累, 0 AS 期末金额
FROM dbo.Kc
WHERE (日期 = :s_min)
GROUP BY LEFT(器材编号, 2)
UNION ALL
SELECT LEFT(器材编号, 2) AS 大类, 0 AS 期初金额, SUM(计划金额) AS 自购金额,
0 AS 收入调整, 0 AS 收入年累, 0 AS 生产用料, 0 AS 让价, 0 AS 让价年累, 0 AS 调整, 0 AS 报损,
0 AS 发出年累, 0 AS 期末金额
FROM dbo.rk_d
WHERE (日期 >= :s_min) and (日期 <= :s_max) and 料单编号 in (select 料单编号 from rk_m where (日期 >= :s_min) and (日期 <= :s_max) and (cast(已登帐 as char ) like :s_jz) and (cast(已入库 as char) like :s_ck))
GROUP BY LEFT(器材编号, 2)
UNION ALL
SELECT LEFT(器材编号, 2) AS 大类, 0 AS 期初金额, 0 AS 自购金额,
0 AS 收入调整, SUM(计划金额) AS 收入年累, 0 AS 生产用料, 0 AS 让价, 0 AS 让价年累, 0 AS 调整, 0 AS 报损,
0 AS 发出年累, 0 AS 期末金额
FROM dbo.rk_d
WHERE (left(日期,2) = left(:s_min,2)) and 料单编号 in (select 料单编号 from rk_m where (left(日期,2) = left(:s_min,2))and (cast(已登帐 as char ) like :s_jz) and (cast(已入库 as char) like :s_ck))
GROUP BY LEFT(器材编号, 2)
UNION ALL
SELECT LEFT(器材编号, 2) AS 大类, 0 AS 期初金额,0 AS 自购金额,
0 AS 收入调整, 0 AS 收入年累, SUM(出库金额) AS 生产用料, 0 AS 让价, 0 AS 让价年累, 0 AS 调整, 0 AS 报损,
0 AS 发出年累, 0 AS 期末金额
FROM dbo.ck_d
WHERE (日期 >= :s_min) and (日期 <= :s_max) and 料单编号 in (select 料单编号 from ck_m where (日期 >= :s_min) and (日期 <= :s_max) and (cast(已登帐 as char ) like :s_jz) and (cast(已出库 as char) like :s_ck))
GROUP BY LEFT(器材编号, 2)
UNION ALL
SELECT LEFT(器材编号, 2) AS 大类, 0 AS 期初金额, 0 AS 自购金额,
0 AS 收入调整, 0 AS 收入年累, 0 AS 生产用料, 0 AS 让价, 0 AS 让价年累, 0 AS 调整, 0 AS 报损,
SUM(出库金额) AS 发出年累, 0 AS 期末金额
FROM dbo.ck_d
WHERE (left(日期,2) = left(:s_min,2)) and 料单编号 in (select 料单编号 from ck_m where (left(日期,2) = left(:s_min,2))and (cast(已登帐 as char ) like :s_jz) and (cast(已出库 as char) like :s_ck))
GROUP BY LEFT(器材编号, 2)
UNION ALL
SELECT LEFT(器材编号, 2) AS 大类, 0 AS 期初金额,0 AS 自购金额,
0 AS 收入调整, 0 AS 收入年累, 0 AS 生产用料, SUM(计划金额) AS 让价, 0 AS 让价年累, 0 AS 调整, 0 AS 报损,
0 AS 发出年累, 0 AS 期末金额
FROM dbo.jr_d
WHERE (日期 >= :s_min) and (日期 <= :s_max) and 料单编号 in (select 料单编号 from jr_m where ((日期 >= :s_min) and (日期 <= :s_max) and (cast(已登帐 as char ) like :s_jz) and (cast(已出库 as char) like :s_ck))
GROUP BY LEFT(器材编号, 2)
UNION ALL
SELECT LEFT(器材编号, 2) AS 大类, 0 AS 期初金额, 0 AS 自购金额,
0 AS 收入调整, 0 AS 收入年累, 0 AS 生产用料, 0 AS 让价, SUM(计划金额) AS 让价年累, 0 AS 调整, 0 AS 报损,
0 AS 发出年累, 0 AS 期末金额
FROM dbo.jr_d
WHERE (left(日期,2) = left(:s_min,2)) and 料单编号 in (select 料单编号 from jr_m where (left(日期,2) = left(:s_min,2))and (cast(已登帐 as char ) like :s_jz) and (cast(已出库 as char) like :s_ck))
GROUP BY LEFT(器材编号, 2)
union all
SELECT LEFT(器材编号, 2) AS 大类, 0 AS 期初金额, 0 AS 自购金额,
0 AS 收入调整, 0 AS 收入年累, 0 AS 生产用料, 0 AS 让价, 0 AS 让价年累, 0 AS 调整, 0 AS 报损,
0 AS 发出年累, sum(库存金额) AS 期末金额
FROM dbo.Kc
WHERE (日期 = :s_max)
GROUP BY LEFT(器材编号, 2)
) as a on b.大类编码= a.大类
group by b.大类编码,名称
order by b.大类编码

...全文