Skip to Content
0
Jan 20, 2017 at 11:05 AM

Search for multiple products using WHERE clause

67 Views

Hi guys - in the below query, I would like to return results, using the WHERE clause that searches only part of a product code BUT for multiple products . i.e. returns info against NB1233, NB1933, NB2533 .... these are the first 6 modifiers of certain product codes we have within our system - is it possible for me to do this using WHERE within one clause? I tried to use the CONTAINS clause but the did not work for my query.

SELECT
T2.[ItemCode]
, T2.[ItemName]
, SUM (T1.[Quantity]) AS 'Total Qty Sold'
, T3.[OnHand] AS 'Currently In Stock'
, T3.[OnOrder] AS 'Currently On Order'
, T2.MinLevel
, T2.MaxLevel
, CASE WHEN CEILING(((SUM (T1.[Quantity]) *2 - (T3.[OnHand]) - (T3.[OnOrder])))) < 0 THEN 0 ELSE CEILING(((SUM (T1.[Quantity]) *2 - (T3.[OnHand]) - (T3.[OnOrder])))) END AS 'Recommended to Order'

FROM
ODLN T0
INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode AND T3.WhsCode = T1.WhsCode

WHERE
****LEFT BLANK FOR STRING****
AND T0.[DocDate] BETWEEN [%1] AND [%2]
AND T1.[ItemCode] NOT Like '%%Freight%%'

GROUP BY
T2.[ItemCode],
T2.[ItemName],
T2.MinLevel,
T2.MaxLevel,
T3.[OnOrder],
T3.[OnHand]
ORDER BY
T2.[ItemCode]

As usual, any help will be greatfully received

Regards

Roy