SQL 语句收集

最新推荐文章于 2024-10-16 10:13:45 发布
最新推荐文章于 2024-10-16 10:13:45 发布
阅读量870 收藏 23
点赞数 18
文章标签： java mybatis 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_40666130/article/details/136682336
版权

---------SQL  语句收集----------

TRUNCATE TABLE BUT00013;
-- 推导规则重新导入后，需要对物料组*进行处理，替换为%
update MARA2 set IS_FAILED = null;
update MARA2 set MESSAGE = null;
update MARA1 set UMREN = null;
update MARA1 set UMREZ = null;
update matnr_werks set matkl= replace(matkl,'*','%');
COMMIT;

TRUNCATE TABLE BUT00013;
-- 推导规则重新导入后，需要对物料组*进行处理，替换为%
update MARA6 set IS_FAILED = null;
update MARA6 set MESSAGE = null;

-----------查询字段长度------
select * from MARA1 where length(ZTXTMI)<=40;

DELETE FROM MARA7 WHERE IS_FAILED ='S';
update BUT00021 set BU_SORT1=NAME_ORG1;

update MARA1 set ZTXTLO=ZTXTSH;


SELECT COUNT(*) FROM "MARA1";   -------表总数计数；
----------DELETE FROM "MARA1" WHERE MEINS NOT IN('公斤');----------
update MARA1 set MEINS= replace(MEINS,'公斤','千克');

SELECT * FROM "MARA1_商城" WHERE MATNR_EXT IN(80370278);
update matnr_werks set matkl= replace(matkl,'*','%');
COMMIT;


-- 物料业务视图生成，包含MARA2、MARA3、MARA4、MARA5、MARA6，最后需要执行MARA1状态更改SQL
--MARA2 物料生产视图生成        
TRUNCATE TABLE MARA2;
INSERT INTO MARA2 
SELECT T.MATNR_EXT,
       T1.WERKS,
       T1.PERKZ,
       T1.STRGR,
       NULL AS VRMOD,
       NULL AS VINT1,
       NULL AS VINT2,
       NULL AS MISKZ,
       T1.MTVFP,
       NULL AS BSTRF,
       T1.BESKZ,
       NULL AS EISBE,
       T1.DISLS,
       NULL AS BSTMI,
       NULL AS BSTMA,
       T1.RGEKZ,
       T2.PLIFZ AS PLIFZ,
       NULL AS WEBAZ,
       NULL AS MKALBASIC,
       T1.DISMM AS DISMMMDMA,
       T1.DISMDMA AS DISMDMA,
       NULL AS FHOMRPPP,
       NULL AS SOBMDMA,
       NULL AS LGPMDMA,
       NULL AS AUSDT,
       NULL AS KZAUS,
       T1.FEVOR,
       T1.SFCPF,
       T1.UEETK,
       NULL AS UNETO,
       T1.DZEIT,
       NULL AS LGORT,
       NULL AS NFMAT,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
        T.PRDHA AS PRDHA,
            T1.XCHPF,
            T1.EKGRP,
            T1.KZKUP
  FROM MARA1 T 
  LEFT JOIN MATNR_PLIFZ T2 ON T.PRDHA=T2.MATKL
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL 
 WHERE T.IS_PROCESS IS NULL;
 
DELETE FROM MARA2
 WHERE PERKZ IS NULL
   AND STRGR IS NULL
   AND MTVFP IS NULL
   AND BESKZ IS NULL
   AND DISLS IS NULL
   AND RGEKZ IS NULL
   AND DISMDMA IS NULL
   AND FEVOR IS NULL
   AND SFCPF IS NULL
   AND UEETK IS NULL
   AND DZEIT IS NULL;
   --AND PRCMARCBA IS NULL;
COMMIT;
 
--MARA3 物料销售视图生成
TRUNCATE TABLE MARA3;
INSERT INTO MARA3  
SELECT T.MATNR_EXT,
       T1.VKORG,
       T1.VTWEG,
       NULL AS KONDM,
       NULL AS KTGRM,
       NULL AS VRKME,
       NULL AS DWERK,
       NULL AS AUMNG,
       '1' AS TAXKM,
       T1.WERKS AS WERKS,
       T1.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA,
       T1.MTVFP
  FROM MARA1 T 
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL 
  WHERE T.IS_PROCESS IS NULL AND T.MATNR_EXT NOT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%')
UNION ALL
SELECT T.MATNR_EXT,
       T1.VKORG,
       T1.VTWEG,
       NULL AS KONDM,
       NULL AS KTGRM,
       NULL AS VRKME,
       NULL AS DWERK,
       NULL AS AUMNG,
       NULL AS TAXKM,
       T1.WERKS AS WERKS,
       T1.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA,
       T1.MTVFP
  FROM MARA1 T 
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL 
  WHERE T.IS_PROCESS IS NULL AND T.MATNR_EXT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%') AND T1.VKORG IS NOT NULL
UNION ALL
SELECT T.MATNR_EXT,
       T2.VKORG,
       T2.VTWEG,
       NULL AS KONDM,
       NULL AS KTGRM,
       NULL AS VRKME,
       NULL AS DWERK,
       NULL AS AUMNG,
       NULL AS TAXKM,
       T1.WERKS AS WERKS,
       T2.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA,
       T1.MTVFP
  FROM MARA1 T 
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL 
  LEFT JOIN MATNR_VKORG T2 ON T1.WERKS=T2.WERKS
  WHERE T.IS_PROCESS IS NULL AND T.MATNR_EXT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%') AND T1.VKORG IS NULL;

DELETE FROM MARA3 WHERE VKORG IS NULL AND VTWEG IS NULL AND LADGR IS NULL;
COMMIT;
 
--MARA4 物料采购视图生成
TRUNCATE TABLE MARA4;
INSERT INTO MARA4  
SELECT T.MATNR_EXT,
       T1.WERKS,
       T1.EKGRP,
       NULL AS INSMK,
         T1.XCHPF AS XCHPF,
       T1.KAUTB AS KAUTB,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA,
       null AS WEBAZ
  FROM MARA1 T, MATNR_WERKS T1
 WHERE T.PRDHA LIKE T1.MATKL AND T.IS_PROCESS IS NULL;
 
 
DELETE FROM MARA4 WHERE WERKS IS NULL AND EKGRP IS NULL;
COMMIT;
 

 
--MARA5 物料财务(会记成本)视图生成
TRUNCATE TABLE MARA5;
INSERT INTO MARA5 ("MATNR_EXT","BWKEY","NCOST","AWSMARCCS","STLAL","STLAN","LOSGR","KZKUP","FXPRU","SOBMARCCS","HRKFT","EKALR","HKMAT","KOSCSTNG","BKLAS","MBEWMLAST","STPRS","PEINH","VPRSV","STPRSMLVL","PEINHMLVL","VPRSVMLVL","PRCMARCBA","PVPRS","PRCTR","BWTTY","IS_FAILED","MESSAGE","PRDHA","WERKS") 
SELECT T.MATNR_EXT,
       T1.WERKS AS BWKEY,
       NULL AS NCOST,
       T1.AWSMARCCS AS AWSMARCCS,
       NULL AS STLAL,
       NULL AS STLAN,
       T1.LOSGR,
       T1.KZKUP,
       NULL AS FXPRU,
       T1.SOBMARCCS,
       T1.HRKFT,
       T1.EKALR,
       T1.HKMAT,
       NULL AS KOSCSTNG,
       T1.BKLAS,
       T1.MBEWMLAST,
       T1.STPRS,
       T1.PEINH,
       T1.VPRSVMLVL AS VPRSV,
       T1.STPRS AS STPRSMLVL,
       T1.PEINH AS PEINHMLVL,
       T1.VPRSVMLVL,
             T1.PRCMARCBA as PRCMARCBA,
       NULL AS PVPRS,
       T1.PRCTR AS PRCTR,
       T1.BWTTY AS BWTTY,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA,
       T1.WERKS AS WERKS
  FROM MARA1 T, MATNR_WERKS T1
 WHERE T.PRDHA LIKE T1.MATKL;

    
 update MARA5 set PRCMARCBA = PRCTR where PRCMARCBA is null and PRCTR is not null;
 update MARA5 set BWTTY = 'X' where BWKEY in('160T','163T');
  update MARA5 set BWTTY = null where BWKEY not in('160T','163T');

DELETE FROM MARA5
 WHERE AWSMARCCS IS NULL
   AND LOSGR IS NULL
   AND KZKUP IS NULL
   AND SOBMARCCS IS NULL
   AND HRKFT IS NULL
   AND EKALR IS NULL
   AND HKMAT IS NULL
   AND BKLAS IS NULL
   AND MBEWMLAST IS NULL
   AND STPRS IS NULL
   AND PEINH IS NULL
   AND VPRSVMLVL IS NULL
   AND PRCMARCBA IS NULL;
COMMIT;

--MARA6 物料质量视图生成
TRUNCATE TABLE MARA6;
INSERT INTO MARA6 
SELECT T.MATNR_EXT,
       T1.WERKS AS WERKS,
       T1.ART,
       NULL AS AKTIV,
       NULL AS QAPA,
       NULL AS QMPUR,
         T1.PRFRQ AS PRFRQ,
       NULL AS SSQSS,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA       
  FROM MARA1 T, MATNR_WERKS T1
 WHERE T.PRDHA LIKE T1.MATKL AND T.IS_PROCESS IS NULL AND (T1.PRFRQ<>'需收集' OR T1.PRFRQ IS NULL)
UNION ALL
SELECT T.MATNR_EXT,
       T1.WERKS AS WERKS,
       T1.ART,
       NULL AS AKTIV,
       NULL AS QAPA,
       NULL AS QMPUR,
         CASE WHEN TO_NUMBER(T.ZDUSHU)<40 THEN '120'
       WHEN TO_NUMBER(T.ZDUSHU)>=50 THEN '300'
       WHEN TO_NUMBER(T.ZDUSHU)>=40 AND TO_NUMBER(T.ZDUSHU)<50 THEN '180'
       END AS PRFRQ,
       NULL AS SSQSS,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA
  FROM MARA1 T, MATNR_WERKS T1
 WHERE T.PRDHA LIKE T1.MATKL AND T.IS_PROCESS IS NULL AND T1.PRFRQ='需收集';
 COMMIT;
 
update MARA6 set AKTIV = 'X' where ART is not null;
DELETE FROM MARA6 WHERE ART IS NULL AND PRFRQ IS NULL;

--MARA7 物料销售视图&工厂存储视图生成
-- A1.PRDHA:  产品层次
TRUNCATE TABLE MARA7;
INSERT INTO MARA7  
select R2.MATNR_EXT, R2.WERKS,R2.LOGRT, R1.VKORG, R1.VTWEG,R1.TAXKM,R1.LADGR, R2.PRDHA,R1.MTVFP ,R2.IS_FAILED, R2.MESSAGE,
R2.XCHPF,R2.PRCMARCBA
from (
select MATNR_EXT, VKORG,WERKS, VTWEG,TAXKM,LADGR, PRDHA,MTVFP ,LOGRT, IS_FAILED,MESSAGE,XCHPF, PRCMARCBA from (SELECT T.MATNR_EXT,
       T1.VKORG,
             T1.WERKS AS WERKS,
       T1.VTWEG,
       '1' AS TAXKM,
       T1.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
         T.PRDHA AS PRDHA,
         T1.MTVFP,
           null as LOGRT ,T1.XCHPF,T1.PRCMARCBA
  FROM MARA1 T 
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL 
  WHERE  T.MATNR_EXT NOT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%')
UNION ALL
SELECT T.MATNR_EXT,
         T1.WERKS AS WERKS,
       T1.VKORG,
       T1.VTWEG,
       NULL AS TAXKM,
       T1.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA,
       T1.MTVFP,
         null as LOGRT,T1.XCHPF,T1.PRCMARCBA
  FROM MARA1 T 
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL 
  WHERE  T.MATNR_EXT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%') AND T1.VKORG IS NOT NULL
UNION ALL
SELECT T.MATNR_EXT,
         T1.WERKS AS WERKS,
       T2.VKORG,
       T2.VTWEG,
       NULL AS TAXKM,
       T2.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
         T.PRDHA AS PRDHA,
         T1.MTVFP,
           null as LOGRT,T1.XCHPF,T1.PRCMARCBA
  FROM MARA1 T 
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL 
  LEFT JOIN MATNR_VKORG T2 ON T1.WERKS=T2.WERKS
  WHERE  T.MATNR_EXT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%') AND T1.VKORG IS NULL)
    TT where (TT.VKORG IS not NULL or  tt.VTWEG IS not NULL or tt.LADGR IS not NULL)
-- and TT.WERKS = '120T'
ORDER BY TT.MATNR_EXT ASC, TT.WERKS ASC
)R1 full join (
select  MATNR_EXT, VKORG,WERKS, VTWEG,TAXKM,LADGR, PRDHA, MTVFP ,LOGRT, IS_FAILED,MESSAGE,XCHPF,PRCMARCBA from (SELECT T.MATNR_EXT,
       T1.WERKS,
       T1.LOGRT AS LOGRT,
             T.PRDHA AS PRDHA,
             null as VKORG,
             null as VTWEG,
             null as TAXKM,
             null as LADGR,
             null as MTVFP,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
             T1.XCHPF AS XCHPF,T1.PRCMARCBA AS PRCMARCBA
  FROM MARA1 T, MATNR_WERKS T1
 WHERE T.PRDHA LIKE T1.MATKL) TT where TT.LOGRT is not null ORDER BY TT.MATNR_EXT ASC, TT.WERKS ASC
 )R2 on R1.MATNR_EXT = R2.MATNR_EXT and R1.WERKS = R2.WERKS;
COMMIT;

update  MARA7 set PRCMARCBA = (select PRCTR from MARA5 a5 where a5.MATNR_EXT = MARA7.MATNR_EXT and a5.WERKS = MARA7.WERKS and a5.PRCTR is not null) where MARA7.PRCMARCBA is null;

--MARA7 物料工厂存储视图生成
-- A1.PRDHA:  产品层次
TRUNCATE TABLE MARA7;

INSERT INTO MARA7
select R2.MATNR_EXT,R2.WERKS,R2.LOGRT, R1.VKORG, R1.VTWEG,R1.TAXKM,R1.LADGR, R2.PRDHA,R1.MTVFP ,R2.IS_FAILED, R2.MESSAGE,
R2.XCHPF,R2.PRCMARCBA
from (
select MATNR_EXT,WERKS,VKORG, VTWEG,TAXKM,LADGR, PRDHA,MTVFP ,LOGRT, IS_FAILED,MESSAGE,XCHPF, PRCMARCBA from (SELECT T.MATNR_EXT,
             T1.WERKS AS WERKS,
       T1.VKORG,
       T1.VTWEG,
       '1' AS TAXKM,
       T1.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
         T.PRDHA AS PRDHA,
         T1.MTVFP,
           null as LOGRT ,T1.XCHPF,T1.PRCMARCBA
  FROM MARA1 T
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL
  WHERE  T.MATNR_EXT NOT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%')
UNION ALL
SELECT T.MATNR_EXT,
         T1.WERKS AS WERKS,
       T1.VKORG,
       T1.VTWEG,
       NULL AS TAXKM,
       T1.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
       T.PRDHA AS PRDHA,
       T1.MTVFP,
         null as LOGRT,T1.XCHPF,T1.PRCMARCBA
  FROM MARA1 T
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL
  WHERE  T.MATNR_EXT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%') AND T1.VKORG IS NOT NULL
UNION ALL
SELECT T.MATNR_EXT,
         T1.WERKS AS WERKS,
       T2.VKORG,
       T2.VTWEG,
       NULL AS TAXKM,
       T2.LADGR,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
         T.PRDHA AS PRDHA,
         T1.MTVFP,
           null as LOGRT,T1.XCHPF,T1.PRCMARCBA
  FROM MARA1 T
  LEFT JOIN MATNR_WERKS T1 ON T.PRDHA LIKE T1.MATKL
  LEFT JOIN MATNR_VKORG T2 ON T1.WERKS=T2.WERKS
  WHERE  T.MATNR_EXT IN (SELECT MATNR_EXT FROM MARA1 WHERE PRDHA LIKE '90%') AND T1.VKORG IS NULL)
    TT where (TT.VKORG IS not NULL or  tt.VTWEG IS not NULL or tt.LADGR IS not NULL)
-- and TT.WERKS = '120T'
ORDER BY TT.MATNR_EXT ASC, TT.WERKS ASC
)R1 full  join (
select  MATNR_EXT, VKORG,WERKS, VTWEG,TAXKM,LADGR, PRDHA, MTVFP ,LOGRT, IS_FAILED,MESSAGE,XCHPF,PRCMARCBA from (SELECT T.MATNR_EXT,
       T1.WERKS,
       T1.LOGRT AS LOGRT,
             T.PRDHA AS PRDHA,
             null as VKORG,
             null as VTWEG,
             null as TAXKM,
             null as LADGR,
             null as MTVFP,
       NULL AS IS_FAILED,
       NULL AS MESSAGE,
             T1.XCHPF AS XCHPF,T1.PRCMARCBA AS PRCMARCBA
  FROM MARA1 T, MATNR_WERKS T1
 WHERE T.PRDHA LIKE T1.MATKL) TT where TT.LOGRT is not null ORDER BY TT.MATNR_EXT ASC, TT.WERKS ASC
 )R2 on R1.MATNR_EXT = R2.MATNR_EXT and R1.WERKS = R2.WERKS;
 
 merge into MARA7 a7 USING MARA5 a5 ON(a5.MATNR_EXT = a7.MATNR_EXT and  a5.WERKS = a7.WERKS AND a5.PRCTR IS NOT NULL)
WHEN MATCHED THEN
  UPDATE SET a7.PRCMARCBA=a5.PRCTR where a7.PRCMARCBA IS NULL;
    
    update MARA7 set TAXKM=1;


update  MARA7 set PRCMARCBA = (select PRCTR from MARA5 a5 where a5.MATNR_EXT = MARA7.MATNR_EXT and a5.WERKS = MARA7.WERKS and a5.PRCTR is not null) where MARA7.PRCMARCBA is null;
COMMIT;


--MARA1 物料基础视图数据状态更新
UPDATE MARA1 SET IS_PROCESS='Y' WHERE IS_PROCESS IS NULL;
COMMIT;

truncate TABLE MDG_BOM_MATERIAL;

TRUNCATE TABLE mdg_bom_material;

SELECT * FROM MDG_BOM WHERE ASSORTID=2000001618; 

SELECT COUNT(*) FROM MARA6;   -------表总数计数；

UPDATE MARA1 SET IS_FAILED=NULL,MESSAGE=NULL;
TRUNCATE TABLE BUT00023;
   
-- 客商业务视图生成，包含BUT00012、BUT00013、BUT00014，最后需要执行BUT00011状态更改SQL
-- 处理子客户没有地址，采用父级地址信息
UPDATE BUT00011 T SET T.RFE_POST=(SELECT T1.RFE_POST FROM BUT00011 T1 WHERE T.ZSJDW=T1.PARTNER) WHERE T.RFE_POST IS NULL AND T.ZSJDW IS NOT NULL;
UPDATE BUT00011 T SET T.CITY1=(SELECT T1.CITY1 FROM BUT00011 T1 WHERE T.ZSJDW=T1.PARTNER) WHERE T.CITY1 IS NULL AND T.ZSJDW IS NOT NULL;
UPDATE BUT00011 T SET T.HOUSE_NUM1=(SELECT T1.HOUSE_NUM1 FROM BUT00011 T1 WHERE T.ZSJDW=T1.PARTNER) WHERE T.HOUSE_NUM1 IS NULL AND T.ZSJDW IS NOT NULL;
UPDATE BUT00011 T SET T.STREET=(SELECT T1.STREET FROM BUT00011 T1 WHERE T.ZSJDW=T1.PARTNER) WHERE T.STREET IS NULL AND T.ZSJDW IS NOT NULL;
UPDATE BUT00011 T SET T.STR_SUPPL2=(SELECT T1.STR_SUPPL2 FROM BUT00011 T1 WHERE T.ZSJDW=T1.PARTNER) WHERE T.STR_SUPPL2 IS NULL AND T.ZSJDW IS NOT NULL;
COMMIT;

--BUT00012 客商公司视图生成
TRUNCATE TABLE BUT00012;
INSERT INTO BUT00012
  SELECT T.PARTNER AS KUNNR,
         T1.BUKRS,
         T1.AKONT,
         NULL AS FDGRV,
         NULL AS ZUAWA,
         NULL AS BEGRU,
         '0001' AS ZTERM,
         T.RFE_POST AS ZSPSF,
         T.CITY1 AS ZSPCS,
         T.HOUSE_NUM1 AS ZSPQX,
         T.STREET || T.STR_SUPPL2 AS ZSPXXDZ,
         NULL AS ZSPR,
         NULL AS ZLXFS,
         NULL AS IS_FAILED,
         NULL AS MESSAGE
    FROM BUT00011 T, CUSTOMER_BUKRS T1
   WHERE T.COMPANY LIKE '%' || T1.COMPANY || '%' AND T.IS_PROCESS IS NULL;
COMMIT;
UPDATE BUT00011 SET COMPANY ='B';

--BUT00013 客商销售视图生成
TRUNCATE TABLE BUT00013;
INSERT INTO BUT00013
  SELECT T.BU_GROUP AS KTOKD,
         T.PARTNER AS KUNNR,
         T1.VKORG,
         T.PARTNER AS CPF_LIFNR,
                 T.ZLSPQ,
                 T.ZLSSQ,
         NULL AS IS_FAILED,
         NULL AS MESSAGE
    FROM BUT00011 T, CUSTOMER_VKORG T1
   WHERE T.COMPANY LIKE '%' || T1.COMPANY || '%' AND T.IS_PROCESS IS NULL;
     
COMMIT;

DELETE FROM BUT00014 WHERE IS_FAILED=NULL;

--BUT00011 客商基础视图数据状态更新
UPDATE BUT00011 SET IS_PROCESS='Y' WHERE IS_PROCESS IS NULL;
COMMIT;

-- 供应商业务视图生成，包含BUT00022、BUT00023，最后需要执行BUT00021状态更改SQL
--BUT00022 供应商公司视图生成
TRUNCATE TABLE BUT00022;
INSERT INTO BUT00022 
SELECT T.PARTNER AS LIFN,
       T1.COMPANY_CODE,
       '2202010000' AS AKONT,
       NULL AS fdgrv,
       NULL AS zuawa,
       NULL AS begru,
       '0001' AS zterm,
       NULL AS is_failed,
       NULL AS message
  FROM BUT00021 T, PROVIDER_COMPANY T1 WHERE T.IS_PROCESS IS NULL;
COMMIT;
  
--BUT00023 供应商采购视图生成
TRUNCATE TABLE BUT00023;
INSERT INTO BUT00023 
SELECT T.PARTNER AS LIFN,
       T.BU_GROUP AS KTOKK, 
       T1.EKORG_CODE AS EKORG,
       NULL AS LIFN2,
       NULL AS WERKS,
       NULL AS is_failed,
       NULL AS message       
  FROM BUT00021 T, PROVIDER_EKORG T1 WHERE T.IS_PROCESS IS NULL;
COMMIT;

UPDATE BUT00023 SET KTOKK=REPLACE(KTOKK,'Z0','ZV');
COMMIT;

UPDATE BUT00021 SET BU_GROUP=REPLACE(BU_GROUP,'ZV','Z0');
COMMIT;
--BUT00021 供应商基础视图数据状态更新
UPDATE BUT00021 SET IS_PROCESS='Y' WHERE IS_PROCESS IS NULL;
COMMIT;

-- 数据量统计
exec dbms_stats.gather_table_stats('MDG','BUT00022');
exec dbms_stats.gather_table_stats('MDG','CUS_GF_RAW');
exec dbms_stats.gather_table_stats('MDG','CUS_XL_RAW');
exec dbms_stats.gather_table_stats('MDG','PRO_RAW');
exec dbms_stats.gather_table_stats('MDG','BUT00015');
exec dbms_stats.gather_table_stats('MDG','MATERIAL_FINAL');
exec dbms_stats.gather_table_stats('MDG','BUT00011');
exec dbms_stats.gather_table_stats('MDG','MARA6');
exec dbms_stats.gather_table_stats('MDG','PROVIDER_COMPANY');
exec dbms_stats.gather_table_stats('MDG','PROVIDER_EKORG');
exec dbms_stats.gather_table_stats('MDG','CUSTOMER_VKORG');
exec dbms_stats.gather_table_stats('MDG','CUSTOMER_BUKRS');
exec dbms_stats.gather_table_stats('MDG','Sheet1');
exec dbms_stats.gather_table_stats('MDG','shengfen');
exec dbms_stats.gather_table_stats('MDG','BUT00021');
exec dbms_stats.gather_table_stats('MDG','MATNR_PLIFZ');
exec dbms_stats.gather_table_stats('MDG','MARA5');
exec dbms_stats.gather_table_stats('MDG','MARA2');
exec dbms_stats.gather_table_stats('MDG','MATNR_WERKS');
exec dbms_stats.gather_table_stats('MDG','MATNR_VKORG');
exec dbms_stats.gather_table_stats('MDG','MARA1');
exec dbms_stats.gather_table_stats('MDG','MARA4');
exec dbms_stats.gather_table_stats('MDG','MARA7');
exec dbms_stats.gather_table_stats('MDG','MARA3');
exec dbms_stats.gather_table_stats('MDG','BUT00013');
exec dbms_stats.gather_table_stats('MDG','BUT00023');
exec dbms_stats.gather_table_stats('MDG','BUT00012');
exec dbms_stats.gather_table_stats('MDG','BUT00024');
exec dbms_stats.gather_table_stats('MDG','BUT00014');

SELECT T.TABLE_NAME, T.Num_Rows FROM DBA_TABLES T WHERE OWNER='MDG' ORDER BY T.TABLE_NAME;

SELECT PARTNER, POST_CODE1 FROM BUT00011 WHERE LENGTH(POST_CODE1)=5;

UPDATE BUT00011 SET POST_CODE1='066000' WHERE PARTNER=4002003448;
UPDATE BUT00011 SET POST_CODE1='030000' WHERE PARTNER=4002003471;
UPDATE BUT00011 SET POST_CODE1='050000' WHERE PARTNER=4002003476;
UPDATE BUT00011 SET POST_CODE1='056000' WHERE PARTNER=4002003387;
UPDATE MARA1 SET MEINS='TO' WHERE MEINS='TON';

SELECT * FROM BUT00023 WHERE IS_FAILED='E';


--MARA5 物料财务(会记成本)视图生成
TRUNCATE TABLE MARA5;
INSERT INTO MARA5 
SELECT
T.MATNR_EXT,
T1.WERKS AS BWKEY,
NULL AS NCOST,
T1.AWSMARCCS AS AWSMARCCS,
NULL AS STLAL,
NULL AS STLAN,
T1.LOSGR,
T1.KZKUP,
NULL AS FXPRU,
T1.SOBMARCCS,
T1.HRKFT,
T1.EKALR,
T1.HKMAT,
NULL AS KOSCSTNG,
T1.BKLAS,
T1.MBEWMLAST,
T1.STPRS,
T1.PEINH,
T1.VPRSVMLVL AS VPRSV,
T1.STPRS AS STPRSMLVL,
T1.PEINH AS PEINHMLVL,
T1.VPRSVMLVL,
NULL AS PVPRS,
T1.PRCTR AS PRCMARCBA,
T1.BWTTY AS BWTTY,
NULL AS IS_FAILED,
NULL AS MESSAGE,
T.PRDHA AS PRDHA,
T1.WERKS AS WERKS,
T1.PRCMARCBA AS PRCTR 
FROM
    MARA1 T,
    MATNR_WERKS T1 
WHERE
    T.PRDHA LIKE T1.MATKL 
    AND T.IS_PROCESS IS NULL;
 

Melody972
关注
oracle监听sql语句,监视oracle执行的SQL语句
weixin_39661345的博客
04-15 824
查询v$sqlselect * from v$sql twhere t.LAST_ACTIVE_TIME>(sysdate - interval '1' MINUTE) --执行1分钟内的SQL语句and t.PARSING_SCHEMA_NAME = 'LSBIKE' --数据库and (t.MODULE is null or t.MODULE not like '%PL/SQL%') ...
mysql日期sql语句大全_mysql日期查找sql语句大全
weixin_30061949的博客
01-27 2097
经常会遇到这样的情况，本月阅读排行榜，本周排行榜等等。那么这样的sql怎么写呢，小班收集了一些这样的时间函数给大家参考，希望大家在自己网站项目中可以用的到在mysql中对时间日期操作的函数有很多,有时我们就希望直接通过sql查询出指定日期的数据了,下面小编来给大家总结一下.使用DATE_FORMAT方法:SELECT * FROM `ler_items` WHERE DATE_FORMAT(pos...
Oracle优化sql语句
cyhysr的博客
03-04 1105
通过使用SQL Tuning Advisor （SQL优化顾问）和SQL Access Advisor（SQL访问顾问），可以以顾问模式运行查询优化器，测试一个或者若干sql语句，然后判断如何去改善效率。SQL 优化顾问和SQL访问顾问可以做出各种推荐方案，例如创建SQL profiles，重构SQL 语句，创建索引或者物化视图，刷新优化器统计数据。顶级活动页面的Top SQL部分出现的SQL语句是被按照wait class进行分类的，A区域中的图例就是相应的wait class。
基础篇：数据库 SQL 入门教程
热门推荐
LuciferLiu_DBA
09-12 36万+
超全SQL教程，收藏就完事了！
java操作sql语句记录收集篇
weixin_44503925的博客
02-23 748
update 表名 set 列名 = 列名 + 1 where 条件 描述 列对应的类型应为数字类型 例如 <update id="updatedRenwalDetailNum"> update t_cs_100291_detail set commit_apply_num = commit_apply_num+1 where apply_id = #{applyId} </update> ...
sql语句表查询
zzsaixuexi的博客
09-29 1924
该文章收集了大部分的表查询语句 文章目录查看表中的信息查询所有行和列的信息查询对应行的信息有条件查询信息查询条件常用条件模糊查询排序查询复和查询limit 查询 前n条信息聚合查询分组查询连接查询合并查询正则表达式查询having 应用去除重复 distinct 查看表中的信息 查询所有行和列的信息 SELECT * FROM 表名; 查询对应行的信息 SELECT 字段1, 字段2,字段3 FROM 表名; 有条件查询信息 SELECT * FROM 表名 where 条件; where .
经典SQL语句大全(绝对的经典)
java_2017_csdn的博客
10-12 7901
一、基础 1、说明：创建数据库CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname3、说明：备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' -...
MySQL做数据统计SQL语句整理大全（原理+实战）
一个热爱技术的程序猿
05-13 2300
🎨领域：Java后端开发 🔥收录专栏： MySQL原理与实战 🐒个人主页：BreezAm 💖Gitee：https://gitee.com/BreezAm ✨个人标签：【后端】【大数据】【前端】【运维】 文章目录💖介绍🌳一、前置知识🌲1.1 date_sub() 函数🌲1.2 now() 函数🌲1.3 date_format() 函数🌲1.4 date()、year()、month()、day()、quarter() 函数🌲1.5 ifnull() 函数🌲1.6 curdate()🌲1.7 dat.
mysql批量执行sql语句_批量执行动态SQL语句
weixin_42120563的博客
02-02 916
下面是编程之家 jb51.cc 通过网络收集整理的代码片段。编程之家小编现在分享给大家，也给大家做个参考。-- exesql_batchdeclare-- incomming paramv_orisql VARCHAR2(1024):= 'create table TABLE_[N]_[D] as select * from TABLE where 1=2';-- original sqlv_be...
oracle 慢sql查询语句
SuperCrrazy的博客
06-08 1082
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) “平均执行时间”,round(sa.ELAPSED_TIME / 1000000, 2) “总执行时间”,sa.PARSING_USER_ID “用户ID”,sa.EXECUTIONS “执行次数”,u.username “用户名”,
精妙SQL语句收集.doc
10-03
《精妙SQL语句收集》 SQL（Structured Query Language）是一种用于管理关系数据库的标准语言，其强大之处在于能够高效地处理数据。以下是一些基础到高级的SQL语句及其应用场景，帮助你更好地理解和运用SQL。 1. **...
经典SQL语句收集
06-12
经典SQL语句收集
SQL语句收集（实用版）
10-15
"SQL语句收集（实用版）"这个压缩包文件，显然是一份包含了多种实用SQL技巧和示例的文档集合，旨在帮助用户提升SQL查询的效率和准确性。以下是对标题和描述中所提及知识点的详细说明： 1. **基本查询**：SQL的基本...
sql语句收集，常用数据库表操作语句
12-14
### SQL语句收集：常用数据库表操作语句 根据给定的信息，我们可以总结出一系列重要的SQL语句，这些语句涵盖了数据库操作中的多种常见场景，包括数据查询、数据插入、数据更新与删除等核心功能。下面将对这些SQL...
【JAVA开源】基于Vue和SpringBoot的高校学科竞赛平台
杨荧的CSDN博客
10-10 1483
教师功能有个人中心，题目类型管理，竞赛题库管理，竞赛类型管理，竞赛信息管理，报名信息管理，竞赛评分管理，参赛名单管理，晋级名单管理，获奖名单管理，竞赛总结管理，报销清单管理，成绩申诉管理，参赛信息管理，参赛信息管理，往年成绩管理，获奖情况管理。
【C++刷题】力扣-#121-买卖股票的最佳时机
最新发布
会写代码的饭桶
10-16 289
给定一个数组 prices，其中 prices[i] 表示第 i 天的股票价格。假设你可以在第 i 天买入并在第 j 天卖出股票（i ≤ j），设计一个算法来计算你所能获取的最大利润。注意你只能持有一股股票，并且你不能同时参与多笔交易（即在再次买入前必须卖出股票）。
基于SSM班级事务管理系统的设计
2401_87849773的博客
10-15 346
管理员账户功能包括：系统首页，个人中心，学生管理，班委管理，班会组织管理，健康档案管理，党员发展管理，党员培训管理，学生成绩管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。班委账号功能包括：系统首页，学生管理，学生成绩管理，活动信息管理，班费通知管理。服务器：SpringBoot自带 apache tomcat。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发系统：Windows。
Springboot api http并发测试请求
nsa65223的专栏
10-11 339
【代码】Springboot api http并发测试请求。
spring task的使用场景
qq_40603125的博客
10-13 322
spring任务调度框架约定的时间。
DBA常用SQL语句收集
* select dbms_metadata.get_ddl('INDEX','INDEX_1','DXING') FROM dual：查询索引的 DDL 语句 用户管理 在 Oracle 数据库中，用户管理是非常重要的一方面。常用的用户管理命令包括： * create user dsg ...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值