SQL - how to limit a field to certain expressions

[mmartin999]

All I want to do in this query is add "F08" anywhere it says WHERE (((FISCAL_CAL.Fiscal_year) Like "F07")). So I want F07 and F08 to be returned in the data.

can someone help? I don't know SQL at all and this query cannot be viewed graphically since it was written in SQL (by someone else).

thanks

 

[giacomo]

try replacing WHERE (((FISCAL_CAL.Fiscal_year) Like "F07")) with
WHERE (((FISCAL_CAL.Fiscal_year) IN ("F07","F08") ))

hth,
Giacomo

 

[mmartin999]

unfortunately, this didn't work. I get an error message "SELECT 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, Plan" is not a valid name.

any other ideas? (I took out what looked like one extra pair of parenthesis. It didn't work either way).

thanks

 

[mmartin999]

I meant to paste the entire query:

SELECT * INTO PROJECTED_SHIPMENTS_test
FROM [SELECT 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) IN ("F07","F08"))
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) IN ("F07","F08")) 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) IN ("F07","F08")) AND Purchase_Volumes.dttm1 IS NULL]. AS [%$##@_Alias];

 

[giacomo]

Does it run if you try to execute just the inner select? if not do you get a more descriptive error? The IN statement should work and as far as I can see you did no harm by removing the extra parenthsis

The other option would be to do an OR, like this:

(FISCAL_CAL.Fiscal_year like "F07" OR FISCAL_CAL.Fiscal_year like "F08")

hth,
Giacomo