blog.Toby

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  130 随笔 :: 2 文章 :: 150 评论 :: 0 Trackbacks

insert into CTtemp.dbo.SEC_S_SCO_NEW(guid,pub_dt,creat_tm,updt_tm,rmrk,sec_cd,f0010,f0020,f0030,f0040,f0050,f0060,f0070,f0080,f0090,f0100,f0110,f0120,f0130,f0140,f0150,f0160,f0170,f0180,sum1,sum2,sum3,subsum,totalsum,lasttotal)
select NEWID() guid, a1.PUB_DT, GETDATE() creat_tm, NULL updt_tm, NULL rmrk, a1.SEC_CD,
 b1.SCORE F0010, b1.SEGMENT F0020, b2.SCORE F0030, b2.SEGMENT F0040, b3.SCORE F0050, b3.SEGMENT F0060,
 c1.SCORE F0070, c1.SEGMENT F0080, c2.SCORE F0090, c2.SEGMENT F0100, c3.SCORE F0110, c3.SEGMENT F0120,
 d1.SCORE F0130, d1.SEGMENT F0140, d2.SCORE F0150, d2.SEGMENT F0160, d3.SCORE F0170, d3.SEGMENT F0180,
 a2.SCORE Sum1, a3.SCORE Sum2, a4.SCORE Sum3, a5.SCORE SubSUM, a1.SCORE TotalSum,e.SCORE LastTotal 
FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a1
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a2 ON a1.SEC_CD=a2.SEC_CD AND a1.pub_dt=a2.pub_dt AND a2.SCO_ID='0101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a3 ON a1.SEC_CD=a3.SEC_CD AND a1.pub_dt=a3.pub_dt AND a3.SCO_ID='0102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a4 ON a1.SEC_CD=a4.SEC_CD AND a1.pub_dt=a4.pub_dt AND a4.SCO_ID='0103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a5 ON a1.SEC_CD=a5.SEC_CD AND a1.pub_dt=a5.pub_dt AND a5.SCO_ID='0104'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b1 ON a1.SEC_CD=b1.SEC_CD AND a1.pub_dt=b1.pub_dt AND b1.SCO_ID='010101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b2 ON a1.SEC_CD=b2.SEC_CD AND a1.pub_dt=b2.pub_dt AND b2.SCO_ID='010102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b3 ON a1.SEC_CD=b3.SEC_CD AND a1.pub_dt=b3.pub_dt AND b3.SCO_ID='010103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c1 ON a1.SEC_CD=c1.SEC_CD AND a1.pub_dt=c1.pub_dt AND c1.SCO_ID='010201'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c2 ON a1.SEC_CD=c2.SEC_CD AND a1.pub_dt=c2.pub_dt AND c2.SCO_ID='010202'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c3 ON a1.SEC_CD=c3.SEC_CD AND a1.pub_dt=c3.pub_dt AND c3.SCO_ID='010203'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d1 ON a1.SEC_CD=d1.SEC_CD AND a1.pub_dt=d1.pub_dt AND d1.SCO_ID='010301'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d2 ON a1.SEC_CD=d2.SEC_CD AND a1.pub_dt=d2.pub_dt AND d2.SCO_ID='010302'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d3 ON a1.SEC_CD=d3.SEC_CD AND a1.pub_dt=d3.pub_dt AND d3.SCO_ID='010303'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT e ON a1.SEC_CD=e.SEC_CD AND e.pub_dt=(SELECT min(PUB_DT) FROM (select top 2 pub_dt from CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT where sco_id='01' order by pub_dt desc) a) AND e.SCO_ID=a1.SCO_ID
WHERE a1.SCO_ID='01'
 AND a1.PUB_DT = (SELECT max(PUB_DT) FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT)


----------------
出错:
消息 8180,级别 16,状态 1,第 1 行
未能准备语句。
消息 207,级别 16,状态 3,第 1 行
列名 'Col2201' 无效。
消息 207,级别 16,状态 3,第 1 行
列名 'Col2204' 无效。

把最后left join 改为inner join 就可以了。
--------------------------------------------------------------------------------------------------------------------------

insert into CTtemp.dbo.SEC_S_SCO_NEW(guid,pub_dt,creat_tm,updt_tm,rmrk,sec_cd,f0010,f0020,f0030,f0040,f0050,f0060,f0070,f0080,f0090,f0100,f0110,f0120,f0130,f0140,f0150,f0160,f0170,f0180,sum1,sum2,sum3,subsum,totalsum,lasttotal)
select NEWID() guid, a1.PUB_DT, GETDATE() creat_tm, NULL updt_tm, NULL rmrk, a1.SEC_CD,
 b1.SCORE F0010, b1.SEGMENT F0020, b2.SCORE F0030, b2.SEGMENT F0040, b3.SCORE F0050, b3.SEGMENT F0060,
 c1.SCORE F0070, c1.SEGMENT F0080, c2.SCORE F0090, c2.SEGMENT F0100, c3.SCORE F0110, c3.SEGMENT F0120,
 d1.SCORE F0130, d1.SEGMENT F0140, d2.SCORE F0150, d2.SEGMENT F0160, d3.SCORE F0170, d3.SEGMENT F0180,
 a2.SCORE Sum1, a3.SCORE Sum2, a4.SCORE Sum3, a5.SCORE SubSUM, a1.SCORE TotalSum,e.SCORE LastTotal 
FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a1
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a2 ON a1.SEC_CD=a2.SEC_CD AND a1.pub_dt=a2.pub_dt AND a2.SCO_ID='0101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a3 ON a1.SEC_CD=a3.SEC_CD AND a1.pub_dt=a3.pub_dt AND a3.SCO_ID='0102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a4 ON a1.SEC_CD=a4.SEC_CD AND a1.pub_dt=a4.pub_dt AND a4.SCO_ID='0103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT a5 ON a1.SEC_CD=a5.SEC_CD AND a1.pub_dt=a5.pub_dt AND a5.SCO_ID='0104'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b1 ON a1.SEC_CD=b1.SEC_CD AND a1.pub_dt=b1.pub_dt AND b1.SCO_ID='010101'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b2 ON a1.SEC_CD=b2.SEC_CD AND a1.pub_dt=b2.pub_dt AND b2.SCO_ID='010102'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT b3 ON a1.SEC_CD=b3.SEC_CD AND a1.pub_dt=b3.pub_dt AND b3.SCO_ID='010103'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c1 ON a1.SEC_CD=c1.SEC_CD AND a1.pub_dt=c1.pub_dt AND c1.SCO_ID='010201'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c2 ON a1.SEC_CD=c2.SEC_CD AND a1.pub_dt=c2.pub_dt AND c2.SCO_ID='010202'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT c3 ON a1.SEC_CD=c3.SEC_CD AND a1.pub_dt=c3.pub_dt AND c3.SCO_ID='010203'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d1 ON a1.SEC_CD=d1.SEC_CD AND a1.pub_dt=d1.pub_dt AND d1.SCO_ID='010301'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d2 ON a1.SEC_CD=d2.SEC_CD AND a1.pub_dt=d2.pub_dt AND d2.SCO_ID='010302'
LEFT JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT d3 ON a1.SEC_CD=d3.SEC_CD AND a1.pub_dt=d3.pub_dt AND d3.SCO_ID='010303'
INNER JOIN CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT e ON a1.SEC_CD=e.SEC_CD AND e.pub_dt=(SELECT max(PUB_DT) FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT where sco_id=a1.SCO_ID and pub_dt<a1.pub_dt) AND e.SCO_ID=a1.SCO_ID
WHERE a1.SCO_ID='01'
 AND a1.PUB_DT = (SELECT max(PUB_DT) FROM CDW_SRV.WD_10_SEL.dbo.SCORE_D_FACT)
posted on 2007-04-07 10:23 渠上月 阅读(301) 评论(2)  编辑  收藏 所属分类: sql (sqlServer)

评论

# re: 奇怪的问题 一条 SQL 2007-08-30 20:59 天野
我也出现过一样的错误,只不过我是把INNER 改成 LEFT
这是怎么回事  回复  更多评论
  

# re: 奇怪的问题 一条 SQL 2007-08-30 20:59 天野
qutianye@sina.com
请给我写信告诉我为什么好么?  回复  更多评论
  


只有注册用户登录后才能发表评论。


网站导航: