Regexp_replace (substr (init_delivery_time, 1, 10), '-', ') & gt;=20190101
And regexp_replace (substr (init_delivery_time, 1, 10), '-', ') & lt;=20190218
From_unixtime (unix_timestamp (pay_time, 'MM - dd yyyy - HH: MM: ss'), 'yyyyMMdd')
Select REGEXP_REPLACE (matnr '^ 0 *', ') from zt_ssa. Zg_atp_pb_val
If a case
If (t4) scp_order_no is not null, t4. Actual_qty, 0) as actual_qty,
The case when t3. Scp_qty * 0.95 & lt;=NVL (t4) actual_qty, 0) and t3. Scp_qty * 1.05 & gt;=NVL (t4) actual_qty, 0) then "Y" else null end as the flag
Get is a few
Date_format (from_unixtime (unix_timestamp (a.s tatis_date, 'yyyyMMdd'), '- dd yyyy - MM'), 'u') dayc
Gets the current time select from_unixtime (unix_timestamp (), 'MM - dd yyyy - HH: MM: ss')
According to the different period of time to collect
The select gds_cd, supplier_cd dwh_cd,
NVL (sum (case when real_complete_date=20190108 then dlvy_qty else 0 end), 0) dlvy_qty,
NVL (sum (case when real_complete_date & gt;=20190102 then dlvy_qty else 0 nivtope1 end)/(7, 0),
The sum (NVL (dlvy_qty, 0))/12 nivtope2
The from
(
The select t2 gds_cd, t2 supplier_cd, t2. Dwh_cd, t2. Real_complete_date, t2. Dlvy_qty
The from
(
The select dlvy_cmd_no dlvy_cmd_item_no, real_complete_date dwh_cd, gds_cd, supplier_cd, dlvy_qty from dm. Big__d
Where statis_date & gt;=20181228 and statis_date & lt;=20190108
And regexp_replace (substr (create_time, 1, 10), '-', ') & lt;=20190108
And regexp_replace (substr (update_time, 1, 10), '-', ') & lt;=20190108
Group by dlvy_cmd_no dlvy_cmd_item_no, real_complete_date dwh_cd, gds_cd, supplier_cd, dlvy_qty
T2)
Where t2. Real_complete_date & gt;=20181228 and t2 real_complete_date & lt;=20190108
) t1
Where a t1. Gds_cd='10002059765' and t1. Supplier_cd='10145871' and t1. Dwh_cd='DC03'
Group by gds_cd supplier_cd, dwh_cd
To get the corresponding date
Select the DATE (' 2019-02-28 ') from DUAL;
Select DATE_ADD (DATE (' 2019-03-03 '), the INTERVAL - 35 DAY) from DUAL;
Row_number () over (partition by vendor_cd order by SSS. Eff_date desc) seq by date in descending order, the supplier sorted
Select * from (
The select SSS gds_cd, SSS vendor_cd, SSS. The location, SSS. Eff_date, SSS. BoxRegulation, row_number () over (partition by vendor_cd order by SSS. Eff_date desc) seq from (
Select af gds_cd, af vendor_cd, af. The location of af. Eff_date, ab. BoxRegulation from bi_sor. TSOR_CA_WORKNG_DTL_ED af,
(select al-qeada ds_cd, a.s tore_cd, substr (b.v endor_cd, 3, 8) vendor_cd, b.b oxRegulation from dm. Tdestimate a, dm. Tdm__d b
Where a.s tatis_date=20190303 and b.s tatis_date=20190303
And al-qeada ds_cd=b.g ds_cd and a.s tore_cd=b.s torecode) ab
Where af. Gds_cd=ab. Gds_cd and af. Location=ab. Store_cd and ab. Vendor_cd=af. Vendor_cd
SSS)
) sa where sa. The seq=1
Select a. d. lvy_cmd_no, a. d. lvy_cmd_item_no, a.u uid, 'X', '30', a.a sgmt_bill_type, a.r eal_complete_date, a.a ctual_qty, b.s cp_order_no, b.i tem_no,
C.o rder_type, c.p cs_o, mount u_code, mount rand_code, mount sn_mode, Arthur c. mmdty_code, Arthur c. mmdty_name, c.d epot_code, c.d epot_name, c.s. upplier_code,
C.s. upplier_name, c.l ocation_code, c.l ocation_name, c.s. cp_qty, Arthur c. reate_time, c.P RICE_KIND, d.s tr_busi_fmt_cd
The from SCPDM. TDM_SCPRS_DLVYCMDRCPT_4LOS_D a
Left the join
(select * from dm. Tsor_er_ed where statis_date=20190314) b on a. d. lvy_cm d_no=b.d lvy_cmd_no and a. d. lvy_cmd_item_no=b.d lvy_cmd_item_no
Left the join
(select * from dm. Tsor_m_ed where statis_date=20190314) c on b.s cp_order_no=c.s. cp_order_no and c.i tem_no=c.i tem_no
Right join
(select * from im. T_lant_td where str_busi_fmt_cd=9) d on d.p lant_cd=c.d epot_code
Where a.s tatis_date=20190314 and a.a sgmt_bill_type in (' ZVIN ', 'ZVSI', 'ZVRE')
COALESCE (', 'a', ' ', null) - returns the first one is not null values, in order to find to find the first null values not stop check
Made data/* */
Delimiter//
DROP PROCEDURE IF the EXISTS mytestpro; If there is a delete *//*
CREATE PROCEDURE mytestpro ()
The BEGIN
DECLARE INT I;
DECLARE j VARCHAR (200);
The SET I=2;
WHILE I & lt; 1000 DO/* cycle has started to build data */
The SET j=CONCAT (' name ', I);
INSERT INTO CD (` Cd_name `, ` Cd_code `) VALUES (j, CONCAT (' 001 ', I));/* * build data/
The SET I=I + 1;
END
WHILE;
END//
Delimiter.
CALL mytestpro (); Call a stored procedure/* */
DROP PROCEDURE mytestpro/made finished delete * */
DELIMITER $$
CREATE PROCEDURE prcd_create_data2 (IN totalCount int)
The begin
DECLARE num int.
The set of num=10;
While num
INSERT INTO xjltest
(
Cd_name,
Cd_code
)
VALUES (' 1 ', num);
End the while;
END $$
DELIMITER.
- insert a table
Insert into dm. T_ES_D PARTITION (statis_date='20191106')
Values
Dc0163000550d0240001 (' 107423328 ', '2.0', 'A002', 'tyrt', 'S01', 'ert', '1', '1')
- insert a data table (will clear the table first, then insert)
Insert the overwrite table dm. T_DW_D PARTITION (statis_date='20191106')
Values
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull