WITH
CATALOG_SKU_CTE AS (
SELECT A.sku
FROM `catalog-stage.catalog_enterprise_views.sku_hierarchy_view` A, `catalog-stage.catalog_enterprise_views.sku_v` B
WHERE A.sku = B.SKU AND UPPER(B.group) = "MERCHANDISING"
AND UPPER(B.specification) = "NONCONFIGURABLE"
AND UPPER(B.classification) IN ("LUMBER","NORMAL")
-- ====================================
),
-- Get all current stores and their markets
STORE_CTE AS (
SELECT STR_NBR
FROM `assrtmgmt-stage.assrtmgmt.STR_CRDB_VIEW`
WHERE str_typ_ind = "N"
AND MKT_NBR <> 401
),
-- Create a list of SKU-store combinations for this triangulation
SKU_STORE_CTE AS (
SELECT A.SKU, B.STR_NBR
FROM CATALOG_SKU_CTE A, STORE_CTE B
),
-- ====================================
AC_CTE AS (
SELECT SKU, ASSORTED, LOCATION, STRATEGY
FROM `assrtmgmt-stage.AC_SHARED.product_assortment_commons_realtime`
WHERE UPPER(LOCATION_TYPE) = "STR"
AND SELLING_CHANNEL = 1
),
POG_PRODUCT_CTE AS (
SELECT DISTINCT SKU_NBR, DBKEY
FROM `spaceplanning-stage.SPACE_COMMONS.IX_SPC_PRODUCT_VIEW`
WHERE ACTV_FLG = true
), MYASSORT_CTE AS (
SELECT DISTINCT A.sku_id, B.str_id
FROM `assrtmgmt-stage.myassort_strategy.stock_set_sku` A,
`assrtmgmt-stage.myassort_strategy.stock_set_str` B
WHERE A.set_id = B.set_id
),
-- Get list of SKU-store combinations from POG where the SKU-store is either on live a POG or a Pending POG
POG_VIEW_CTE AS (
SELECT DISTINCT POG_SKUS.SKU_NBR, POG_STORES.STR_NBR
FROM POG_PRODUCT_CTE POG_SKUS,
`spaceplanning-stage.SPACE_COMMONS.IX_SPC_PLANOGRAM_VIEW` POG,
`spaceplanning-stage.SPACE_COMMONS.IX_SPC_POSITION_VIEW` POG_POS,
`spaceplanning-stage.SPACE_COMMONS.THD_STORE_POG_ASSIGN_VIEW` POG_STORES
WHERE POG_SKUS.DBKEY = POG_POS.DBPARENTPRODUCTKEY
AND POG_POS.ACTV_FLG = true
AND POG_POS.DBPRNTPLANOGRAMKEY = POG.DBKEY
AND POG.ACTV_FLG = true
AND POG.DBKEY = POG_STORES.DBPARENTPLANOGRAMKEY
AND POG_STORES.FINISHEDDATE IS NULL
AND POG_STORES.ACTV_FLG = true
)
-- Get list of SKU-store combinations from POG where the SKU-store is either on live a POG or a Pending POG
-- ===========================
-- MAIN QUERY
-- ===========================
SELECT
SSC.SKU AS `SKU`,
SSC.STR_NBR AS `Store`,
CASE
WHEN MA.sku_id is NULL THEN "N"
ELSE "Y"
END AS `MyAssortment`,
CASE
WHEN PVC.SKU_NBR is NULL THEN "N"
ELSE "Y"
END AS `POG`,
CASE
WHEN AC.ASSORTED IS NULL THEN "N"
WHEN UPPER(AC.ASSORTED) = "OFF" THEN "N"
WHEN UPPER(AC.STRATEGY) LIKE "%INACTIVE%" THEN "N"
WHEN UPPER(AC.STRATEGY) LIKE "%DELETE%" THEN "N"
WHEN UPPER(AC.STRATEGY) = "CLEARANCE" THEN "N"
ELSE "Y"
END AS `Assortment_Commons`
FROM SKU_STORE_CTE SSC
LEFT OUTER JOIN MYASSORT_CTE MA
ON SSC.SKU = MA.SKU_ID
AND SSC.STR_NBR = MA.STR_ID
LEFT OUTER JOIN POG_VIEW_CTE PVC
ON SSC.SKU = PVC.SKU_NBR
AND SSC.STR_NBR = CAST(PVC.STR_NBR AS INT64)
LEFT OUTER JOIN AC_CTE AC
ON SSC.SKU = AC.SKU
AND SSC.STR_NBR = AC.LOCATION
ORDER BY SSC.SKU, SSC.STR_NBR) ;
On the above query, I would like to create a new table from the below main select query.
When I add "CREATE or REPLACE TABLE stage.myassort.company_report as, it shows unrecognized keyword "CREATE". How do I create a new table from this select query?
-- ===========================
-- MAIN QUERY
-- ===========================
CREATE or REPLACE TABLE `stage.myassort.company_report` as
SELECT
SSC.SKU AS `SKU`,
SSC.STR_NBR AS `Store`,
CASE
WHEN MA.sku_id is NULL THEN "N"
ELSE "Y"
END AS `MyAssortment`,
CASE
WHEN PVC.SKU_NBR is NULL THEN "N"
ELSE "Y"
END AS `POG`,
CASE
WHEN AC.ASSORTED IS NULL THEN "N"
WHEN UPPER(AC.ASSORTED) = "OFF" THEN "N"
WHEN UPPER(AC.STRATEGY) LIKE "%INACTIVE%" THEN "N"
WHEN UPPER(AC.STRATEGY) LIKE "%DELETE%" THEN "N"
WHEN UPPER(AC.STRATEGY) = "CLEARANCE" THEN "N"
ELSE "Y"
Try to put `CREATE OR REPLACE TABLE ... AS` before your query.
CREATE OR REPLACE TABLE your_dataset.your_table AS
WITH my_cte AS (
SELECT ...
)
SELECT * FROM my_cte;
This keeps your query the same, but saves the result into a table.