Hi,all I create 3 tables at context with color, a_110,b_110,c_110, then the script become
SELECT count(*) FROM testtmp.a_110 a LEFT OUTER JOIN testtmp.b_110 b ON a.cms_id = b.cms_id AND a.pltfm_id = b.pltfm_id LEFT OUTER JOIN testtmp.c_110 c ON b.cms_id = c.cms_id AND b.categ_lvl2_id = c.categ_lvl2_id AND b.pltfm_id = c.pltfm_id LEFT OUTER JOIN dw.dim_cms dim ON a.cms_id= dim.cms_id and GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18') AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-10-18') where GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18') AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-09-01') ; --11524 But, problems arise,two scripts result is defferent, who can tell reason, thanks SELECT count(*) FROM (SELECT nav_page_value AS cms_id, pltfm_id, COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 THEN t.sessn_id ELSE NULL END)) AS cms_vstrs, COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 AND t.nav_next_tracker_id > 0 THEN t.sessn_id ELSE NULL END)) AS cms_click_vstrs, COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 THEN t.nav_tracker_id ELSE NULL END)) AS cms_pv, COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 AND t.sessn_pv > 1 THEN t.sessn_id ELSE NULL END)) AS cms_sec_vstrs, COUNT(DISTINCT(CASE WHEN (t.detl_tracker_id > 0 AND (length(t.detl_button_position) = 0 OR t.detl_button_position IS NULL OR t.detl_button_position = 'null')) OR (t.cart_tracker_id > 0 AND length(t.detl_tracker_id) = 0) THEN t.sessn_id ELSE NULL END)) AS cms_detl_vstrs, COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 THEN t.ordr_code ELSE NULL END)) AS cms_ordr_num FROM dw.fct_traffic_cms_detl t WHERE ds= '2015-10-18' AND t.nav_page_value IS NOT NULL GROUP BY nav_page_value, pltfm_id) a LEFT OUTER JOIN (SELECT nvl(t3.mg_brand_id,-999999) AS mg_brand_id, nvl(t1.nav_page_value,-1) AS cms_id, hc.categ_lvl2_id , t1.pltfm_id, nvl(COUNT(DISTINCT(CASE WHEN t1.detl_pv > 0 OR dirct_cart_pv>0 THEN t1.sessn_id ELSE NULL END)), 0) AS detl_vstrs, COUNT(DISTINCT(CASE WHEN t1.ordr_tranx_activ_flg=1 THEN parnt_ordr_id ELSE NULL END)) AS ordr_num, COUNT(DISTINCT(CASE WHEN t1.ordr_tranx_activ_flg=1 THEN t1.end_user_id ELSE NULL END)) AS cust_num FROM dw.fct_traffic_prdt_cart_path t1 LEFT OUTER JOIN dw.dim_prod t2 ON t1.prod_id = t2.prod_id AND t2.cur_flag = 1 LEFT OUTER JOIN dw.hier_categ hc ON t2.categ_lvl_id = hc.categ_lvl_id INNER JOIN dw.dim_mrchnt b ON t1.mrchnt_id = b.mrchnt_id AND b.cur_flag = 1 LEFT OUTER JOIN dw.dim_brand t3 ON t2.brand_id = t3.brand_id AND t3.cur_flag = 1 WHERE t1.ds = '2015-10-18' AND b.biz_unit = 1 AND t1.nav_page_categ_id = 1 AND t1.nav_page_value>0 GROUP BY nvl(t3.mg_brand_id,-999999), nvl(t1.nav_page_value,-1) , hc.categ_lvl2_id , t1.pltfm_id) b ON a.cms_id = b.cms_id AND a.pltfm_id = b.pltfm_id LEFT OUTER JOIN (SELECT nvl(t1.nav_page_value,-1) AS cms_id, hc.categ_lvl2_id , t1.pltfm_id, nvl(COUNT(DISTINCT(CASE WHEN t1.detl_pv > 0 OR dirct_cart_pv>0 THEN t1.sessn_id ELSE NULL END)), 0) AS categ_lvl2_cms_detl_vstrs, COUNT(DISTINCT(CASE WHEN t1.ordr_tranx_activ_flg=1 THEN parnt_ordr_id ELSE NULL END)) AS categ_lvl2_cms_ordr_num FROM dw.fct_traffic_prdt_cart_path t1 LEFT OUTER JOIN dw.dim_prod t2 ON t1.prod_id = t2.prod_id AND t2.cur_flag = 1 LEFT OUTER JOIN dw.hier_categ hc ON t2.categ_lvl_id = hc.categ_lvl_id INNER JOIN dw.dim_mrchnt b ON t1.mrchnt_id = b.mrchnt_id AND b.cur_flag = 1 LEFT OUTER JOIN dw.dim_brand t3 ON t2.brand_id = t3.brand_id AND t3.cur_flag = 1 WHERE t1.ds = '2015-10-18' AND b.biz_unit = 1 AND t1.nav_page_categ_id = 1 AND t1.nav_page_value>0 GROUP BY nvl(t1.nav_page_value,-1) , hc.categ_lvl2_id , t1.pltfm_id) c ON b.cms_id = c.cms_id AND b.categ_lvl2_id = c.categ_lvl2_id AND b.pltfm_id = c.pltfm_id LEFT OUTER JOIN dw.dim_cms dim ON a.cms_id= dim.cms_id and GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18') AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-10-18') where GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18') AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-09-01') ; --10723