2008-7-6 09:48
jvkojvko
晒晒昨天晚上通宵搞出来的任务
任务： 销售ABC分类每月更新
[b]其实任务需求就是：
1.计算上一月份的ABC分类码：根据每个月的销售，按销售额的比例来区分ABC，占销售额前80%（因为目前表里的值都是80%，28原 则嘛）为A类产品
80%-98%的为C，其余的为C类。
2.同产品的销售ABC分类码比较，更新产品ABC分类[/b]
规则：根据表F41001中设置的比率按销售额所占总比进行ABC分类，每类还有各自等级
比如 A代表畅销品，A代表一个月畅销，A2代表连续2个月畅销
若本月分类低于上月的分类，则由上一个月的分类降3级，不够降则为最新分类，比如说某产品上月畅销度为A5，本月变为B，则变为 A2，若上月为A2，本月为C，同样降3级，不够降则更新为C （对于此，我的方法是用decode(sign(),,)来实现的）
之前别人开发的用游标的，改了一下，运行时间由4.5H变为20分钟，呵呵，颇有成就感
过程：
CREATE OR REPLACE Procedure PRODDTA.pr_64_calcABC(ii_date Date)
Authid Current_User
As
vs_sql Varchar2(200);
vs_year Varchar2(2);
vs_month Varchar2(2);
Begin
-------------------会计月从5月1号开始
Select to_char(ii_date - Interval '5' Month,'YY'),to_char(ii_date - Interval '5' Month,'mm') Into vs_year,vs_month From dual;
-------------------清空临时表
Execute Immediate 'truncate table proddta.F64AbcTmp';
Execute Immediate 'truncate table proddta.F64AbcTmp_A';
vs_sql := 'insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas) select ihitm,ihmcu,ihas' || trim(vs_month) || ' from proddta.f4115 ,proddta.F64msMcu where ihas' || trim(vs_month) ||' <> 0 and ihmcu = ngmcu and ihfy = '|| to_number(vs_year);
Execute Immediate vs_sql;
commit;
Execute Immediate 'truncate table proddta.F64AbcAll';
Execute Immediate 'truncate table proddta.F64AbcAll_A';
vs_sql := 'insert into proddta.F64AbcAll_A(ngitm,ngas) select ngitm,sum(ngas) ngas from proddta.F64AbcTmp_A group by ngitm';
Execute Immediate vs_sql;
Commit;
-------------------按门市计算百分比排行
Insert Into proddta.F64AbcTmp(ngitm,ngmcu,NGAS,per_rank)
Select NGITM,NGMCU,NGAS,Sum(RT)OVER(Partition By NGMCU Order By RT Desc) From (
Select NGITM,NGMCU,NGAS,RATIO_TO_REPORT(NGAS)OVER(Partition By NGMCU) RT From proddta.F64AbcTmp_A a
) B;
Insert Into proddta.F64AbcAll(ngitm,NGAS,per_rank)
Select NGITM,NGAS,Sum(RT)OVER(Order By RT Desc) From (
Select NGITM,NGAS,RATIO_TO_REPORT(NGAS)OVER() RT From proddta.F64AbcAll_A a
) B;
Commit;
-------------------计算门市ABC分类
Update proddta.F64AbcTmp a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia1);
Update proddta.F64AbcTmp a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcTmp a Set ngflag = 'C' Where ngflag Is Null;
-------------------计算总ABC分类
Update proddta.F64AbcAll a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia1);
Update proddta.F64AbcAll a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcAll a Set ngflag = 'C' Where ngflag Is Null;
Commit;
--------------------门市ABC计算
Update LXJ.F4102_1 Set ibabcs = nvl((Select ngflag From proddta.F64AbcTmp Where ibitm = ngitm And ibmcu = ngmcu),'D')
Where Exists (Select 1 From proddta.F64msMcu Where IBMCU = NGMCU);
Update LXJ.F4102_1 Set ibsrp5 = decode(sign(ascii(substr(nvl(ibsrp5,'D'),1,1)) - ascii(ibabcs)), 1,ibabcs,0,substr(nvl(ibsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(ibsrp5,'D')),2,1),1))+1),decode(sign(to_number(nvl(substr(trim(nvl(ibsrp5,'D')),2,1),1)) - 3),1,substr(nvl(ibsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(ibsrp5,'D')),2,1),1)) - 3),ibabcs))
Where Exists (Select 1 From proddta.F64msMcu Where IBMCU = NGMCU);
Commit;
--------------------总ABC计算
Update LXJ.F4101_1 Set imabcs = nvl((Select ngflag From proddta.F64AbcAll Where imitm = ngitm),'D')
Where Exists (Select 1 From (Select ibitm From LXJ.F4102_1 Where Exists (Select 1 From proddta.F64msMcu Where ibmcu = ngmcu)) a Where a.ibitm = imitm);
Update LXJ.F4101_1 Set imsrp5 = decode(sign(ascii(substr(nvl(imsrp5,'D'),1,1)) - ascii(imabcs)), 1,imabcs,0,substr(nvl(imsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(imsrp5,'D')),2,1),1))+1),decode(sign(to_number(nvl(substr(trim(nvl(imsrp5,'D')),2,1),1)) - 3),1,substr(nvl(imsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(imsrp5,'D')),2,1),1)) - 3),imabcs))
Where Exists (Select 1 From (Select ibitm From LXJ.F4102_1 Where Exists (Select 1 From proddta.F64msMcu Where ibmcu = ngmcu)) a Where a.ibitm = imitm);
Commit;
Insert Into proddta.F640011(psfY,psmnth,psmcu,psitm,pssrp5)
Select to_number(to_char(ii_date - Interval '1' Month,'YY')), to_number(to_char(ii_date - Interval '1' Month,'MM')),IBMCU,IBITM,IBSRP5
From LXJ.f4102_1
Where IBSRP5 Like 'A%';
Commit;
Insert Into proddta.F640011(psfY,psmnth,psmcu,psitm,pssrp5)
Select to_number(to_char(ii_date - Interval '1' Month,'YY')), to_number(to_char(ii_date - Interval '1' Month,'MM')),' SC01',IMITM,IMSRP5
From LXJ.f4101_1
Where IMSRP5 Like 'A%';
Commit;
--Execute Immediate 'truncate table proddta.F64AbcTmp';
--Execute Immediate 'truncate table proddta.F64AbcTmp_A';
--Execute Immediate 'truncate table proddta.F64AbcAll';
--Execute Immediate 'truncate table proddta.F64AbcAll_A';
End;
[[i] 本帖最后由 jvkojvko 于 2008-7-7 11:31 编辑 [/i]]
任务： 销售ABC分类每月更新
[b]其实任务需求就是：
1.计算上一月份的ABC分类码：根据每个月的销售，按销售额的比例来区分ABC，占销售额前80%（因为目前表里的值都是80%，28原 则嘛）为A类产品
80%-98%的为C，其余的为C类。
2.同产品的销售ABC分类码比较，更新产品ABC分类[/b]
规则：根据表F41001中设置的比率按销售额所占总比进行ABC分类，每类还有各自等级
比如 A代表畅销品，A代表一个月畅销，A2代表连续2个月畅销
若本月分类低于上月的分类，则由上一个月的分类降3级，不够降则为最新分类，比如说某产品上月畅销度为A5，本月变为B，则变为 A2，若上月为A2，本月为C，同样降3级，不够降则更新为C （对于此，我的方法是用decode(sign(),,)来实现的）
之前别人开发的用游标的，改了一下，运行时间由4.5H变为20分钟，呵呵，颇有成就感
过程：
CREATE OR REPLACE Procedure PRODDTA.pr_64_calcABC(ii_date Date)
Authid Current_User
As
vs_sql Varchar2(200);
vs_year Varchar2(2);
vs_month Varchar2(2);
Begin
-------------------会计月从5月1号开始
Select to_char(ii_date - Interval '5' Month,'YY'),to_char(ii_date - Interval '5' Month,'mm') Into vs_year,vs_month From dual;
-------------------清空临时表
Execute Immediate 'truncate table proddta.F64AbcTmp';
Execute Immediate 'truncate table proddta.F64AbcTmp_A';
vs_sql := 'insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas) select ihitm,ihmcu,ihas' || trim(vs_month) || ' from proddta.f4115 ,proddta.F64msMcu where ihas' || trim(vs_month) ||' <> 0 and ihmcu = ngmcu and ihfy = '|| to_number(vs_year);
Execute Immediate vs_sql;
commit;
Execute Immediate 'truncate table proddta.F64AbcAll';
Execute Immediate 'truncate table proddta.F64AbcAll_A';
vs_sql := 'insert into proddta.F64AbcAll_A(ngitm,ngas) select ngitm,sum(ngas) ngas from proddta.F64AbcTmp_A group by ngitm';
Execute Immediate vs_sql;
Commit;
-------------------按门市计算百分比排行
Insert Into proddta.F64AbcTmp(ngitm,ngmcu,NGAS,per_rank)
Select NGITM,NGMCU,NGAS,Sum(RT)OVER(Partition By NGMCU Order By RT Desc) From (
Select NGITM,NGMCU,NGAS,RATIO_TO_REPORT(NGAS)OVER(Partition By NGMCU) RT From proddta.F64AbcTmp_A a
) B;
Insert Into proddta.F64AbcAll(ngitm,NGAS,per_rank)
Select NGITM,NGAS,Sum(RT)OVER(Order By RT Desc) From (
Select NGITM,NGAS,RATIO_TO_REPORT(NGAS)OVER() RT From proddta.F64AbcAll_A a
) B;
Commit;
-------------------计算门市ABC分类
Update proddta.F64AbcTmp a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia1);
Update proddta.F64AbcTmp a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcTmp a Set ngflag = 'C' Where ngflag Is Null;
-------------------计算总ABC分类
Update proddta.F64AbcAll a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia1);
Update proddta.F64AbcAll a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcAll a Set ngflag = 'C' Where ngflag Is Null;
Commit;
--------------------门市ABC计算
Update LXJ.F4102_1 Set ibabcs = nvl((Select ngflag From proddta.F64AbcTmp Where ibitm = ngitm And ibmcu = ngmcu),'D')
Where Exists (Select 1 From proddta.F64msMcu Where IBMCU = NGMCU);
Update LXJ.F4102_1 Set ibsrp5 = decode(sign(ascii(substr(nvl(ibsrp5,'D'),1,1)) - ascii(ibabcs)), 1,ibabcs,0,substr(nvl(ibsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(ibsrp5,'D')),2,1),1))+1),decode(sign(to_number(nvl(substr(trim(nvl(ibsrp5,'D')),2,1),1)) - 3),1,substr(nvl(ibsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(ibsrp5,'D')),2,1),1)) - 3),ibabcs))
Where Exists (Select 1 From proddta.F64msMcu Where IBMCU = NGMCU);
Commit;
--------------------总ABC计算
Update LXJ.F4101_1 Set imabcs = nvl((Select ngflag From proddta.F64AbcAll Where imitm = ngitm),'D')
Where Exists (Select 1 From (Select ibitm From LXJ.F4102_1 Where Exists (Select 1 From proddta.F64msMcu Where ibmcu = ngmcu)) a Where a.ibitm = imitm);
Update LXJ.F4101_1 Set imsrp5 = decode(sign(ascii(substr(nvl(imsrp5,'D'),1,1)) - ascii(imabcs)), 1,imabcs,0,substr(nvl(imsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(imsrp5,'D')),2,1),1))+1),decode(sign(to_number(nvl(substr(trim(nvl(imsrp5,'D')),2,1),1)) - 3),1,substr(nvl(imsrp5,'D'),1,1)||to_char(to_number(nvl(substr(trim(nvl(imsrp5,'D')),2,1),1)) - 3),imabcs))
Where Exists (Select 1 From (Select ibitm From LXJ.F4102_1 Where Exists (Select 1 From proddta.F64msMcu Where ibmcu = ngmcu)) a Where a.ibitm = imitm);
Commit;
Insert Into proddta.F640011(psfY,psmnth,psmcu,psitm,pssrp5)
Select to_number(to_char(ii_date - Interval '1' Month,'YY')), to_number(to_char(ii_date - Interval '1' Month,'MM')),IBMCU,IBITM,IBSRP5
From LXJ.f4102_1
Where IBSRP5 Like 'A%';
Commit;
Insert Into proddta.F640011(psfY,psmnth,psmcu,psitm,pssrp5)
Select to_number(to_char(ii_date - Interval '1' Month,'YY')), to_number(to_char(ii_date - Interval '1' Month,'MM')),' SC01',IMITM,IMSRP5
From LXJ.f4101_1
Where IMSRP5 Like 'A%';
Commit;
--Execute Immediate 'truncate table proddta.F64AbcTmp';
--Execute Immediate 'truncate table proddta.F64AbcTmp_A';
--Execute Immediate 'truncate table proddta.F64AbcAll';
--Execute Immediate 'truncate table proddta.F64AbcAll_A';
End;
[[i] 本帖最后由 jvkojvko 于 2008-7-7 11:31 编辑 [/i]]
2008-7-6 09:49
jvkojvko
另：ps to 老狐狸
通宵了，没精力写那个非固定行列转换了，呵呵
通宵了，没精力写那个非固定行列转换了，呵呵
2008-7-6 09:54
gthboy
“运行时间由4.5H变为20分钟”
提高效率的原因是什么？关键代码是哪里？呵呵，太长，看不懂
提高效率的原因是什么？关键代码是哪里？呵呵，太长，看不懂
2008-7-6 10:05
ryq0000
[quote]原帖由 [i]gthboy[/i] 于 2008-7-6 09:54 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10869453&ptid=1017422][img]http://www.itpub.net/images/common/back.gif[/img][/url]
“运行时间由4.5H变为20分钟”
提高效率的原因是什么？关键代码是哪里？呵呵，太长，看不懂 [/quote]
同问一下.
“运行时间由4.5H变为20分钟”
提高效率的原因是什么？关键代码是哪里？呵呵，太长，看不懂 [/quote]
同问一下.
2008-7-6 10:14
zhangweicai74
现在又这么早就起来了？
辛苦了:rose:
辛苦了:rose:
2008-7-6 10:21
sunfly1983
好复杂！厉害！看不明白！呵呵！
2008-7-6 10:31
jvkojvko
呵呵，主要是原先的代码用了太多游标
2008-7-6 10:46
阿日
楼主的意思就是把用到游标的地方都采用一个语句或几个来执行了。
游标的确是很费资源的，小马哥厉害
游标的确是很费资源的，小马哥厉害
2008-7-6 13:12
zhangfengh
vs_sql := 'insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas) select ihitm,ihmcu,ihas' || trim(vs_month) || ' from proddta.f4115 ,proddta.F64msMcu where ihas' || trim(vs_month) ||' <> 0 and ihmcu = ngmcu and ihfy = '|| to_number(vs_year);
这个用动态sql吗？
Execute Immediate 'truncate table proddta.F64AbcAll';
Execute Immediate 'truncate table proddta.F64AbcAll_A';
这两个表是临时表？还是临时的物理表？
ps：lz，你的奥运徽章那么多？有重的吗？能送咱几个吗？:)
[[i] 本帖最后由 zhangfengh 于 2008-7-6 13:13 编辑 [/i]]
这个用动态sql吗？
Execute Immediate 'truncate table proddta.F64AbcAll';
Execute Immediate 'truncate table proddta.F64AbcAll_A';
这两个表是临时表？还是临时的物理表？
ps：lz，你的奥运徽章那么多？有重的吗？能送咱几个吗？:)
[[i] 本帖最后由 zhangfengh 于 2008-7-6 13:13 编辑 [/i]]
2008-7-6 13:53
jvkojvko
[quote]原帖由 [i]zhangfengh[/i] 于 2008-7-6 13:12 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10870312&ptid=1017422][img]http://www.itpub.net/images/common/back.gif[/img][/url]
vs_sql := 'insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas) select ihitm,ihmcu,ihas' || trim(vs_month) || ' from proddta.f4115 ,proddta.F64msMcu where ihas' || trim(vs_month) ||' 0 and ihmcu = ngmcu and ihfy = '|| to_number(vs_year);
这个用动态sql吗？
Execute Immediate 'truncate table proddta.F64AbcAll';
Execute Immediate 'truncate table proddta.F64AbcAll_A';
这两个表是临时表？还是临时的物理表？
ps：lz，你的奥运徽章那么多？有重的吗？能送咱几个吗？:) [/quote]
是动态sql
JDE里面表的格式是：年，分布，项目号，ihas01，ihas02。。。
ihas01表示第一会计月销售总金额
那两个表示是物理表，
两个表结构一模一样，其实只要一个就够了，我刚开始的时候也是只用一个的，
可是用分析函数计算百分比的时候出现了麻烦，我想把这个百分比先记录下来，update回去的时候太慢了，因为数据量有好几十W，
计算出来直接插到另外一个表只要2分钟， 如果回写到同一个表却执行了1小时还没有结果， 后来就新增了一个表
我原来是这样写的：
update proddta.F64AbcTmp t
set per_rank = (select per_rank from (
Select NGITM,NGMCU,Sum(RT)OVER(Partition By NGMCU Order By RT Desc) per_rank From (
Select NGITM,NGMCU,NGAS,RATIO_TO_REPORT(NGAS)OVER(Partition By NGMCU) RT From proddta.F64AbcTmp a
) B) c where c.NGITM = t.NGITM and c. NGMCU = t.NGMCU)
where exists (select 1 from (Select NGITM,NGMCU,Sum(RT)OVER(Partition By NGMCU Order By RT Desc) per_rank From (
Select NGITM,NGMCU,NGAS,RATIO_TO_REPORT(NGAS)OVER(Partition By NGMCU) RT From proddta.F64AbcTmp a
) B) c where c.NGITM = t.NGITM and c. NGMCU = t.NGMCU );
可是上面的语句太慢了，因为视图C是没有索引的，回写回去很慢
vs_sql := 'insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas) select ihitm,ihmcu,ihas' || trim(vs_month) || ' from proddta.f4115 ,proddta.F64msMcu where ihas' || trim(vs_month) ||' 0 and ihmcu = ngmcu and ihfy = '|| to_number(vs_year);
这个用动态sql吗？
Execute Immediate 'truncate table proddta.F64AbcAll';
Execute Immediate 'truncate table proddta.F64AbcAll_A';
这两个表是临时表？还是临时的物理表？
ps：lz，你的奥运徽章那么多？有重的吗？能送咱几个吗？:) [/quote]
是动态sql
JDE里面表的格式是：年，分布，项目号，ihas01，ihas02。。。
ihas01表示第一会计月销售总金额
那两个表示是物理表，
两个表结构一模一样，其实只要一个就够了，我刚开始的时候也是只用一个的，
可是用分析函数计算百分比的时候出现了麻烦，我想把这个百分比先记录下来，update回去的时候太慢了，因为数据量有好几十W，
计算出来直接插到另外一个表只要2分钟， 如果回写到同一个表却执行了1小时还没有结果， 后来就新增了一个表
我原来是这样写的：
update proddta.F64AbcTmp t
set per_rank = (select per_rank from (
Select NGITM,NGMCU,Sum(RT)OVER(Partition By NGMCU Order By RT Desc) per_rank From (
Select NGITM,NGMCU,NGAS,RATIO_TO_REPORT(NGAS)OVER(Partition By NGMCU) RT From proddta.F64AbcTmp a
) B) c where c.NGITM = t.NGITM and c. NGMCU = t.NGMCU)
where exists (select 1 from (Select NGITM,NGMCU,Sum(RT)OVER(Partition By NGMCU Order By RT Desc) per_rank From (
Select NGITM,NGMCU,NGAS,RATIO_TO_REPORT(NGAS)OVER(Partition By NGMCU) RT From proddta.F64AbcTmp a
) B) c where c.NGITM = t.NGITM and c. NGMCU = t.NGMCU );
可是上面的语句太慢了，因为视图C是没有索引的，回写回去很慢
2008-7-6 13:54
jvkojvko
我一大套还没收齐，等我收齐了有多就送你，呵呵， 现在暂时先不送了
2008-7-6 13:55
jvkojvko
针对这个问题老狐狸你有什么好的解决方法吗？
请多多指教
请多多指教
2008-7-6 14:09
zhangfengh
1、没看出来有变化的表名字段名为什么要用动态sql？
2、临时表
3、最好别用那么多记录的update，想法在外边做好处理，再insert进去
2、临时表
3、最好别用那么多记录的update，想法在外边做好处理，再insert进去
2008-7-6 14:27
jvkojvko
对于第一点是这样的：
我不是说了我的表结构了吗？
第一个会计月的数据是ihas01字段
第二个会计月的数据是ihas02字段
那我计算的时候只取当前月的销售额啊
我不是说了我的表结构了吗？
第一个会计月的数据是ihas01字段
第二个会计月的数据是ihas02字段
那我计算的时候只取当前月的销售额啊
2008-7-6 14:28
jvkojvko
对于第三点，你是说这里？
-------------------计算门市ABC分类
Update proddta.F64AbcTmp a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia1);
Update proddta.F64AbcTmp a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcTmp a Set ngflag = 'C' Where ngflag Is Null;
-------------------计算总ABC分类
Update proddta.F64AbcAll a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia1);
Update proddta.F64AbcAll a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcAll a Set ngflag = 'C' Where ngflag Is Null;
-------------------计算门市ABC分类
Update proddta.F64AbcTmp a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia1);
Update proddta.F64AbcTmp a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcTmp a Set ngflag = 'C' Where ngflag Is Null;
-------------------计算总ABC分类
Update proddta.F64AbcAll a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia1);
Update proddta.F64AbcAll a Set ngflag = 'B' Where Exists (Select 1 From proddta.f41001 b Where b.cimcu = ' SC01' And per_rank <= ciia2) And ngflag Is Null;
Update proddta.F64AbcAll a Set ngflag = 'C' Where ngflag Is Null;
2008-7-6 18:30
ispu
原不是原来游标不仅多而且还是嵌套的？差距好大啊
2008-7-6 21:21
nyfor
任务没看懂 :) 顶一下.
2008-7-6 21:41
zhangfengh
vs_sql := 'insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas) select ihitm,ihmcu,ihas' || trim(vs_month) || ' from proddta.f4115 ,proddta.F64msMcu where ihas' || trim(vs_month) ||' <> 0 and ihmcu = ngmcu and ihfy = '||
to_number(vs_year);
不能直接写成：
insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas)
select ihitm,ihmcu,ihas || trim(vs_month) ||
from proddta.f4115 ,proddta.F64msMcu
where ihas || trim(vs_month) <> 0 and ihmcu = ngmcu and ihfy = to_number(vs_year);
？
to_number(vs_year);
不能直接写成：
insert into proddta.F64AbcTmp_A(Ngitm,Ngmcu,Ngas)
select ihitm,ihmcu,ihas || trim(vs_month) ||
from proddta.f4115 ,proddta.F64msMcu
where ihas || trim(vs_month) <> 0 and ihmcu = ngmcu and ihfy = to_number(vs_year);
？
2008-7-6 21:41
zhangfengh
[quote]原帖由 [i]nyfor[/i] 于 2008-7-6 21:21 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10872450&ptid=1017422][img]http://www.itpub.net/images/common/back.gif[/img][/url]
任务没看懂 :) 顶一下. [/quote]
:)
其实我也没仔细看他的任务，只是就sql看sql罢了
任务没看懂 :) 顶一下. [/quote]
:)
其实我也没仔细看他的任务，只是就sql看sql罢了
2008-7-6 21:43
zhangfengh
[quote]原帖由 [i]jvkojvko[/i] 于 2008-7-6 14:28 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10870541&ptid=1017422][img]http://www.itpub.net/images/common/back.gif[/img][/url]
对于第三点，你是说这里？
-------------------计算门市ABC分类
Update proddta.F64AbcTmp a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank [/quote]
没有具体的某个地方，只是觉得这么多的update，可能会有问题，至于行不行，你好好研究一下业务吧
对于第三点，你是说这里？
-------------------计算门市ABC分类
Update proddta.F64AbcTmp a Set ngflag = 'A' Where Exists (Select 1 From proddta.f41001 b Where a.ngmcu = b.cimcu And per_rank [/quote]
没有具体的某个地方，只是觉得这么多的update，可能会有问题，至于行不行，你好好研究一下业务吧
页: [1] 2 3
查看完整版本: 晒晒昨天晚上通宵搞出来的任务
Powered by ITPUB论坛