Views or Tables

Analytics

Views or Tables

I have to extract data from 3 huge production tables which have the data at week level and needs to be transformed to day level. I need this data extract to do some additional processing based on it using a bteq script. Is it better to have the data extracted during the bteq script execution process or is it better to have a view created for these using the following sqls and get the data from the views during bteq execution process? Which one will give us better performance?
query 1:
--------
SELECT TBL1.SKU,TBL1.STORE,CAL.DT,
SUM(
CASE CAL.DAY_OF_WK
WHEN 1 THEN TBL1.SAT_SH_QTY
WHEN 2 THEN TBL1.SUN_SH_QTY
WHEN 3 THEN TBL1.MON_SH_QTY
WHEN 4 THEN TBL1.TUE_SH_QTY
WHEN 5 THEN TBL1.WED_SH_QTY
WHEN 6 THEN TBL1.THU_SH_QTY
WHEN 7 THEN TBL1.FRI_SH_QTY
END
)
FROM TABLE123 TBL1 JOIN CALENDAR_DAY CAL
ON TBL1.WEEKID = CAL.WEEKID
JOIN ITEM_DESC ITM
ON ITM.SKU = TBL1.SKU
JOIN DEPT_TBL DEP
ON ITM.DEPT = DEP.DEPT
AND ITM.SUBCLASS = DEP.SUBCLASS
AND ITM.LINE = DEP.FINELINE
WHERE TBL1.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X')
AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1
GROUP BY TBL1.SKU,TBL1.STORE,CAL.DT
HAVING SUM(
CASE CAL.DAY_OF_WK
WHEN 1 THEN TBL1.SAT_SH_QTY
WHEN 2 THEN TBL1.SUN_SH_QTY
WHEN 3 THEN TBL1.MON_SH_QTY
WHEN 4 THEN TBL1.TUE_SH_QTY
WHEN 5 THEN TBL1.WED_SH_QTY
WHEN 6 THEN TBL1.THU_SH_QTY
WHEN 7 THEN TBL1.FRI_SH_QTY
END
) <> 0;

query 2:
---------
SELECT SLS.SKU,SLS.STORE,CAL.DT,SLS.REPORT_CODE,
SUM(
CASE DAY_OF_WK
WHEN 1 THEN SAT_QTY
WHEN 2 THEN SUN_QTY
WHEN 3 THEN MON_QTY
WHEN 4 THEN TUE_QTY
WHEN 5 THEN WED_QTY
WHEN 6 THEN THU_QTY
WHEN 7 THEN FRI_QTY
END

) UNITS,
SUM(
CASE DAY_OF_WK
WHEN 1 THEN SAT_AMT
WHEN 2 THEN SUN_AMT
WHEN 3 THEN MON_AMT
WHEN 4 THEN TUE_AMT
WHEN 5 THEN WED_AMT
WHEN 6 THEN THU_AMT
WHEN 7 THEN FRI_AMT
END
) RETAIL
FROM POS SLS JOIN CALENDAR_DAY CAL
ON SLS.WEEKID = CAL.WEEKID
JOIN ITEM_DESC ITM
ON ITM.SKU = SLS.SKU
JOIN DEPT_TBL DEP
ON ITM.DEPT = DEP.DEPT
AND ITM.SUBCLASS = DEP.SUBCLASS
AND ITM.LINE = DEP.FINELINE
WHERE SLS.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X')
AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1
GROUP BY SLS.SKU,SLS.STORE,CAL.DT,SLS.REPORT_CODE
HAVING SUM(
CASE DAY_OF_WK
WHEN 1 THEN SAT_QTY
WHEN 2 THEN SUN_QTY
WHEN 3 THEN MON_QTY
WHEN 4 THEN TUE_QTY
WHEN 5 THEN WED_QTY
WHEN 6 THEN THU_QTY
WHEN 7 THEN FRI_QTY
END

) <> 0;

query 3
---------
SELECT INV.SKU,INV.STORE,CAL.DT,
SUM(
CASE DAY_OF_WK
WHEN 1 THEN SAT_OH_QTY
WHEN 2 THEN SUN_OH_QTY
WHEN 3 THEN MON_OH_QTY
WHEN 4 THEN TUE_OH_QTY
WHEN 5 THEN WED_OH_QTY
WHEN 6 THEN THU_OH_QTY
WHEN 7 THEN FRI_OH_QTY
END
) OH_UNITS,
SUM(
CASE DAY_OF_WK
WHEN 1 THEN SAT_OH_QTY * RETAIL_AMT
WHEN 2 THEN SUN_OH_QTY * RETAIL_AMT
WHEN 3 THEN MON_OH_QTY * RETAIL_AMT
WHEN 4 THEN TUE_OH_QTY * RETAIL_AMT
WHEN 5 THEN WED_OH_QTY * RETAIL_AMT
WHEN 6 THEN THU_OH_QTY * RETAIL_AMT
WHEN 7 THEN FRI_OH_QTY * RETAIL_AMT
END
) OH_RETAIL
FROM INVTBL INV JOIN CALENDAR_DAY CAL
ON INV.WEEKID = CAL.WEEKID
JOIN ITEM_DESC ITM
ON ITM.SKU = INV.SKU
JOIN DEPT_TBL DEP
ON ITM.DEPT = DEP.DEPT
AND ITM.SUBCLASS = DEP.SUBCLASS
AND ITM.LINE = DEP.FINELINE
JOIN DEPT_TBL DEP
ON ITM.DEPT = DEP.DEPT
AND ITM.SUBCLASS = DEP.SUBCLASS
AND ITM.LINE = DEP.FINELINE
WHERE INV.STORE IN (SELECT STORE FROM STORE_INFO WHERE STORE_TYPE = 'X')
AND CAL.DT BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE - 1
GROUP BY INV.SKU,INV.STORE,CAL.DT;