17,377
社区成员
发帖
与我相关
我的任务
分享oracle转sql server
这段语句在oracle中能正常运行，现在要改成能在sql server中运行，不知道需要改哪些地方，请高手多加指教~
select com_Cd,
FN_GET_ACC_CODE_NM(#argComCd#,'A902',#argAcctUn#) un_nm,
FN_GET_ACC_CODE_NM(#argComCd#, 'A901', #acctCD# ) AS acct_Nm_ext,
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 and max(stnd_dr_cr_cl) ='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=1 and max(stnd_dr_cr_cl) ='2' then
(sum(sum(cr_am)) over(partition by com_cd,slip_no order by yyyymm)-
sum(sum(dr_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,
max(mng_item1) 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, a.acct_cd 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 acct_cd acct_cd
from v_acg_mngitemsum_d_01
where
com_cd = #argComCd#
and acct_un like decode(#argAcctUn#,'000','','100')||'%' and acct_ym = substr(#argAcctBgnDt#,1,6)
and mng_item_cd1 = #argMngItemCd1# and nvl(mng_item_vl1,' ') like #argMngItemVl1#||'%'
)
and com_cd=#argComCd#
and acct_un=#argAcctUn#
and acct_cd like #acctCD#||'%'
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,
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#||'%'
) 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 #acctCD#||'%') 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
union all
select '' com_cd, '' st_acct_cd, '' acct_struc_nm,
'' acct_dt,
'' acct_sq ,
'[累 计]' ds,
'' yyyymm,
'' yyyymm1,
0 dr_am,
0 cr_am,
0 rm_am,
'' acct_cd,
'' acct_nm,
'' slip_no,
'' mng_item1,
stnd_dr_cr_cl stnd_dr_cr_cl,
3 cnt
from aci_acctcode_c
where
com_cd=#argComCd#
and acct_cd= #acctCD#
)
group by com_cd, st_acct_cd, yyyymm1, cnt, slip_no, yyyymm
order by com_cd, yyyymm1,acct_cd, cnt, acct_dt, acct_sq, slip_no
...全文
请发表友善的回复…
发表回复
inthirties 2009-08-05
- 打赏
- 举报
好长的一段哟。
majy 2009-08-05
- 打赏
- 举报
这个语句基本上转化无望
一个是case when，这个是小意思，最重要的是里面用到了不少的分析函数，在sql server中根本没有，只能写程序或者存储过程来完成，努力去转化吧
一个是case when，这个是小意思，最重要的是里面用到了不少的分析函数，在sql server中根本没有，只能写程序或者存储过程来完成，努力去转化吧
suncrafted 2009-08-05
- 打赏
- 举报
支持楼上的说法
oraclelogan 2009-08-05
- 打赏
- 举报
大概看了下，需要转换的地方太多了，还有 FN_GET_ACC_CODE_NM 这个函数是自己写的oracle函数吧，也需要贴出来转换成sqlserver函数吧！
建议理清业务逻辑，重写吧！
建议理清业务逻辑，重写吧！
sk4ksdksk 2009-08-05
- 打赏
- 举报
哎，我看也是太麻烦了，不知道给高人改，需要多长时间能解决掉啊
