视图中语句直接在查询分析器中运行正确,但在查询分析器中用select * from vp_aa提示出错

eracn 南通爱康太阳能器材有限公司 职员  2003-12-12 04:09:12
服务器: 消息 8624,级别 16,状态 13,行 1
Internal SQL Server error.

语句通过UNION连接,分别屏蔽后执行select * from vp_aa一切正常。
在视图编辑过程中无语法错误。

...全文
24 12 打赏 收藏 举报
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
eracn 2003-12-16
是。因为我是将前一个数据库做备份以后恢复的。
而且我还特地查看了相关的表
  • 打赏
  • 举报
回复
wzh1215 2003-12-16
你确定两者的数据结构完全一样吗?
  • 打赏
  • 举报
回复
eracn 2003-12-16
在另一个数据库运行正常。
两者的数据结构一致
  • 打赏
  • 举报
回复
Arionma 2003-12-13
把上面语句中的字段全部用table.field的格式完整的表示出来试试。
  • 打赏
  • 举报
回复
eracn 2003-12-13
问题在于我将payment_totaltranscost =( select round(sum(pcontract_transcost),2) from d_pcontract where pur_id=a.id)屏蔽后执行没错误。

那错误提示表示什么意思啊?
  • 打赏
  • 举报
回复
ghosthjt 2003-12-13
这么长,哪有时间看呀。
  • 打赏
  • 举报
回复
nobelpu 2003-12-13
我的建议===>表结构重设计.
  • 打赏
  • 举报
回复
txlicenhe 2003-12-12
自己慢慢调试比较好。
  • 打赏
  • 举报
回复
vileboy 2003-12-12
你先两个表连接,如果没有错误,再3个表,再4个表,一直到你要的结果
去试试
  • 打赏
  • 举报
回复
eracn 2003-12-12
union all
SELECT a.id,a.a_id,d.delivery_id,a.e_style,a.status,a.emp_id,c.dep_id,c.agentemp_id,c.agentdep_id,
b.sup_id,f.sup_name,b.mete_id,mete_name = g.name,mete_desc = g.desc_name,e.delivery_ratestatus,
pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.delivery_rate end,
d.delivery_currency,
mete_price = convert(decimal(18,6),round(b.mete_rateprice/(1+e.delivery_rate),6)),--b.mete_price,
b.mete_rateprice,
payment_metequantity =b.payment_metequantity,
payment_receivedquantity = e.pitem_quantity ,
payment_metetotalamount=convert(decimal(18,4),round(sum(convert(decimal(18,6),round(b.mete_rateprice/(1.00+CASE WHEN a.productclass='固定资产' then 0.0000 else e.delivery_rate end),6))*b.payment_metequantity),2)),
e.pitem_quantitypercent,
payment_pcontract_max=convert(decimal(18,6),e.pitem_quantity*(1+e.pitem_quantitypercent)),
payment_usedquantity = round(sum(CASE WHEN j.payment_quantity IS NULL THEN 0 ELSE j.payment_quantity END),6),
payment_usedamount_hk=round(sum(CASE WHEN j.payment_amount IS NULL THEN 0 ELSE j.payment_amount END),6),
payment_usedamount = round(sum(CASE WHEN j.payment_amount+j.payment_metetax IS NULL THEN 0 ELSE j.payment_amount+j.payment_metetax END),6),
-- payment_totaltranscost = CASE WHEN d.delivery_transcost IS NULL THEN 0 ELSE d.delivery_transcost END,
payment_totaltranscost = ( select sum(delivery_transcost) from d_partialdelivery where pur_id=a.id),
payment_usedtranscost=(select sum(payment_transcosttotal) from d_paymentapplypurcontent where d_paymentapplypurcontent.pur_id=d.pur_id),
payment_marginpercent = CASE WHEN d.delivery_marginpercent IS NULL THEN 0 ELSE d.delivery_marginpercent END,
payment_usedmargin = round(sum(CASE WHEN i.payment_margin IS NULL THEN 0 ELSE i.payment_margin END),6),
term_id = d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,k.sup_bamount
FROM ((dbo.vp_pur_mete_id a join dbo.vp_payment_receivelist b on a.pur_id = b.pur_id and b.o_mete_id = a.mete_id
join dbo.vp_agentemp c on a.emp_id = c.emp_id
join dbo.d_partialdelivery d on a.e_style = 1 and d.pur_id = a.id and d.sup_id = b.sup_id
and d.delivery_status = 3 and d.delivery_id = b.pcontract_id
join dbo.d_partialdeliverycontent e on d.delivery_id = e.delivery_id and b.mete_id = e.pitem_id and e.o_pitem_id = a.mete_id and
e.delivery_id = b.pcontract_id

join dbo.c_supplier f on b.sup_id = f.sup_id and d.sup_id = f.sup_id
join dbo.vp_mete_item g on b.mete_id = g.id and e.pitem_id = g.id)
left join c_supplybalance k on a.pur_id = k.pur_id and
b.pur_id = k.pur_id and
b.sup_id = k.sup_id and
b.pcontract_id = k.pcontract_id and
d.pur_id = k.pur_id and
d.delivery_id = k.pcontract_id and
d.sup_id = k.sup_id and
e.delivery_id = k.pcontract_id and
f.sup_id = k.sup_id)
left join
(dbo.d_paymentapplysupplycontent h join dbo.d_paymentapplypurcontent i on
rtrim(ltrim(h.payment_id)) = rtrim(ltrim(i.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(i.payment_invoiceid))
join dbo.d_paymentapplymetecontent j on rtrim(ltrim(h.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(i.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.pur_id)) = rtrim(ltrim(j.pur_id)) and rtrim(ltrim(i.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) ) on
rtrim(ltrim(a.id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(a.id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(b.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(b.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(b.mete_id)) = rtrim(ltrim(j.mete_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(b.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(d.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(d.delivery_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.delivery_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.delivery_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(e.delivery_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pitem_id)) = rtrim(ltrim(j.mete_id))
WHERE a.status = 12 and
a.e_style =1 and
(SELECT count(pur_id) FROM dbo.d_paymentapplypurcontent u,dbo.d_paymentapply v
WHERE u.pur_id = a.id and u.pcontract_id = d.delivery_id and u.payment_id = v.payment_id and v.payment_status >1 and u.payment_type = 0) = 0
group by a.id,d.pur_id,a.a_id,d.delivery_id,a.e_style,a.status,a.emp_id,c.dep_id,c.agentemp_id,c.agentdep_id,
b.sup_id,f.sup_name,b.mete_id,g.name, g.desc_name,e.delivery_ratestatus,
e.delivery_rate,
d.delivery_currency,
e.delivery_rate,
b.mete_rateprice,
b.payment_metequantity,
e.pitem_quantity ,
e.pitem_quantitypercent,
d.delivery_transcost,
d.delivery_marginpercent,
d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,k.sup_bamount
  • 打赏
  • 举报
回复
eracn 2003-12-12
SELECT a.id,
a.a_id,
d.pcontract_id,
a.e_style,
a.status,
a.emp_id,
c.dep_id,
c.agentemp_id,
c.agentdep_id,
b.sup_id,
f.sup_name,
b.mete_id,
mete_name = g.name,
mete_desc = g.desc_name,
e.pcontract_ratestatus,
pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.pcontract_rate end,
d.pcontract_currency,
mete_price = convert(decimal(18,6),round(b.mete_rateprice/(1.00+e.pcontract_rate),6)),
b.mete_rateprice,
payment_metequantity = b.payment_metequantity,
payment_receivedquantity = e.pitem_quantity,
payment_metetotalamount=convert(decimal(18,4),round(sum(convert(decimal(18,6),round(b.mete_rateprice/(1.00+ case when a.productclass='固定资产' then 0.0000 else e.pcontract_rate end),6))*b.payment_metequantity),2)),
e.pitem_quantitypercent,
payment_pcontract_max=convert(decimal(18,6),round(e.pitem_quantity*(1+e.pitem_quantitypercent),6)),
payment_usedquantity = round(sum(j.payment_quantity),6),
payment_usedamount_hk= round(sum(j.payment_amount),6),
payment_usedamount =round(sum(j.payment_amount+j.payment_metetax),6),
payment_totaltranscost =( select round(sum(pcontract_transcost),2) from d_pcontract where pur_id=a.id),
payment_usedtranscost=(select sum(payment_transcosttotal) from d_paymentapplypurcontent where d_paymentapplypurcontent.pur_id=d.pur_id and d_paymentapplypurcontent.payment_type=0),
payment_marginpercent = d.pcontract_marginpercent,
payment_usedmargin = round(sum(CASE WHEN i.payment_margin IS NULL THEN 0 ELSE i.payment_margin END),6),
term_id = d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,
k.sup_bamount
FROM ((dbo.vp_pur_mete_id a join dbo.vp_payment_receivelist b on a.pur_id = b.pur_id and b.o_mete_id = a.mete_id

join dbo.vp_agentemp c on a.emp_id = c.emp_id
join dbo.d_pcontract d on a.e_style = 2 and d.pur_id = a.id and d.sup_id = b.sup_id
and d.pcontract_status = 2 and d.pcontract_id = b.pcontract_id
join dbo.d_pcontractcontent e on d.pcontract_id = e.pcontract_id and b.mete_id = e.pitem_id and e.o_pitem_id = a.mete_id and
b.pcontract_id = e.pcontract_id
join dbo.c_supplier f on b.sup_id = f.sup_id and d.sup_id = f.sup_id
join dbo.vp_mete_item g on b.mete_id = g.id and e.pitem_id = g.id)
left join c_supplybalance k on a.pur_id = k.pur_id and
b.pur_id = k.pur_id and
b.sup_id = k.sup_id and
b.pcontract_id = k.pcontract_id and
d.pur_id = k.pur_id and
d.pcontract_id = k.pcontract_id and
d.sup_id = k.sup_id and
e.pcontract_id = k.pcontract_id and
f.sup_id = k.sup_id)
left join
(dbo.d_paymentapplysupplycontent h join dbo.d_paymentapplypurcontent i on
rtrim(ltrim(h.payment_id)) = rtrim(ltrim(i.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(i.payment_invoiceid))
join dbo.d_paymentapplymetecontent j on rtrim(ltrim(h.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(i.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.pur_id)) = rtrim(ltrim(j.pur_id)) and rtrim(ltrim(i.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) ) on
rtrim(ltrim(a.id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(a.id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(b.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(b.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(b.mete_id)) = rtrim(ltrim(j.mete_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(d.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(e.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pitem_id)) = rtrim(ltrim(j.mete_id))

WHERE a.status = 12 and a.e_style = 2 and
(SELECT count(pur_id) FROM dbo.d_paymentapplypurcontent u,dbo.d_paymentapply v
WHERE u.pur_id = a.id and u.pcontract_id = d.pcontract_id and u.payment_id = v.payment_id and v.payment_status >1 and u.payment_type = 0) = 0
group by a.id,d.pur_id,
a.a_id,
d.pcontract_id,
a.e_style,
a.status,
a.emp_id,
c.dep_id,
c.agentemp_id,
c.agentdep_id,
b.sup_id,
f.sup_name,
b.mete_id,
g.name,
g.desc_name,
e.pcontract_ratestatus,
--pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.pcontract_rate end,
d.pcontract_currency,
e.pcontract_rate,
b.mete_rateprice,
b.payment_metequantity,
e.pitem_quantity,
e.pitem_quantitypercent,
e.pitem_quantitypercent,
d.pcontract_transcost,
d.pcontract_marginpercent,
d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,
k.sup_bamount
  • 打赏
  • 举报
回复
zjcxc 2003-12-12
贴出来看看.
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2003-12-12 04:09
社区公告
暂无公告