无效的参数号码是什么错误

[jojoova]

我的一个dw，sql是这样的
select * from (
SELECT asy.xm as xm,
asy.zyh as zyh,
asy.cwh as cwh,
asy.bmh as bmh,
asy.nl as nl,
asy.ryrq as ryrq,
asy.cyrq as cyrq
FROM asy
WHERE CONVERT(varchar(12), cyrq,112 ) >= :ls_asycyrqS
and CONVERT(varchar(12), cyrq,112 ) <= :ls_asycyrqE
union all
select
asyc.xm as xm,
asyc.zyh as zyh,
asyc.cwh as cwh,
asyc.bmh as bmh,
asyc.nl as nl,
asyc.ryrq as ryrq,
asyc.cyrq as cyrq
from asyc
WHERE CONVERT(varchar(12), cyrq,112 ) >= :ls_asyccyrqS
and CONVERT(varchar(12), cyrq,112 ) <= :ls_asyccyrqE
) rr
order by bmh asc,cyrq asc
;
在传入值的时候提示sqlstate = s1093，无效的参数号码
但是如果我不加外面这层排序的，直接写成
SELECT asy.xm as xm,
asy.zyh as zyh,
asy.cwh as cwh,
asy.bmh as bmh,
asy.nl as nl,
asy.ryrq as ryrq,
asy.cyrq as cyrq
FROM asy
WHERE CONVERT(varchar(12), cyrq,112 ) >= :ls_asycyrqS
and CONVERT(varchar(12), cyrq,112 ) <= :ls_asycyrqE
union all
select
asyc.xm as xm,
asyc.zyh as zyh,
asyc.cwh as cwh,
asyc.bmh as bmh,
asyc.nl as nl,
asyc.ryrq as ryrq,
asyc.cyrq as cyrq
from asyc
WHERE CONVERT(varchar(12), cyrq,112 ) >= :ls_asyccyrqS
and CONVERT(varchar(12), cyrq,112 ) <= :ls_asyccyrqE
；
传值的时候就不会出现错误，是为什么啊？

[lucky_2005]

应该是不支持将查询结果作为一个数据表使用，其实你可以把排序直接放在dw里做，不要在sql里写的

[jojoova]

哦，我刚刚试了下，把外面那层select * from 拿掉，直接写成
SELECT asy.xm as xm,
asy.zyh as zyh,
asy.cwh as cwh,
asy.bmh as bmh,
asy.nl as nl,
asy.ryrq as ryrq,
asy.cyrq as cyrq
FROM asy
WHERE CONVERT(varchar(12), cyrq,112 ) >= :ls_asycyrqS
and CONVERT(varchar(12), cyrq,112 ) <= :ls_asycyrqE
union all
select
asyc.xm as xm,
asyc.zyh as zyh,
asyc.cwh as cwh,
asyc.bmh as bmh,
asyc.nl as nl,
asyc.ryrq as ryrq,
asyc.cyrq as cyrq
from asyc
WHERE CONVERT(varchar(12), cyrq,112 ) >= :ls_asyccyrqS
and CONVERT(varchar(12), cyrq,112 ) <= :ls_asyccyrqE

order by bmh asc,cyrq asc
就可以了，哈哈，结贴了