22,209
社区成员
发帖
与我相关
我的任务
分享oracle转sql server!
这段语句在oracle中能正常运行，现在要改成能在sql server中运行，不知道需要改哪些地方，请高手多加指教~
select com_Cd,
FN_GET_ACC_CODE_NM(#argComCd#,'A902',#argAcctUn#) RT_NAME,
st_Acct_Cd,
max(acct_struc_nm) st_Acct_Nm,
max(acct_dt) acct_Dt,
max(acct_sq) acct_Sq,
case when cnt = 0 then max(ds)||' : ['||max(acct_struc_nm)||']'
else max(ds)
end as ds,
yyyymm acctYm,
case when cnt in (0,1,2) then sum(dr_am)
else sum(sum(dr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as dr_Am,
case when cnt in (0,1,2) then sum(cr_am)
else sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as cr_Am,
case when cnt=0 then max(rm_am)
when cnt=1 then
(sum(sum(dr_am)) over(partition by com_cd,slip_no order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,slip_no order by yyyymm))
when cnt=2 then
(sum(sum(dr_am)) over(partition by com_cd,yyyymm order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,yyyymm order by yyyymm))
when cnt=3 then
(
sum(sum(dr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm))
end as rm_Am,
case when cnt=0 then 'A'
when cnt=1 then
max(max('B')) over(partition by com_cd,slip_no order by yyyymm)
when cnt=2 then 'D'
when cnt=3 then 'E'
end as rm_Am_loc,
max(acct_cd) acct_Cd,
max(acct_nm) acct_Nm,
slip_No,
FN_GET_ACC_CODE_NM(#argComCd#,'W906',#argMngItemVl1#) mng_item1,
case when cnt in (0,1,2) then sum(cr_am)
else sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as cr_Am,
max(stnd_dr_cr_cl) stnd_dr_cr_cl,
cnt
from
(
select a.com_cd com_cd, substr(a.acct_cd,1,4) st_Acct_Cd, b.acct_struc_nm acct_struc_nm, null acct_dt, null acct_sq ,
'[上月结转]' as ds, substr(#argAcctBgnDt#,1,6) yyyymm, substr(#argAcctBgnDt#,1,4)||'00' yyyymm1,
0 dr_am, 0 cr_am, a.mbgn_am rm_am,
a.acct_cd acct_cd, null acct_nm, null slip_no,
null mng_item1,
b.stnd_dr_cr_cl stnd_dr_cr_cl,
0 cnt
from
(select max(com_cd) com_cd, max(acct_un) acct_un,max(acct_ym) acct_ym,sum(mbgn_am) mbgn_am,acct_cd as acct_cd from acg_acctsum_m
where acct_ym=substr(#argAcctBgnDt#,1,6)
and acct_cd in
(
select b.acct_cd acct_cd
from acg_slip_h a, v_acg_acctmngitem_d_01 b
where a.com_cd=b.com_cd
and a.slip_no=b.slip_no
and a.com_cd = #argComCd# and a.acct_dt >= #argAcctBgnDt#||'01' and a.acct_dt <= #argAcctEndDt#||'31' and a.slip_st='99'
and a.acct_un like decode(#argAcctUn#,'000','',#argAcctUn#)||'%'
and b.mng_item_cd1 = #argMngItemCd1# and nvl(b.mng_item_vl1,' ') like #argMngItemVl1#||'%'
and substr( b.acct_cd,1,4) in
(
select
COMM_CD AS COMM_CD
from ACI_COMMCODE_I
where cl_cd='WL01'
AND COMM_CD != '0000'
)
)
and com_cd=#argComCd#
and acct_un=#argAcctUn#
group by acct_ym,acct_cd
) a,
(
select a.com_cd com_cd, a.acct_struc_cd acct_struc_cd, a.acct_struc_nm, b.stnd_dr_cr_cl stnd_dr_cr_cl
from aci_acctstruc_i a, aci_acctcode_c b
where a.com_cd=b.com_cd and a.acct_cd=b.acct_cd
and a.com_cd=#argComCd# and a.app_yy= substr(#argAcctBgnDt#,1,4)) b
where a.com_cd=b.com_cd
and a.acct_cd=b.acct_struc_cd
union all
select a.com_cd com_cd, a.st_acct_cd st_acct_cd, b.acct_struc_nm acct_struc_nm,
decode(a.slip_no,null,null,a.acct_dt) acct_dt,
decode(a.slip_no,null,null,a.acct_sq) acct_sq ,
case when a.slip_no is not null then a.ds
when a.yyyymm2 is not null then '[月 计]'
end as ds,
decode(a.slip_no,null,a.yyyymm1,null) yyyymm, a.yyyymm1 yyyymm1,
decode(a.yyyymm2,null,0,a.dr_am) dr_am,
decode(a.yyyymm2,null,0,a.cr_am) cr_am,
a.rm_am rm_am,
decode(a.slip_no,null,null,a.acct_cd) acct_cd,
decode(a.slip_no,null,null,a.acct_nm) acct_nm,
a.slip_no slip_no,
decode(a.slip_no,null,null,a.mng_item1) mng_item1,
b.stnd_dr_cr_cl stnd_dr_cr_cl,
case when a.slip_no is not null then 1
when a.yyyymm2 is not null then 2
end as cnt
from
( select a.com_cd com_cd, b.st_acct_cd st_acct_cd, max(a.acct_dt) acct_dt, max(a.acct_sq) acct_sq, max(a.ds) ds,
a.yyyymm1 yyyymm1, b.st_acct_cd||a.yyyymm1 yyyymm2, sum(a.dr_am) dr_am, sum(a.cr_am) cr_am, sum(a.rm_am) rm_am,
a.slip_no_sq slip_no, max(a.acct_cd) acct_cd, max(b.acct_nm) acct_nm, max(mng_item1) mng_item1
from
( select a.com_cd com_cd, b.slip_no slip_no, b.slip_sq slip_sq, a.acct_dt acct_dt, a.acct_sq acct_sq,
b.ds ds, substr(a.acct_dt,1,6) as yyyymm1,
decode(b.dr_cr_cl,'1',b.am,0) dr_am, decode(b.dr_cr_cl,'2',b.am,0) cr_am, 0 rm_am,
(b.slip_no||lpad(b.slip_sq,5,0)) slip_no_sq, b.acct_cd acct_cd,
mng_item_nm1||decode(trim(mng_item_nm1),null,null,' : ')||decode(trim(b.mng_item_vl_nm1),null,b.mng_item_vl1,trim(b.mng_item_vl_nm1)) mng_item1
from acg_slip_h a, v_acg_acctmngitem_d_01 b
where a.com_cd=b.com_cd
and a.slip_no=b.slip_no
and a.com_cd = #argComCd# and a.acct_dt >= #argAcctBgnDt#||'01' and a.acct_dt <= #argAcctEndDt#||'31' and a.slip_st='99'
and a.acct_un like decode(#argAcctUn#,'000','',#argAcctUn#)||'%'
and b.mng_item_cd1 = #argMngItemCd1# and nvl(b.mng_item_vl1,' ') like #argMngItemVl1#||'%'
and substr( b.acct_cd,1,4) in
(
select
COMM_CD AS COMM_CD
from ACI_COMMCODE_I
where cl_cd='WL01'
AND COMM_CD != '0000'
)
) a,
( select com_cd, acct_cd, acct_struc_nm acct_nm,
decode('1','1',acct_lv4_cd,'2',acct_lv3_cd,'3',acct_lv2_cd,'4',acct_lv1_cd) st_acct_cd
from aci_acctstruc_i
where com_cd = #argComCd# and app_yy = substr(#argAcctBgnDt#,1,4) and acct_struc_cd like ''||'%') b
where a.com_cd = b.com_cd and a.acct_cd = b.acct_cd
group by ROLLUP(a.com_cd, b.st_acct_cd, a.yyyymm1, b.st_acct_cd||a.yyyymm1, a.slip_no_sq)
having a.yyyymm1 is not null ) a,
( select a.com_cd com_cd, a.acct_struc_cd acct_struc_cd, a.acct_struc_nm, b.stnd_dr_cr_cl stnd_dr_cr_cl
from aci_acctstruc_i a, aci_acctcode_c b
where a.com_cd=b.com_cd and a.acct_cd=b.acct_cd and a.com_cd = #argComCd# and a.app_yy = substr(#argAcctBgnDt#,1,4)) b
where a.com_cd=b.com_cd
and a.st_acct_cd=b.acct_struc_cd
and a.yyyymm2 is not null
)
group by com_cd, st_acct_cd, yyyymm1, cnt, slip_no, yyyymm
order by com_cd, yyyymm1,st_acct_cd, cnt, acct_dt, acct_sq, slip_no
...全文
请发表友善的回复…
发表回复
xuejie09242 2009-08-05
- 打赏
- 举报
自定义函数也要在SQL中重写。另外，decode也要改一下。如果是2000，over部分都需要改了，那么
。。。 。。。
。。。
用SQL重写吧！
。。。 。。。
。。。
用SQL重写吧！
xuejie09242 2009-08-05
- 打赏
- 举报
select com_Cd,
FN_GET_ACC_CODE_NM(#argComCd#,'A902',#argAcctUn#) RT_NAME,st_Acct_Cd,
max(acct_struc_nm) st_Acct_Nm, max(acct_dt) acct_Dt, max(acct_sq) acct_Sq,
-- case when cnt = 0 then max(ds)||' : ['||max(acct_struc_nm)||']'
-- else max(ds) end as ds,
case cnt when 0 then max(ds)+' : ['+max(acct_struc_nm)+']'
else max(ds) end as ds,
yyyymm acctYm, case when cnt in (0,1,2) then sum(dr_am)
else sum(sum(dr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as dr_Am, case when cnt in (0,1,2) then sum(cr_am)
else sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as cr_Am,
case when cnt=0 then max(rm_am)
when cnt=1 then
(sum(sum(dr_am)) over(partition by com_cd,slip_no order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,slip_no order by yyyymm))
when cnt=2 then
(sum(sum(dr_am)) over(partition by com_cd,yyyymm order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,yyyymm order by yyyymm))
when cnt=3 then
(sum(sum(dr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm))
end as rm_Am,
case when cnt=0 then 'A'
when cnt=1 then
max(max('B')) over(partition by com_cd,slip_no order by yyyymm)
when cnt=2 then 'D'
when cnt=3 then 'E'
end as rm_Am_loc,
max(acct_cd) acct_Cd,
max(acct_nm) acct_Nm,
slip_No,
FN_GET_ACC_CODE_NM(#argComCd#,'W906',#argMngItemVl1#) mng_item1,
case when cnt in (0,1,2) then sum(cr_am)
else sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as cr_Am,
max(stnd_dr_cr_cl) stnd_dr_cr_cl,
cnt
from
(
select a.com_cd com_cd, --substr(a.acct_cd,1,4) st_Acct_Cd, b.acct_struc_nm
substring(a.acct_cd,1,4) st_Acct_Cd, b.acct_struc_nm
acct_struc_nm, null acct_dt, null acct_sq ,
'[上月结转]' as ds, --substr(#argAcctBgnDt#,1,6) yyyymm,
substr(#argAcctBgnDt#,1,6) yyyymm,
substr(#argAcctBgnDt#,1,4)+'00' yyyymm1,
0 dr_am, 0 cr_am, a.mbgn_am rm_am,
a.acct_cd acct_cd, null acct_nm, null slip_no,
null mng_item1,
b.stnd_dr_cr_cl stnd_dr_cr_cl,
0 cnt
from
(select max(com_cd) com_cd, max(acct_un) acct_un,max(acct_ym) acct_ym,sum(mbgn_am) mbgn_am,acct_cd as acct_cd from acg_acctsum_m
where acct_ym=substring(#argAcctBgnDt#,1,6)
and acct_cd in
(
select b.acct_cd acct_cd
from acg_slip_h a, v_acg_acctmngitem_d_01 b
where a.com_cd=b.com_cd
and a.slip_no=b.slip_no
and a.com_cd = #argComCd# and a.acct_dt >= #argAcctBgnDt#+'01' and a.acct_dt <= #argAcctEndDt#+'31' and a.slip_st='99'
and a.acct_un like decode(#argAcctUn#,'000','',#argAcctUn#)+'%'
and b.mng_item_cd1 = #argMngItemCd1# and nvl(b.mng_item_vl1,' ') like #argMngItemVl1#+'%'
and substring( b.acct_cd,1,4) in
(
select
COMM_CD AS COMM_CD
from ACI_COMMCODE_I
where cl_cd='WL01'
AND COMM_CD <> '0000'
)
)
and com_cd=#argComCd#
and acct_un=#argAcctUn#
group by acct_ym,acct_cd
) a,
(
select a.com_cd com_cd, a.acct_struc_cd acct_struc_cd, a.acct_struc_nm, b.stnd_dr_cr_cl stnd_dr_cr_cl
from aci_acctstruc_i a, aci_acctcode_c b
where a.com_cd=b.com_cd and a.acct_cd=b.acct_cd
and a.com_cd=#argComCd# and a.app_yy= substring(#argAcctBgnDt#,1,4)) b
where a.com_cd=b.com_cd
and a.acct_cd=b.acct_struc_cd
union all
select a.com_cd com_cd, a.st_acct_cd st_acct_cd, b.acct_struc_nm acct_struc_nm,
decode(a.slip_no,null,null,a.acct_dt) acct_dt,
decode(a.slip_no,null,null,a.acct_sq) acct_sq ,
case when a.slip_no is not null then a.ds
when a.yyyymm2 is not null then '[月 计]'
end as ds,
decode(a.slip_no,null,a.yyyymm1,null) yyyymm, a.yyyymm1 yyyymm1,
decode(a.yyyymm2,null,0,a.dr_am) dr_am,
decode(a.yyyymm2,null,0,a.cr_am) cr_am,
a.rm_am rm_am,
decode(a.slip_no,null,null,a.acct_cd) acct_cd,
decode(a.slip_no,null,null,a.acct_nm) acct_nm,
a.slip_no slip_no,
decode(a.slip_no,null,null,a.mng_item1) mng_item1,
b.stnd_dr_cr_cl stnd_dr_cr_cl,
case when a.slip_no is not null then 1
when a.yyyymm2 is not null then 2
end as cnt
from
( select a.com_cd com_cd, b.st_acct_cd st_acct_cd, max(a.acct_dt) acct_dt, max(a.acct_sq) acct_sq, max(a.ds) ds,
a.yyyymm1 yyyymm1, b.st_acct_cd+a.yyyymm1 yyyymm2, sum(a.dr_am) dr_am, sum(a.cr_am) cr_am, sum(a.rm_am) rm_am,
a.slip_no_sq slip_no, max(a.acct_cd) acct_cd, max(b.acct_nm) acct_nm, max(mng_item1) mng_item1
from
( select a.com_cd com_cd, b.slip_no slip_no, b.slip_sq slip_sq, a.acct_dt acct_dt, a.acct_sq acct_sq,
b.ds ds, substr(a.acct_dt,1,6) as yyyymm1,
decode(b.dr_cr_cl,'1',b.am,0) dr_am, decode(b.dr_cr_cl,'2',b.am,0) cr_am, 0 rm_am,
(b.slip_no+lpad(b.slip_sq,5,0)) slip_no_sq, b.acct_cd acct_cd,
mng_item_nm1+decode(trim(mng_item_nm1),null,null,' : ')+decode(trim(b.mng_item_vl_nm1),null,b.mng_item_vl1,trim(b.mng_item_vl_nm1)) mng_item1
from acg_slip_h a, v_acg_acctmngitem_d_01 b
where a.com_cd=b.com_cd
and a.slip_no=b.slip_no
and a.com_cd = #argComCd# and a.acct_dt >= #argAcctBgnDt#+'01' and a.acct_dt <= #argAcctEndDt#+'31' and a.slip_st='99'
and a.acct_un like decode(#argAcctUn#,'000','',#argAcctUn#)+'%'
and b.mng_item_cd1 = #argMngItemCd1# and nvl(b.mng_item_vl1,' ') like #argMngItemVl1#+'%'
and substr( b.acct_cd,1,4) in
(
select
COMM_CD AS COMM_CD
from ACI_COMMCODE_I
where cl_cd='WL01'
AND COMM_CD <> '0000'
)
) a,
( select com_cd, acct_cd, acct_struc_nm acct_nm,
decode('1','1',acct_lv4_cd,'2',acct_lv3_cd,'3',acct_lv2_cd,'4',acct_lv1_cd) st_acct_cd
from aci_acctstruc_i
where com_cd = #argComCd# and app_yy = substr(#argAcctBgnDt#,1,4) and acct_struc_cd like ''+'%') b
where a.com_cd = b.com_cd and a.acct_cd = b.acct_cd
group by ROLLUP(a.com_cd, b.st_acct_cd, a.yyyymm1, b.st_acct_cd+a.yyyymm1, a.slip_no_sq)
having a.yyyymm1 is not null ) a,
( select a.com_cd com_cd, a.acct_struc_cd acct_struc_cd, a.acct_struc_nm, b.stnd_dr_cr_cl stnd_dr_cr_cl
from aci_acctstruc_i a, aci_acctcode_c b
where a.com_cd=b.com_cd and a.acct_cd=b.acct_cd and a.com_cd = #argComCd# and a.app_yy = substr(#argAcctBgnDt#,1,4)) b
where a.com_cd=b.com_cd
and a.st_acct_cd=b.acct_struc_cd
and a.yyyymm2 is not null
)
group by com_cd, st_acct_cd, yyyymm1, cnt, slip_no, yyyymm
order by com_cd, yyyymm1,st_acct_cd, cnt, acct_dt, acct_sq, slip_no
真是牛人，写这么长的语句！
基本没什么可改的。一是函数 substr->substring.二是||－＞＋ 三是参数名－＞@...
sdhdy 2009-08-05
- 打赏
- 举报
--类似这种都要改，#argAcctBgnDt#是变量？如果是的话，SQL是这样的：@argAcctBgnDt
select com_Cd,
--这个函数这里要改
FN_GET_ACC_CODE_NM(#argComCd#,'A902',#argAcctUn#) RT_NAME,
st_Acct_Cd,
max(acct_struc_nm) st_Acct_Nm,
max(acct_dt) acct_Dt,
max(acct_sq) acct_Sq,
--这里也要改
case when cnt = 0 then max(ds)||' : ['||max(acct_struc_nm)||']'
else max(ds)
end as ds,
yyyymm acctYm,
case when cnt in (0,1,2) then sum(dr_am)
else sum(sum(dr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as dr_Am,
case when cnt in (0,1,2) then sum(cr_am)
else sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as cr_Am,
case when cnt=0 then max(rm_am)
when cnt=1 then
(sum(sum(dr_am)) over(partition by com_cd,slip_no order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,slip_no order by yyyymm))
when cnt=2 then
(sum(sum(dr_am)) over(partition by com_cd,yyyymm order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,yyyymm order by yyyymm))
when cnt=3 then
(
sum(sum(dr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)-
sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm))
end as rm_Am,
case when cnt=0 then 'A'
when cnt=1 then
max(max('B')) over(partition by com_cd,slip_no order by yyyymm)
when cnt=2 then 'D'
when cnt=3 then 'E'
end as rm_Am_loc,
max(acct_cd) acct_Cd,
max(acct_nm) acct_Nm,
slip_No,
--这里要改。
FN_GET_ACC_CODE_NM(#argComCd#,'W906',#argMngItemVl1#) mng_item1,
case when cnt in (0,1,2) then sum(cr_am)
else sum(sum(cr_am)) over(partition by com_cd,st_acct_cd order by yyyymm)
end as cr_Am,
max(stnd_dr_cr_cl) stnd_dr_cr_cl,
cnt
............................
xiequan2 2009-08-05
- 打赏
- 举报
sql 2005很多跟oracle差不多，改起来应该不难，要是sql2000的，改动有点大
soft_wsx 2009-08-05
- 打赏
- 举报
SQL SERVER 和 ORACLE语法不一样,而且你的语句也太长了
feixianxxx 2009-08-05
- 打赏
- 举报
把何SQL不同地方的关键字 还有一些地方换了 就成了
htl258_Tony 2009-08-05
- 打赏
- 举报
不是说了吗,除了一些关键字,其它的就是自定义函数的代码了
SQL77 2009-08-05
- 打赏
- 举报
那么长呀,晕,也不懂,帮顶ORACLE
soft_wsx 2009-08-05
- 打赏
- 举报
按6楼说的做
sk4ksdksk 2009-08-05
- 打赏
- 举报
感觉over部分改起来都是一个大工程啊
