from insert select where

from dim.dim_wms_store a insert overwrite table test_20150609 select * where store_id=2 insert overwrite table test_201506092 select * where store_id=5

 

 

--------------------------------------------------

 

FROM

(

 

FROM

(

SELECT

id fact_chuku_id,

 

IF (

export_type = '6',

concat('C', id),

id

) fact_chuku_id_1,

export_state inner_delv_ob_status,

export_type inner_delv_biz_type_cd,

yn inner_delv_cancel_status,

store_from src_store_id,

org_from src_delv_center_num,

store_to target_store_id,

org_to target_delv_center_num,

create_date create_tm,

create_by create_stf_id,

validate_date validate_tm,

validate_by validate_stf_id,

complete_date send_tm,

substring(create_date, 1, 10) chuku_dt

FROM

fdm.fdm_newdeploy_chuku_chain

WHERE

start_date <= '2015-06-08'

AND end_date > '2015-06-08'

AND substring(create_date, 1, 10) >= '2014-12-10'

) fact_chuku

JOIN (

SELECT

chuku_id chuorders_chuku_id,

art_no chuorders_art_no,

max(art_name) item_name

FROM

fdm.fdm_newdeploy_chuorders_chain

WHERE

start_date <= '2015-06-08'

AND end_date > '2015-06-08'

GROUP BY

chuku_id,

art_no

) fact_chuorders 

ON (

fact_chuku.fact_chuku_id = fact_chuorders.chuorders_chuku_id

)

LEFT OUTER JOIN (

SELECT

cast(item_sku_id AS BIGINT) sku_id,

item_first_cate_cd,

item_second_cate_cd,

item_third_cate_cd

FROM

gdm.gdm_m03_item_sku_da

WHERE

dt = '2015-06-08'

) fack_sku 

ON (

fact_chuorders.chuorders_art_no = fack_sku.sku_id

)

LEFT OUTER JOIN (

SELECT

regexp_replace (max(crtdate), '/', '-') wms_rec_date,

expno expno_1,

regexp_replace (max(down_time), '/', '-') wms_rec_tm

FROM

fdm.fdm_wms2_cals_jd_export_chain

WHERE

start_date <= '2015-06-08'

AND end_date > '2015-06-08'

AND wh_code < 500

AND (

(

EXPTYPE = 'SEX'

AND ywtype <> 'T'

)

OR substr(expno, 1, 1) = 'C'

)

GROUP BY

expno

UNION ALL

SELECT

substr(create_time, 1, 10) wms_rec_date,

outbound_no expno_1,

create_time wms_rec_tm

FROM

fdm.fdm_yayi_report2_ob_internal_order_m_chain

WHERE

dp = 'ACTIVE'

AND yn = 0 -- yayi

 

UNION ALL

SELECT

substr(create_time, 1, 10) wms_rec_date,

outbound_no expno_1,

create_time wms_rec_tm

FROM

fdm.fdm_wms5_report_ob_internal_order_m_chain

WHERE

dp = 'ACTIVE'

AND yn = 0 -- wms5. 0

) fact_export 

ON (

fact_chuku.fact_chuku_id_1 = fact_export.expno_1

)

LEFT OUTER JOIN (

          SELECT

*

FROM

(

SELECT

id inner_delv_relation_id,

box_id,

chuku_id,

art_no item_sku_id,

art_num send_qty

FROM

fdm.fdm_newdeploy_send_relation_chain

WHERE

start_date <= '2015-06-08'

AND end_date > '2015-06-08'

) fact_relation

JOIN (

SELECT

id fact_box_id,

barcode inner_delv_box_id,

sendno inner_delv_send_id,

work_statue inner_delv_ib_status,

custom_no carrier_stf_id,

custom_name carrier_name,

transfer_no shipping_bill_id,

transferType inner_delv_ship_mode_cd

FROM

fdm.fdm_newdeploy_box_chain

WHERE

start_date <= '2015-06-08'

AND end_date > '2015-06-08'

) fact_box 

ON (

fact_relation.box_id = fact_box.fact_box_id

)

LEFT OUTER JOIN (

SELECT

chukuid chukuid2,

box_no box_no2,

sku sku2,

max(operate_time) send_tm

FROM

fdm.fdm_newdeploy_status_wms_chain

WHERE

start_date <= '2015-06-08'

AND end_date > '2015-06-08'

AND status_wms = 8

GROUP BY

chukuid,

box_no,

sku

) fact_status 

ON (

fact_box.inner_delv_box_id = fact_status.box_no2

AND fact_relation.chuku_id = fact_status.chukuid2

AND fact_relation.item_sku_id = fact_status.sku2

) 

 

) fact_relation_box ON (

fact_chuorders.chuorders_chuku_id = fact_relation_box.chuku_id

AND fact_chuorders.chuorders_art_no = fact_relation_box.item_sku_id

) SELECT

inner_delv_relation_id,

fact_chuku_id inner_delv_id,

concat('P', box_id) inner_delv_into_wh_bill_id,

inner_delv_box_id,

inner_delv_send_id,

chuorders_art_no item_sku_id,

item_name,

item_first_cate_cd,

item_second_cate_cd,

item_third_cate_cd,

inner_delv_ob_status,

inner_delv_ib_status,

inner_delv_biz_type_cd,

inner_delv_cancel_status,

src_store_id,

src_delv_center_num,

target_store_id,

target_delv_center_num,

create_tm,

create_stf_id,

validate_tm,

validate_stf_id,

wms_rec_date,

wms_rec_tm,

fact_relation_box.send_tm,

send_qty,

carrier_stf_id,

carrier_name,

shipping_bill_id,

chuku_dt

) fact 

INSERT overwrite TABLE gdm_m08_ob_inner_delv_sum PARTITION 

(

dp = 'ACTIVE',

dt = '4712-12-31'

) 

SELECT

inner_delv_relation_id,

inner_delv_id,

inner_delv_into_wh_bill_id,

inner_delv_box_id,

inner_delv_send_id,

item_sku_id,

item_name,

item_first_cate_cd,

item_second_cate_cd,

item_third_cate_cd,

inner_delv_ob_status,

inner_delv_ib_status,

inner_delv_biz_type_cd,

inner_delv_cancel_status,

src_store_id,

src_delv_center_num,

target_store_id,

target_delv_center_num,

create_tm,

create_stf_id,

validate_tm,

validate_stf_id,

wms_rec_date,

wms_rec_tm,

send_tm,

send_qty,

carrier_stf_id,

carrier_name,

shipping_bill_id

WHERE

chuku_dt > '2014-12-10' INSERT overwrite TABLE gdm_m08_ob_inner_delv_sum PARTITION (

dp = 'HISTORY',

dt = '2015-06-08'

) 

SELECT

inner_delv_relation_id,

inner_delv_id,

inner_delv_into_wh_bill_id,

inner_delv_box_id,

inner_delv_send_id,

item_sku_id,

item_name,

item_first_cate_cd,

item_second_cate_cd,

item_third_cate_cd,

inner_delv_ob_status,

inner_delv_ib_status,

inner_delv_biz_type_cd,

inner_delv_cancel_status,

src_store_id,

src_delv_center_num,

target_store_id,

target_delv_center_num,

create_tm,

create_stf_id,

validate_tm,

validate_stf_id,

wms_rec_date,

wms_rec_tm,

send_tm,

send_qty,

carrier_stf_id,

carrier_name,

shipping_bill_id

WHERE

chuku_dt <= '2014-12-10'