对应TPC-DS官方文档的部分如下
-- ==================view table，执行的建视图SQL如下=======================
create table if not exists t_crv
(
cr_returned_date_sk bigint,
cr_returned_time_sk bigint,
cr_item_sk bigint not null,
cr_refunded_customer_sk bigint,
cr_refunded_cdemo_sk bigint,
cr_refunded_hdemo_sk bigint,
cr_refunded_addr_sk bigint,
cr_returning_customer_sk bigint,
cr_returning_cdemo_sk bigint,
cr_returning_hdemo_sk bigint,
cr_returning_addr_sk bigint,
cr_call_center_sk bigint,
cr_catalog_page_sk bigint,
cr_ship_mode_sk bigint,
cr_warehouse_sk bigint,
cr_reason_sk bigint,
cr_order_number bigint not null,
cr_return_quantity bigint,
cr_return_amount decimal(7,2),
cr_return_tax decimal(7,2),
cr_return_amt_inc_tax decimal(7,2),
cr_fee decimal(7,2),
cr_return_ship_cost decimal(7,2),
cr_refunded_cash decimal(7,2),
cr_reversed_charge decimal(7,2),
cr_store_credit decimal(7,2),
cr_net_loss decimal(7,2)
) DISTRIBUTE BY HASH(cr_item_sk)
;
create table if not exists t_csv
(
cs_sold_date_sk bigint,
cs_sold_time_sk bigint,
cs_ship_date_sk bigint,
cs_bill_customer_sk bigint,
cs_bill_cdemo_sk bigint,
cs_bill_hdemo_sk bigint,
cs_bill_addr_sk bigint,
cs_ship_customer_sk bigint,
cs_ship_cdemo_sk bigint,
cs_ship_hdemo_sk bigint,
cs_ship_addr_sk bigint,
cs_call_center_sk bigint,
cs_catalog_page_sk bigint,
cs_ship_mode_sk bigint,
cs_warehouse_sk bigint,
cs_item_sk bigint not null,
cs_promo_sk bigint,
cs_order_number bigint not null,
cs_quantity bigint,
cs_wholesale_cost decimal(7,2),
cs_list_price decimal(7,2),
cs_sales_price decimal(7,2),
cs_ext_discount_amt decimal(7,2),
cs_ext_sales_price decimal(7,2),
cs_ext_wholesale_cost decimal(7,2),
cs_ext_list_price decimal(7,2),
cs_ext_tax decimal(7,2),
cs_coupon_amt decimal(7,2),
cs_ext_ship_cost decimal(7,2),
cs_net_paid decimal(7,2),
cs_net_paid_inc_tax decimal(7,2),
cs_net_paid_inc_ship decimal(7,2),
cs_net_paid_inc_ship_tax decimal(7,2),
cs_net_profit decimal(7,2)
) DISTRIBUTE BY HASH(cs_item_sk)
;
create table if not exists t_srv
(
sr_returned_date_sk bigint,
sr_return_time_sk bigint,
sr_item_sk bigint not null,
sr_customer_sk bigint,
sr_cdemo_sk bigint,
sr_hdemo_sk bigint,
sr_addr_sk bigint,
sr_store_sk bigint,
sr_reason_sk bigint,
sr_ticket_number bigint not null,
sr_return_quantity bigint,
sr_return_amt decimal(7,2),
sr_return_tax decimal(7,2),
sr_return_amt_inc_tax decimal(7,2),
sr_fee decimal(7,2),
sr_return_ship_cost decimal(7,2),
sr_refunded_cash decimal(7,2),
sr_reversed_charge decimal(7,2),
sr_store_credit decimal(7,2),
sr_net_loss decimal(7,2)
) DISTRIBUTE BY HASH(sr_item_sk)
;
create table if not exists t_ssv
(
ss_sold_date_sk bigint,
ss_sold_time_sk bigint,
ss_item_sk bigint not null,
ss_customer_sk bigint,
ss_cdemo_sk bigint,
ss_hdemo_sk bigint,
ss_addr_sk bigint,
ss_store_sk bigint,
ss_promo_sk bigint,
ss_ticket_number bigint not null,
ss_quantity bigint,
ss_wholesale_cost decimal(7,2),
ss_list_price decimal(7,2),
ss_sales_price decimal(7,2),
ss_ext_discount_amt decimal(7,2),
ss_ext_sales_price decimal(7,2),
ss_ext_wholesale_cost decimal(7,2),
ss_ext_list_price decimal(7,2),
ss_ext_tax decimal(7,2),
ss_coupon_amt decimal(7,2),
ss_net_paid decimal(7,2),
ss_net_paid_inc_tax decimal(7,2),
ss_net_profit decimal(7,2)
) DISTRIBUTE BY HASH(ss_item_sk)
;
create table if not exists t_wrv
(
wr_returned_date_sk bigint,
wr_returned_time_sk bigint,
wr_item_sk bigint not null,
wr_refunded_customer_sk bigint,
wr_refunded_cdemo_sk bigint,
wr_refunded_hdemo_sk bigint,
wr_refunded_addr_sk bigint,
wr_returning_customer_sk bigint,
wr_returning_cdemo_sk bigint,
wr_returning_hdemo_sk bigint,
wr_returning_addr_sk bigint,
wr_web_page_sk bigint,
wr_reason_sk bigint,
wr_order_number bigint not null,
wr_return_quantity bigint,
wr_return_amt decimal(7,2),
wr_return_tax decimal(7,2),
wr_return_amt_inc_tax decimal(7,2),
wr_fee decimal(7,2),
wr_return_ship_cost decimal(7,2),
wr_refunded_cash decimal(7,2),
wr_reversed_charge decimal(7,2),
wr_account_credit decimal(7,2),
wr_net_loss decimal(7,2)
) DISTRIBUTE BY HASH(wr_item_sk)
;
create table if not exists t_wsv
(
ws_sold_date_sk bigint,
ws_sold_time_sk bigint,
ws_ship_date_sk bigint,
ws_item_sk bigint not null,
ws_bill_customer_sk bigint,
ws_bill_cdemo_sk bigint,
ws_bill_hdemo_sk bigint,
ws_bill_addr_sk bigint,
ws_ship_customer_sk bigint,
ws_ship_cdemo_sk bigint,
ws_ship_hdemo_sk bigint,
ws_ship_addr_sk bigint,
ws_web_page_sk bigint,
ws_web_site_sk bigint,
ws_ship_mode_sk bigint,
ws_warehouse_sk bigint,
ws_promo_sk bigint,
ws_order_number bigint not null,
ws_quantity bigint,
ws_wholesale_cost decimal(7,2),
ws_list_price decimal(7,2),
ws_sales_price decimal(7,2),
ws_ext_discount_amt decimal(7,2),
ws_ext_sales_price decimal(7,2),
ws_ext_wholesale_cost decimal(7,2),
ws_ext_list_price decimal(7,2),
ws_ext_tax decimal(7,2),
ws_coupon_amt decimal(7,2),
ws_ext_ship_cost decimal(7,2),
ws_net_paid decimal(7,2),
ws_net_paid_inc_tax decimal(7,2),
ws_net_paid_inc_ship decimal(7,2),
ws_net_paid_inc_ship_tax decimal(7,2),
ws_net_profit decimal(7,2)
) DISTRIBUTE BY HASH(ws_item_sk)
;
create table if not exists t_iv
(
inv_date_sk bigint not null,
inv_item_sk bigint not null,
inv_warehouse_sk bigint not null,
inv_quantity_on_hand bigint
) DISTRIBUTE BY HASH(inv_item_sk)
;
-- ==================view table data=======================
insert into t_ssv (
SELECT
d_date_sk ss_sold_date_sk,
t_time_sk ss_sold_time_sk,
i_item_sk ss_item_sk,
c_customer_sk ss_customer_sk,
c_current_cdemo_sk ss_cdemo_sk,
c_current_hdemo_sk ss_hdemo_sk,
c_current_addr_sk ss_addr_sk,
s_store_sk ss_store_sk,
p_promo_sk ss_promo_sk,
purc_purchase_id ss_ticket_number,
plin_quantity ss_quantity,
CONVERT( i_wholesale_cost, decimal(7,2)) ss_wholesale_cost,
CONVERT(i_current_price, decimal(7,2)) ss_list_price,
CONVERT(plin_sale_price,decimal(7,2)) ss_sales_price,
CONVERT( (i_current_price-plin_sale_price)*plin_quantity, decimal(7,2)) ss_ext_discount_amt,
CONVERT( plin_sale_price * plin_quantity,decimal(7,2)) ss_ext_sales_price,
CONVERT(i_wholesale_cost * plin_quantity,decimal(7,2)) ss_ext_wholesale_cost,
CONVERT(i_current_price * plin_quantity,decimal(7,2)) ss_ext_list_price,
CONVERT(i_current_price * s_tax_percentage,decimal(7,2)) ss_ext_tax,
CONVERT(plin_coupon_amt,decimal(7,2)) ss_coupon_amt,
CONVERT((plin_sale_price * plin_quantity)-plin_coupon_amt,decimal(7,2)) ss_net_paid,
CONVERT(((plin_sale_price * plin_quantity)-plin_coupon_amt)*(1+s_tax_percentage),decimal(7,2)) ss_net_paid_inc_tax,
CONVERT(((plin_sale_price * plin_quantity)-plin_coupon_amt)-(plin_quantity*i_wholesale_cost),decimal(7,2)) ss_net_profit
FROM
s_purchase
LEFT OUTER JOIN customer ON (purc_customer_id = c_customer_id)
LEFT OUTER JOIN store ON (purc_store_id = s_store_id)
LEFT OUTER JOIN date_dim ON (cast(purc_purchase_date as date) = d_date)
LEFT OUTER JOIN time_dim ON (PURC_PURCHASE_TIME = t_time)
JOIN s_purchase_lineitem ON (purc_purchase_id = plin_purchase_id)
LEFT OUTER JOIN promotion ON plin_promotion_id = p_promo_id
LEFT OUTER JOIN item ON plin_item_id = i_item_id
WHERE
purc_purchase_id = plin_purchase_id
AND i_rec_end_date is NULL
AND s_rec_end_date is NULL
)
;
insert into t_srv (
SELECT
d_date_sk sr_returned_date_sk,
t_time_sk sr_return_time_sk,
i_item_sk sr_item_sk,
c_customer_sk sr_customer_sk,
c_current_cdemo_sk sr_cdemo_sk,
c_current_hdemo_sk sr_hdemo_sk,
c_current_addr_sk sr_addr_sk,
s_store_sk sr_store_sk,
r_reason_sk sr_reason_sk,
sret_ticket_number sr_ticket_number,
sret_return_qty sr_return_quantity,
CONVERT(sret_return_amt,decimal(7,2)) sr_return_amt,
CONVERT(sret_return_tax, decimal(7,2)) sr_return_tax,
CONVERT(sret_return_amt + sret_return_tax, decimal(7,2)) sr_return_amt_inc_tax,
CONVERT(sret_return_fee, decimal(7,2)) sr_fee,
CONVERT(sret_return_ship_cost, decimal(7,2)) sr_return_ship_cost,
CONVERT(sret_refunded_cash, decimal(7,2)) sr_refunded_cash,
CONVERT(sret_reversed_charge, decimal(7,2)) sr_reversed_charge,
CONVERT(sret_store_credit, decimal(7,2)) sr_store_credit,
CONVERT(sret_return_amt+sret_return_tax+sret_return_fee-sret_refunded_cash-sret_reversed_charge-sret_store_credit, decimal(7,2)) sr_net_loss
FROM s_store_returns
LEFT OUTER JOIN date_dim
ON (cast(sret_return_date as date) = d_date)
LEFT OUTER JOIN time_dim
ON (( cast(substr(sret_return_time,1,2) AS integer)*3600
+cast(substr(sret_return_time,4,2) AS integer)*60
+cast(substr(sret_return_time,7,2) AS integer)) = t_time)
LEFT OUTER JOIN item ON (sret_item_id = i_item_id)
LEFT OUTER JOIN customer ON (sret_customer_id = c_customer_id)
LEFT OUTER JOIN store ON (sret_store_id = s_store_id)
LEFT OUTER JOIN reason ON (sret_reason_id = r_reason_id)
WHERE i_rec_end_date IS NULL
AND s_rec_end_date IS NULL
)
;
insert into t_wsv (
SELECT
d1.d_date_sk ws_sold_date_sk,
t_time_sk ws_sold_time_sk,
d2.d_date_sk ws_ship_date_sk,
i_item_sk ws_item_sk,
c1.c_customer_sk ws_bill_customer_sk,
c1.c_current_cdemo_sk ws_bill_cdemo_sk,
c1.c_current_hdemo_sk ws_bill_hdemo_sk,
c1.c_current_addr_sk ws_bill_addr_sk,
c2.c_customer_sk ws_ship_customer_sk,
c2.c_current_cdemo_sk ws_ship_cdemo_sk,
c2.c_current_hdemo_sk ws_ship_hdemo_sk,
c2.c_current_addr_sk ws_ship_addr_sk,
wp_web_page_sk ws_web_page_sk,
web_site_sk ws_web_site_sk,
sm_ship_mode_sk ws_ship_mode_sk,
w_warehouse_sk ws_warehouse_sk,
p_promo_sk ws_promo_sk,
word_order_id ws_order_number,
wlin_quantity ws_quantity,
CONVERT(i_wholesale_cost,decimal(7,2)) ws_wholesale_cost,
CONVERT(i_current_price,decimal(7,2)) ws_list_price,
CONVERT(wlin_sales_price,decimal(7,2)) ws_sales_price,
CONVERT((i_current_price-wlin_sales_price)*wlin_quantity,decimal(7,2)) ws_ext_discount_amt,
CONVERT(wlin_sales_price * wlin_quantity,decimal(7,2)) ws_ext_sales_price,
CONVERT(i_wholesale_cost * wlin_quantity,decimal(7,2)) ws_ext_wholesale_cost,
CONVERT(i_current_price * wlin_quantity,decimal(7,2)) ws_ext_list_price,
CONVERT(i_current_price * web_tax_percentage,decimal(7,2)) ws_ext_tax,
CONVERT(wlin_coupon_amt,decimal(7,2)) ws_coupon_amt,
CONVERT(wlin_ship_cost * wlin_quantity,decimal(7,2)) ws_ext_ship_cost,
CONVERT((wlin_sales_price * wlin_quantity)-wlin_coupon_amt,decimal(7,2)) ws_net_paid,
CONVERT(((wlin_sales_price * wlin_quantity)-wlin_coupon_amt)*(1+web_tax_percentage),decimal(7,2)) ws_net_paid_inc_tax,
CONVERT(((wlin_sales_price * wlin_quantity)-wlin_coupon_amt)-(wlin_quantity*i_wholesale_cost), decimal(7,2)) ws_net_paid_inc_ship,
CONVERT((wlin_sales_price * wlin_quantity)-wlin_coupon_amt + (wlin_ship_cost * wlin_quantity) + i_current_price * web_tax_percentage, decimal(7,2)) ws_net_paid_inc_ship_tax,
CONVERT(((wlin_sales_price * wlin_quantity)-wlin_coupon_amt)-(i_wholesale_cost * wlin_quantity), decimal(7,2)) ws_net_profit
FROM
s_web_order
LEFT OUTER JOIN date_dim d1 ON (cast(word_order_date as date) = d1.d_date)
LEFT OUTER JOIN time_dim ON (word_order_time = t_time)
LEFT OUTER JOIN customer c1 ON (word_bill_customer_id = c1.c_customer_id)
LEFT OUTER JOIN customer c2 ON (word_ship_customer_id = c2.c_customer_id)
LEFT OUTER JOIN web_site ON (word_web_site_id = web_site_id AND web_rec_end_date IS NULL)
LEFT OUTER JOIN ship_mode ON (word_ship_mode_id = sm_ship_mode_id)
JOIN s_web_order_lineitem ON (word_order_id = wlin_order_id)
LEFT OUTER JOIN date_dim d2 ON (cast(wlin_ship_date as date) = d2.d_date)
LEFT OUTER JOIN item ON (wlin_item_id = i_item_id AND i_rec_end_date IS NULL)
LEFT OUTER JOIN web_page ON (wlin_web_page_id = wp_web_page_id AND wp_rec_end_date IS NULL)
LEFT OUTER JOIN warehouse ON (wlin_warehouse_id = w_warehouse_id)
LEFT OUTER JOIN promotion ON (wlin_promotion_id = p_promo_id)
)
;
insert into t_wrv (
SELECT
d_date_sk wr_return_date_sk,
t_time_sk wr_return_time_sk,
i_item_sk wr_item_sk,
c1.c_customer_sk wr_refunded_customer_sk,
c1.c_current_cdemo_sk wr_refunded_cdemo_sk,
c1.c_current_hdemo_sk wr_refunded_hdemo_sk,
c1.c_current_addr_sk wr_refunded_addr_sk,
c2.c_customer_sk wr_returning_customer_sk,
c2.c_current_cdemo_sk wr_returning_cdemo_sk,
c2.c_current_hdemo_sk wr_returning_hdemo_sk,
c2.c_current_addr_sk wr_returing_addr_sk,
wp_web_page_sk wr_web_page_sk,
r_reason_sk wr_reason_sk,
wret_order_id wr_order_number,
wret_return_qty wr_return_quantity,
CONVERT(wret_return_amt, decimal(7,2)) wr_return_amt,
CONVERT(wret_return_tax, decimal(7,2)) wr_return_tax,
CONVERT(wret_return_amt + wret_return_tax, decimal(7, 2)) wr_return_amt_inc_tax,
CONVERT(wret_return_fee, decimal(7, 2)) wr_fee,
CONVERT(wret_return_ship_cost, decimal(7, 2)) wr_return_ship_cost,
CONVERT(wret_refunded_cash, decimal(7, 2)) wr_refunded_cash,
CONVERT(wret_reversed_charge, decimal(7, 2)) wr_reversed_charge,
CONVERT(wret_account_credit, decimal(7, 2)) wr_account_credit,
CONVERT(wret_return_amt+wret_return_tax+wret_return_fee-wret_refunded_cash-wret_reversed_charge-wret_account_credit, decimal(7, 2)) wr_net_loss
FROM s_web_returns LEFT OUTER JOIN date_dim ON (cast(wret_return_date as date) = d_date)
LEFT OUTER JOIN time_dim ON ((CAST(SUBSTR(wret_return_time,1,2) AS integer)*3600
+CAST(SUBSTR(wret_return_time,4,2) AS integer)*60+CAST(SUBSTR(wret_return_time,7,2) AS integer))=t_time)
LEFT OUTER JOIN item ON (wret_item_id = i_item_id)
LEFT OUTER JOIN customer c1 ON (wret_return_customer_id = c1.c_customer_id)
LEFT OUTER JOIN customer c2 ON (wret_refund_customer_id = c2.c_customer_id)
LEFT OUTER JOIN reason ON (wret_reason_id = r_reason_id)
LEFT OUTER JOIN web_page ON (wret_web_page_id = WP_WEB_PAGE_id)
WHERE i_rec_end_date IS NULL AND wp_rec_end_date IS NULL
)
;
insert into t_csv (
SELECT
d1.d_date_sk cs_sold_date_sk,
t_time_sk cs_sold_time_sk,
d2.d_date_sk cs_ship_date_sk,
c1.c_customer_sk cs_bill_customer_sk,
c1.c_current_cdemo_sk cs_bill_cdemo_sk,
c1.c_current_hdemo_sk cs_bill_hdemo_sk,
c1.c_current_addr_sk cs_bill_addr_sk,
c2.c_customer_sk cs_ship_customer_sk,
c2.c_current_cdemo_sk cs_ship_cdemo_sk,
c2.c_current_hdemo_sk cs_ship_hdemo_sk,
c2.c_current_addr_sk cs_ship_addr_sk,
cc_call_center_sk cs_call_center_sk,
cp_catalog_page_sk cs_catalog_page_sk,
sm_ship_mode_sk cs_ship_mode_sk,
w_warehouse_sk cs_warehouse_sk,
i_item_sk cs_item_sk,
p_promo_sk cs_promo_sk,
cord_order_id cs_order_number,
clin_quantity cs_quantity,
i_wholesale_cost cs_wholesale_cost,
i_current_price cs_list_price,
clin_sales_price cs_sales_price,
CONVERT((i_current_price-clin_sales_price)*clin_quantity, decimal(7, 2)) cs_ext_discount_amt,
CONVERT(clin_sales_price * clin_quantity, decimal(7, 2)) cs_ext_sales_price,
CONVERT(i_wholesale_cost * clin_quantity, decimal(7, 2)) cs_ext_wholesale_cost,
CONVERT(i_current_price * clin_quantity, decimal(7, 2)) cs_ext_list_price,
CONVERT(i_current_price * cc_tax_percentage, decimal(7, 2)) cs_ext_tax,
clin_coupon_amt cs_coupon_amt,
CONVERT(clin_ship_cost * clin_quantity, decimal(7, 2)) cs_ext_ship_cost,
CONVERT((clin_sales_price * clin_quantity)-clin_coupon_amt, decimal(7, 2)) cs_net_paid,
CONVERT(((clin_sales_price * clin_quantity)-clin_coupon_amt)*(1+cc_tax_percentage), decimal(7, 2)) cs_net_paid_inc_tax,
CONVERT((clin_sales_price * clin_quantity)-clin_coupon_amt + (clin_ship_cost * clin_quantity), decimal(7, 2)) cs_net_paid_inc_ship,
CONVERT((clin_sales_price * clin_quantity)-clin_coupon_amt + (clin_ship_cost * clin_quantity)+ i_current_price * cc_tax_percentage, decimal(7, 2)) cs_net_paid_inc_ship_tax,
CONVERT(((clin_sales_price * clin_quantity)-clin_coupon_amt)-(clin_quantity*i_wholesale_cost), decimal(7, 2)) cs_net_profit
FROM
s_catalog_order
LEFT OUTER JOIN date_dim d1 ON
(cast(cord_order_date as date) = d1.d_date)
LEFT OUTER JOIN time_dim ON (cord_order_time = t_time)
LEFT OUTER JOIN customer c1 ON (cord_bill_customer_id = c1.c_customer_id)
LEFT OUTER JOIN customer c2 ON (cord_ship_customer_id = c2.c_customer_id)
LEFT OUTER JOIN call_center ON (cord_call_center_id = cc_call_center_id AND cc_rec_end_date IS NULL)
LEFT OUTER JOIN ship_mode ON (cord_ship_mode_id = sm_ship_mode_id)
JOIN s_catalog_order_lineitem ON (cord_order_id = clin_order_id)
LEFT OUTER JOIN date_dim d2 ON
(cast(clin_ship_date as date) = d2.d_date)
LEFT OUTER JOIN catalog_page ON
(clin_catalog_page_number = cp_catalog_page_number and clin_catalog_number = cp_catalog_number)
LEFT OUTER JOIN warehouse ON (clin_warehouse_id = w_warehouse_id)
LEFT OUTER JOIN item ON (clin_item_id = i_item_id AND i_rec_end_date IS NULL)
LEFT OUTER JOIN promotion ON (clin_promotion_id = p_promo_id)
)
;
insert into t_crv (
SELECT
d_date_sk cr_returned_date_sk
,t_time_sk cr_returned_time_sk
,i_item_sk cr_item_sk
,c1.c_customer_sk cr_refunded_customer_sk
,c1.c_current_cdemo_sk cr_refunded_cdemo_sk
,c1.c_current_hdemo_sk cr_refunded_hdemo_sk
,c1.c_current_addr_sk cr_refunded_addr_sk
,c2.c_customer_sk cr_returning_customer_sk
,c2.c_current_cdemo_sk cr_returning_cdemo_sk
,c2.c_current_hdemo_sk cr_returning_hdemo_sk
,c2.c_current_addr_sk cr_returing_addr_sk
,cc_call_center_sk cr_call_center_sk
,cp_catalog_page_sk CR_CATALOG_PAGE_SK
,sm_ship_mode_sk CR_SHIP_MODE_SK
,w_warehouse_sk CR_WAREHOUSE_SK
,r_reason_sk cr_reason_sk
,cret_order_id cr_order_number
,cret_return_qty cr_return_quantity
,cret_return_amt cr_return_amt
,cret_return_tax cr_return_tax
,CONVERT(cret_return_amt + cret_return_tax, decimal(7, 2)) cr_return_amt_inc_tax
,cret_return_fee cr_fee
,cret_return_ship_cost cr_return_ship_cost
,cret_refunded_cash cr_refunded_cash
,cret_reversed_charge cr_reversed_charge
,cret_merchant_credit cr_merchant_credit
,CONVERT(cret_return_amt+cret_return_tax+cret_return_fee-cret_refunded_cash-cret_reversed_charge-cret_merchant_credit, decimal(7, 2)) cr_net_loss
FROM s_catalog_returns
LEFT OUTER JOIN date_dim
ON (cast(cret_return_date as date) = d_date)
LEFT OUTER JOIN time_dim ON
((CAST(substr(cret_return_time,1,2) AS integer)*3600
+CAST(substr(cret_return_time,4,2) AS integer)*60
+CAST(substr(cret_return_time,7,2) AS integer)) = t_time)
LEFT OUTER JOIN item ON (cret_item_id = i_item_id)
LEFT OUTER JOIN customer c1 ON (cret_return_customer_id = c1.c_customer_id)
LEFT OUTER JOIN customer c2 ON (cret_refund_customer_id = c2.c_customer_id)
LEFT OUTER JOIN reason ON (cret_reason_id = r_reason_id)
LEFT OUTER JOIN call_center ON (cret_call_center_id = cc_call_center_id)
LEFT OUTER JOIN catalog_page ON (cret_catalog_page_id = cp_catalog_page_id)
LEFT OUTER JOIN ship_mode ON (cret_shipmode_id = sm_ship_mode_id)
LEFT OUTER JOIN warehouse ON (cret_warehouse_id = w_warehouse_id)
WHERE i_rec_end_date IS NULL AND cc_rec_end_date IS NULL
)
;
insert into t_iv (
SELECT
d_date_sk inv_date_sk,
i_item_sk inv_item_sk,
w_warehouse_sk inv_warehouse_sk,
invn_qty_on_hand inv_quantity_on_hand
FROM s_inventory
LEFT OUTER JOIN warehouse ON (invn_warehouse_id=w_warehouse_id)
LEFT OUTER JOIN item ON (invn_item_id=i_item_id AND i_rec_end_date IS NULL)
LEFT OUTER JOIN date_dim ON (d_date=invn_date)
)
;