SQL - how to limit a field to certain expressions

mmartin999

New Member
Joined
Oct 4, 2006
Messages
33
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
try replacing WHERE (((FISCAL_CAL.Fiscal_year) Like "F07")) with
WHERE (((FISCAL_CAL.Fiscal_year) IN ("F07","F08") ))

hth,
Giacomo
 

mmartin999

New Member
Joined
Oct 4, 2006
Messages
33
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

New Member
Joined
Oct 4, 2006
Messages
33
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,880
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top