哪位帮看下这存储过程毛病出在哪里
alter PROCEDURE sellorder
as
select
sod.autoid as 'sellid',
vm.modid as 'proid',
qm.autoid as 'bjid',
qmc.id as 'jyid',
qmr.id as 'blpid',
vmo.modid as 'bproid',
sod.cdemandcode as '项目代号',
sod.cdemandmemo as '项目名称',
sod.csocode as '销售订单号',
sod.cinvcode as '存货编码',
i.cinvname as '存货名称',
sod.iquantity as '订单数量',
sod.dPreDate as '预发货日期',
so.dcreatesystime as '订单制单日期',
so.dverifydate as '订单审核日期',
bom.CreateDate as 'BOM创建日期',
bom.Relsdate as 'BOM审核日期',
bom.modifydate as 'BOM最后变更日期',
vm.mocode as '生产订单号',
VM.invcode as '母件编码',
i.cinvname as '母件名称',
vm.qty as '生产订单数量',
vm.createdate as '生产订单制单日期',
vm.Relstime as '生产订单审核日期',
qm.cinspectcode as '报检单号',
qm.fquantity as '报检数量',
qm.dmaketime as '报检单制单日期',
qm.dverifytime as '报检单审核日期',
qmc.ccheckcode as '检验单号',
qmc.FREGQUANTITY as '合格接收数量',
qmc.dmaketime as '检验单制单日期',
qmc.dverifytime as '检验单审核日期',
qmr.crejectcode as '不良品处理单号',
qmr.FSUMQUANTITY as '待处理不良品数量',
qmr.DMAKETIME as '不良品处理单制单日期',
qmr.DVERIFYTIME as '不良品处理单审核日期',
vmo.mocode as '不良品生产订单',
re.ccode as '入库单号',
re.iquantity as '入库数量',
re.dnmaketime as '入库单制单日期',
re.dnverifytime as '入库单审核日期',
qq.cdlcode as '发货单号',
qq.iQuantity as '发货数量',
qq.ddate as '发货单制单日期',
sb.cSBVCode as '销售发票号',
sb.ddate as '开票日期',
sb.dverifydate as '发票审核日期'
into #tmps
from SO_SODetails sod
left join Inventory i
on i.cinvcode=sod.cinvcode
left join SO_SOMain so on so.csocode=sod.csocode
left join QM_QREFsaLES qq
on qq.csocode =sod.csocode and qq.cinvcode=sod.cinvcode
left join saleBillVouch sb
on sb.cSOCode = sod.csocode
left join v_mom_modetail vm
on vm.demandcode=sod.cdemandcode and (vm.invcode=sod.cinvcode or vm.invcode in
(
select invcode from v_bom_opcomponent_rpt where bomid in
(select bomid from v_bom_parent_rpt where invcode=sod.cinvcode)
)
)
left join Inventory i1
on i1.cinvcode=vm.invcode
left join
(
select vb.invcode, bb.CreateDate,bb. RelsDate,bb.modifydate from v_bom_parent_rpt vb ,bom_bom bb
where vb.bomid=bb.bomid and bb.bomtype=1
) bom
on (bom.invcode=sod.cinvcode or bom.invcode=vm.invcode)
left join QM_QPROINSPECTLIST qm
on qm.sourceautoid=vm.modid
left join QMCHECKVOUCHER qmc
on qmc. inspectautoid =qm.autoid
left join QMREJECTVOUCHER qmr
on qmr.sourceautoid =qmc. sourceautoid
left join v_mom_modetail vmo
on vmo.sourcemodid=qmr.sourceautoid
left join recordinlist re
on re.icheckidbaks=qmc.id
where sod.cdemandcode<>'Systemdefault' -- and sod.cdemandcode='HT-1111009'
order by sod.sellid desc
alter table #tmps add row int identity
select selx=(select count(1) from #tmps t where t.sellid=tt.sellid and t.row<=tt.row),* from #tmps tt
--exec sellorder
提示我row无效
[解决办法]
alter PROCEDURE sellorderas select sod.autoid as 'sellid' , vm.modid as 'proid' , qm.autoid as 'bjid' , qmc.id as 'jyid' , qmr.id as 'blpid' , vmo.modid as 'bproid' , sod.cdemandcode as '项目代号' , sod.cdemandmemo as '项目名称' , sod.csocode as '销售订单号' , sod.cinvcode as '存货编码' , i.cinvname as '存货名称' , sod.iquantity as '订单数量' , sod.dPreDate as '预发货日期' , so.dcreatesystime as '订单制单日期' , so.dverifydate as '订单审核日期' , bom.CreateDate as 'BOM创建日期' , bom.Relsdate as 'BOM审核日期' , bom.modifydate as 'BOM最后变更日期' , vm.mocode as '生产订单号' , VM.invcode as '母件编码' , i.cinvname as '母件名称' , vm.qty as '生产订单数量' , vm.createdate as '生产订单制单日期' , vm.Relstime as '生产订单审核日期' , qm.cinspectcode as '报检单号' , qm.fquantity as '报检数量' , qm.dmaketime as '报检单制单日期' , qm.dverifytime as '报检单审核日期' , qmc.ccheckcode as '检验单号' , qmc.FREGQUANTITY as '合格接收数量' , qmc.dmaketime as '检验单制单日期' , qmc.dverifytime as '检验单审核日期' , qmr.crejectcode as '不良品处理单号' , qmr.FSUMQUANTITY as '待处理不良品数量' , qmr.DMAKETIME as '不良品处理单制单日期' , qmr.DVERIFYTIME as '不良品处理单审核日期' , vmo.mocode as '不良品生产订单' , re.ccode as '入库单号' , re.iquantity as '入库数量' , re.dnmaketime as '入库单制单日期' , re.dnverifytime as '入库单审核日期' , qq.cdlcode as '发货单号' , qq.iQuantity as '发货数量' , qq.ddate as '发货单制单日期' , sb.cSBVCode as '销售发票号' , sb.ddate as '开票日期' , sb.dverifydate as '发票审核日期' into #tmps from SO_SODetails sod left join Inventory i on i.cinvcode = sod.cinvcode left join SO_SOMain so on so.csocode = sod.csocode left join QM_QREFsaLES qq on qq.csocode = sod.csocode and qq.cinvcode = sod.cinvcode left join saleBillVouch sb on sb.cSOCode = sod.csocode left join v_mom_modetail vm on vm.demandcode = sod.cdemandcode and ( vm.invcode = sod.cinvcode or vm.invcode in ( select invcode from v_bom_opcomponent_rpt where bomid in ( select bomid from v_bom_parent_rpt where invcode = sod.cinvcode ) ) ) left join Inventory i1 on i1.cinvcode = vm.invcode left join ( select vb.invcode , bb.CreateDate , bb.RelsDate , bb.modifydate from v_bom_parent_rpt vb , bom_bom bb where vb.bomid = bb.bomid and bb.bomtype = 1 ) bom on ( bom.invcode = sod.cinvcode or bom.invcode = vm.invcode ) left join QM_QPROINSPECTLIST qm on qm.sourceautoid = vm.modid left join QMCHECKVOUCHER qmc on qmc.inspectautoid = qm.autoid left join QMREJECTVOUCHER qmr on qmr.sourceautoid = qmc.sourceautoid left join v_mom_modetail vmo on vmo.sourcemodid = qmr.sourceautoid left join recordinlist re on re.icheckidbaks = qmc.id where sod.cdemandcode <> 'Systemdefault' -- and sod.cdemandcode='HT-1111009'order by sod.sellid descalter table #tmps add [row] int identity(1,1)--直接换成把identity(1,1) as row加到SELECT查询中试试select selx = ( select count(1) from t b where b.sellid = a.sellid and b.row <= a.row ) , *from t a --我这儿试了没问题啊
