syntax error from suggestion. any ideas?
Thanks for the suggestion. I tried the 1st and got a syntax error message. Can you see what I did wrong? I only modified the 1st SELECT. Maybe I should modify all?
SELECT * INTO [PROJECTED_DEPLOYMENT_TEST] FROM ( Planning_distribution_volumes_extract.SID, EDS_ITEM_MASTER.WGT_PER_UOM, EDS_ITEM_MASTER.ROLLUP_LEVEL_5_CD AS SBU, EDS_ITEM_MASTER.RAW_PROD_VARIETY_CD AS RPV, Planning_distribution_volumes_extract.FROM, Planning_distribution_volumes_extract.TO, Planning_distribution_volumes_extract.MODE, Planning_distribution_volumes_extract.BUC_ST_DTTM, Planning_distribution_volumes_extract.BUC_END_DTTM, Planning_distribution_volumes_extract.QTY, FISCAL_CAL.Fiscal_month, (EDS_ITEM_MASTER.WGT_PER_UOM*Planning_distribution_volumes_extract.QTY/100) AS CWT, "FROM "+Planning_distribution_volumes_extract.FROM+" TO "+Planning_distribution_volumes_extract.TO AS [KEY], EDS_ITEM_MASTER.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, EDS_ITEM_MASTER.STAT_VALUE
FROM (EDS_ITEM_MASTER RIGHT JOIN Planning_distribution_volumes_extract ON EDS_ITEM_MASTER.ITEM_NUM = Planning_distribution_volumes_extract.SID) INNER JOIN FISCAL_CAL ON Planning_distribution_volumes_extract.BUC_ST_DTTM = FISCAL_CAL.Fiscal_month_start
WHERE (((FISCAL_CAL.Fiscal_year) Like "F07"))
UNION ALL SELECT Purchase_Volumes.SID, HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM, HNAD_EDS_PROD1_ITEM_HIER_V.ROLLUP_LEVEL_5_CD AS SBU, HNAD_EDS_PROD1_ITEM_HIER_V.RAW_PROD_VARIETY_CD AS RPV, Purchase_Volumes.Vendor AS [FROM], Purchase_Volumes.TO, Purchase_Volumes.mode, Purchase_Volumes.dttm1, Purchase_Volumes.dttm2, Purchase_Volumes.qty, FISCAL_CAL.Fiscal_month,(HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM*Purchase_Volumes.QTY/100) AS CWT, "FROM "+Purchase_Volumes.vendor+" TO "+Purchase_Volumes.TO AS [KEY], HNAD_EDS_PROD1_ITEM_HIER_V.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, HNAD_EDS_PROD1_ITEM_HIER_V.STAT_VALUE
FROM (Purchase_Volumes INNER JOIN FISCAL_CAL ON Purchase_Volumes.dttm1 = FISCAL_CAL.Fiscal_month_start) INNER JOIN HNAD_EDS_PROD1_ITEM_HIER_V ON Purchase_Volumes.SID = HNAD_EDS_PROD1_ITEM_HIER_V.ITEM_NUM
WHERE ( FISCAL_CAL.Fiscal_YEAR LIKE "F07") AND Purchase_Volumes.dttm1 IS NOT NULL
UNION ALL SELECT Purchase_Volumes.SID, HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM, HNAD_EDS_PROD1_ITEM_HIER_V.ROLLUP_LEVEL_5_CD AS SBU, HNAD_EDS_PROD1_ITEM_HIER_V.RAW_PROD_VARIETY_CD AS RPV, Purchase_Volumes.Vendor AS [FROM], Purchase_Volumes.TO, Purchase_Volumes.mode, Purchase_Volumes.dttm2, Purchase_Volumes.dttm2, Purchase_Volumes.qty, FISCAL_CAL.Fiscal_month,(HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM*Purchase_Volumes.QTY/100) AS CWT, "FROM "+Purchase_Volumes.vendor+" TO "+Purchase_Volumes.TO AS [KEY], HNAD_EDS_PROD1_ITEM_HIER_V.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, HNAD_EDS_PROD1_ITEM_HIER_V.STAT_VALUE
FROM (Purchase_Volumes INNER JOIN FISCAL_CAL ON Purchase_Volumes.dttm2 = FISCAL_CAL.Fiscal_month_start) INNER JOIN HNAD_EDS_PROD1_ITEM_HIER_V ON Purchase_Volumes.SID = HNAD_EDS_PROD1_ITEM_HIER_V.ITEM_NUM
WHERE ( FISCAL_CAL.Fiscal_YEAR LIKE "F07") AND Purchase_Volumes.dttm1 IS NULL