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

xueru9999 2008-01-25 01:36:21

我用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.大类编码

...全文