Thiết kế website giá rẻ

Question

I’m trying to execute my script and im getting continuous error —

Error: Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubquerySemanticException: 0:0 Unsupported SubQuery Expression Currently SubQuery expressions are only allowed as Where and Having Clause predicates. Error encountered near token ‘KW_FALSE’ (state=42000,code=40000)

please help me on this..

WITH 
current_week_end_dt AS (
    SELECT cal2.fsc_wk_end_dt AS week_end_dt
    FROM np_proanalytics.I0036K_FSC_CAL_CNV cal2 
    WHERE cal2.cal_dt = CURRENT_DATE - INTERVAL '7' DAY
),
last_year_nbr AS (
    SELECT DISTINCT cal4.fsc_yr_nbr - 1 AS last_year_nbr
    FROM np_proanalytics.I0036K_FSC_CAL_CNV cal4 
    WHERE cal4.cal_dt = CURRENT_DATE
),
current_bus_dt AS (
    SELECT bus_dt FROM np_proanalytics.I0108A_BUS_DT_RFR
),
previous_week_bus_dt AS (
    SELECT bus_dt - INTERVAL '7' DAY AS bus_dt FROM np_proanalytics.I0108A_BUS_DT_RFR
),
previous_30_days_bus_dt AS (
    SELECT bus_dt - INTERVAL '30' DAY AS bus_dt FROM np_proanalytics.I0108A_BUS_DT_RFR
),
previous_year_bus_dt AS (
    SELECT bus_dt - INTERVAL '365' DAY AS bus_dt FROM np_proanalytics.I0108A_BUS_DT_RFR
),
previous_90_days_bus_dt AS (
    SELECT bus_dt - INTERVAL '90' DAY AS bus_dt FROM np_proanalytics.I0108A_BUS_DT_RFR
),
current_ly_bus_dt AS (
    SELECT DISTINCT cal12.ly_bus_dt
    FROM np_proanalytics.I0036K_FSC_CAL_CNV cal12
    WHERE cal12.cal_dt = CURRENT_DATE - INTERVAL '7' DAY
),
pa11 AS (
    SELECT lfp_org_identifier, SUM(sales) AS ytdsales, SUM(cost) AS ytd_mrg, 
           SUM(sales) - SUM(cost) AS ytd_mrg_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036V_PRR_BUS_DT prr2 ON vw1.cal_dt = prr2.prr_bus_dt 
    JOIN current_week_end_dt cwe ON vw1.cal_dt = cwe.week_end_dt
    GROUP BY lfp_org_identifier
),
pa12 AS (
    SELECT lfp_org_identifier, SUM(sales) AS lyfull, SUM(cost) AS ly_mrg, 
           SUM(sales) - SUM(cost) AS ly_mrg_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036K_FSC_CAL_CNV cal3 ON vw1.cal_dt = cal3.cal_dt 
    JOIN last_year_nbr lyn ON cal3.fsc_yr_nbr = lyn.last_year_nbr
    GROUP BY lfp_org_identifier
),
pa13 AS (
    SELECT lfp_org_identifier, SUM(sales) AS htdsales, SUM(cost) AS htd_mrg, 
           SUM(sales) / SUM(trans_count) AS htd_avg_tkt_amt, SUM(trans_count) AS htd_trans_count
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036Q_DAL_HYR_TO_DT hy1 ON vw1.cal_dt = hy1.prr_cal_dt 
    JOIN current_week_end_dt cwe ON vw1.cal_dt = cwe.week_end_dt
    GROUP BY lfp_org_identifier
),
pa14 AS (
    SELECT lfp_org_identifier, SUM(sales) AS pw
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036K_FSC_CAL_CNV cal6 ON vw1.cal_dt = cal6.cal_dt 
    JOIN current_week_end_dt cwe ON cal6.fsc_wk_end_dt = cwe.week_end_dt
    GROUP BY lfp_org_identifier
),
pa15 AS (
    SELECT lfp_org_identifier, SUM(sales) AS lypw, SUM(cost) AS ly_mrg, 
           SUM(sales) - SUM(cost) AS ly_mrg_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036K_FSC_CAL_CNV cal8 ON vw1.cal_dt = cal8.ly_bus_dt
    GROUP BY lfp_org_identifier
),
pa16 AS (
    SELECT lfp_org_identifier, SUM(sales) AS wtdsales, SUM(cost) AS wtd_mrg, 
           SUM(sales) - SUM(cost) AS wtd_mrg_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036HH_DAL_WK_TO_DT wk1 ON vw1.cal_dt = wk1.prr_cal_dt 
    JOIN np_proanalytics.I0036K_FSC_CAL_CNV cal10 ON wk1.cal_dt = cal10.cal_dt 
    WHERE cal10.wk_day_nbr <> 7
    GROUP BY lfp_org_identifier
),
pa17 AS (
    SELECT lfp_org_identifier, SUM(sales) AS lywtdsales, SUM(cost) AS ly_mrg, 
           SUM(sales) - SUM(cost) AS ly_mrg_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036HH_DAL_WK_TO_DT wk2 ON vw1.cal_dt = wk2.prr_cal_dt 
    JOIN np_proanalytics.I0036K_FSC_CAL_CNV cal11 ON wk2.cal_dt = cal11.cal_dt 
    WHERE cal11.wk_day_nbr <> 7
    GROUP BY lfp_org_identifier
),
pa18 AS (
    SELECT lfp_org_identifier, SUM(sales) AS lyytdsales, SUM(cost) AS ly_mrg, 
           SUM(sales) - SUM(cost) AS ly_mrg_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036V_PRR_BUS_DT prr2 ON vw1.cal_dt = prr2.prr_bus_dt 
    JOIN np_proanalytics.I0036K_FSC_CAL_CNV cal12 ON prr2.cal_dt = cal12.cal_dt  
    GROUP BY lfp_org_identifier
),
pa19 AS (
    SELECT lfp_org_identifier, SUM(sales) AS lyhtdsales, SUM(cost) AS ly_mrg, 
           SUM(sales) - SUM(cost) AS ly_mrg_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN np_proanalytics.I0036Q_DAL_HYR_TO_DT hy2 ON vw1.cal_dt = hy2.prr_cal_dt 
    JOIN np_proanalytics.I0036K_FSC_CAL_CNV cal14 ON hy2.cal_dt = cal14.cal_dt 
    GROUP BY lfp_org_identifier
),
pa110 AS (
    SELECT lfp_org_identifier, MAX(frq_lct_nbr) AS primary_store_shopped, 
           MAX(dis_lct_nbr) AS noof_distinct_stores_shopped
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    WHERE vw1.cal_dt IS NOT NULL
    GROUP BY lfp_org_identifier
),
pa111 AS (
    SELECT lfp_org_identifier, SUM(sales) AS wtd_ly_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN previous_week_bus_dt pwbd ON vw1.cal_dt >= pwbd.bus_dt
    GROUP BY lfp_org_identifier
),
pa112 AS (
    SELECT lfp_org_identifier, SUM(sales) AS pw_ly_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    LEFT JOIN previous_week_bus_dt pwbd ON vw1.cal_dt BETWEEN pwbd.bus_dt AND (SELECT bus_dt FROM current_bus_dt)
    GROUP BY lfp_org_identifier
),
pa113 AS (
    SELECT lfp_org_identifier, SUM(sales) AS htd_ly_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN previous_30_days_bus_dt p30bd ON vw1.cal_dt >= p30bd.bus_dt
    GROUP BY lfp_org_identifier
),
pa114 AS (
    SELECT lfp_org_identifier, SUM(sales) AS ytd_ly_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    LEFT JOIN previous_year_bus_dt pybd ON vw1.cal_dt BETWEEN pybd.bus_dt AND (SELECT bus_dt FROM current_bus_dt)
    GROUP BY lfp_org_identifier
),
pa115 AS (
    SELECT lfp_org_identifier, SUM(sales) AS rol3mth_sales_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN previous_90_days_bus_dt p90bd ON vw1.cal_dt >= p90bd.bus_dt
    GROUP BY lfp_org_identifier
),
pa116 AS (
    SELECT lfp_org_identifier, SUM(sales) AS rol3mth_ly_sales_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1 
    LEFT JOIN previous_90_days_bus_dt p90bd ON vw1.cal_dt BETWEEN p90bd.bus_dt AND (SELECT bus_dt FROM current_bus_dt)
    GROUP BY lfp_org_identifier
),
pa117 AS (
    SELECT lfp_org_identifier, SUM(sales) AS rol6mth_sales_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN previous_year_bus_dt pybd ON vw1.cal_dt >= pybd.bus_dt - INTERVAL '184' DAY
    GROUP BY lfp_org_identifier
),
pa118 AS (
    SELECT lfp_org_identifier, SUM(sales) AS rol6mth_ly_sales_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    LEFT JOIN previous_year_bus_dt pybd ON vw1.cal_dt BETWEEN pybd.bus_dt - INTERVAL '184' DAY AND pybd.bus_dt
    GROUP BY lfp_org_identifier
),
pa119 AS (
    SELECT lfp_org_identifier, SUM(sales) AS rol12mth_sales_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    JOIN previous_year_bus_dt pybd ON vw1.cal_dt >= pybd.bus_dt
    GROUP BY lfp_org_identifier
),
pa120 AS (
    SELECT lfp_org_identifier, SUM(sales) AS rol12mth_ly_sales_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    LEFT JOIN previous_year_bus_dt pybd ON vw1.cal_dt BETWEEN pybd.bus_dt - INTERVAL '365' DAY AND pybd.bus_dt
    GROUP BY lfp_org_identifier
),
pa121 AS (
    SELECT lfp_org_identifier, MAX(frq_lct_nbr) AS noof_distinct_stores_shopped
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1 
    WHERE vw1.cal_dt IS NOT NULL
    GROUP BY lfp_org_identifier
),
pa122 AS (
    SELECT lfp_org_identifier, SUM(sales) AS htd_avg_tkt_amt, SUM(cost) AS lyhtd_avg_tkt_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    WHERE vw1.cal_dt IS NOT NULL
    GROUP BY lfp_org_identifier
),
pa123 AS (
    SELECT lfp_org_identifier, SUM(sales) AS htd_trans_count, SUM(cost) AS lyhtd_trans_count
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    WHERE vw1.cal_dt IS NOT NULL
    GROUP BY lfp_org_identifier
),
pa124 AS (
    SELECT lfp_org_identifier, SUM(sales) AS wtd_mrg_amt, SUM(cost) AS wtd_mrg_ly_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    WHERE vw1.cal_dt IS NOT NULL
    GROUP BY lfp_org_identifier
),
pa125 AS (
    SELECT lfp_org_identifier, SUM(sales) AS htd_mrg_amt, SUM(cost) AS htd_mrg_ly_amt
    FROM np_proanalytics.VW_LAM_SALES_ALL_ACCOUNTS vw1
    WHERE vw1.cal_dt IS NOT NULL
    GROUP BY lfp_org_identifier
)

-- Main query using pre-calculated values from CTEs
INSERT INTO np_proanalytics.test_1
SELECT 
    pa11.lfp_org_identifier,
    pa11.ytdsales AS ytd_amt,
    pa12.lyfull AS py_amt,
    pa13.htdsales AS sal_cy_htd_amt,
    pa14.pw AS sal_cy_pw_amt,
    CASE
        WHEN ((pa14.pw = 0.0 AND pa15.lypw > 0.0) OR (pa14.pw < 0.0 AND pa15.lypw = 0.0)) THEN -100.0
        WHEN (pa14.pw = 0.0 AND pa15.lypw = 0.0) THEN 0.0
        WHEN ((pa14.pw > 0.0 AND pa15.lypw = 0.0) OR (pa14.pw = 0.0 AND pa15.lypw < 0.0)) THEN 100.0
        WHEN ((pa14.pw < 0.0 AND pa15.lypw < 0.0) OR (pa14.pw > 0.0 AND pa15.lypw < 0.0)) THEN (COALESCE(pa14.pw, 0) - COALESCE(pa15.lypw, 0)) / NULLIF(pa15.lypw, 0) * 100.0
        ELSE (COALESCE(pa14.pw, 0) - COALESCE(pa15.lypw, 0)) / NULLIF(pa15.lypw, 0) * 100.0
    END AS sal_cy_pr_vly_pct,
    pa16.wtdsales AS wtd_amt,
    pa11.ytd_mrg_amt,
    pa12.ly_mrg_amt,
    pa13.htd_mrg,
    pa110.primary_store_shopped,
    pa111.wtd_ly_amt,
    pa112.pw_ly_amt,
    pa113.htd_ly_amt,
    pa114.ytd_ly_amt,
    pa115.rol3mth_sales_amt,
    pa116.rol3mth_ly_sales_amt,
    pa117.rol6mth_sales_amt,
    pa118.rol6mth_ly_sales_amt,
    pa119.rol12mth_sales_amt,
    pa120.rol12mth_ly_sales_amt,
    pa121.noof_distinct_stores_shopped,
    pa122.htd_avg_tkt_amt,
    pa123.htd_trans_count,
    pa124.wtd_mrg_amt,
    pa125.htd_mrg_amt
FROM 
    pa11
LEFT JOIN pa12 ON pa11.lfp_org_identifier = pa12.lfp_org_identifier
LEFT JOIN pa13 ON pa11.lfp_org_identifier = pa13.lfp_org_identifier
LEFT JOIN pa14 ON pa11.lfp_org_identifier = pa14.lfp_org_identifier
LEFT JOIN pa15 ON pa11.lfp_org_identifier = pa15.lfp_org_identifier
LEFT JOIN pa16 ON pa11.lfp_org_identifier = pa16.lfp_org_identifier
LEFT JOIN pa110 ON pa11.lfp_org_identifier = pa110.lfp_org_identifier
LEFT JOIN pa111 ON pa11.lfp_org_identifier = pa111.lfp_org_identifier
LEFT JOIN pa112 ON pa11.lfp_org_identifier = pa112.lfp_org_identifier
LEFT JOIN pa113 ON pa11.lfp_org_identifier = pa113.lfp_org_identifier
LEFT JOIN pa114 ON pa11.lfp_org_identifier = pa114.lfp_org_identifier
LEFT JOIN pa115 ON pa11.lfp_org_identifier = pa115.lfp_org_identifier
LEFT JOIN pa116 ON pa11.lfp_org_identifier = pa116.lfp_org_identifier
LEFT JOIN pa117 ON pa11.lfp_org_identifier = pa117.lfp_org_identifier
LEFT JOIN pa118 ON pa11.lfp_org_identifier = pa118.lfp_org_identifier
LEFT JOIN pa119 ON pa11.lfp_org_identifier = pa119.lfp_org_identifier
LEFT JOIN pa120 ON pa11.lfp_org_identifier = pa120.lfp_org_identifier
LEFT JOIN pa121 ON pa11.lfp_org_identifier = pa121.lfp_org_identifier
LEFT JOIN pa122 ON pa11.lfp_org_identifier = pa122.lfp_org_identifier
LEFT JOIN pa123 ON pa11.lfp_org_identifier = pa123.lfp_org_identifier
LEFT JOIN pa124 ON pa11.lfp_org_identifier = pa124.lfp_org_identifier
LEFT JOIN pa125 ON pa11.lfp_org_identifier = pa125.lfp_org_identifier;

Danh mục