Skip to Content
0

Search for multiple products using WHERE clause

Jan 20, 2017 at 11:05 AM

46

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Johan Hakkesteegt Jan 20, 2017 at 11:37 AM
0

Hi Roy,

It depends a bit. I am not exactly sure yet what you mean.

If you mean that you have (among others) a number of items which codes start with NB1233, NB1933, or NB2533 (i.e. NB123301, NB123302, NB123303, etc), you would have to add a line for each of these three itemcode starting parts, and best would be to add them in their own set of parenthesis:

WHERE (ItemCode LIKE 'NB1233%'
       OR ItemCode LIKE 'NB1933%'
       OR ItemCode LIKE 'NB2533%')
  AND /* the rest of the where clause */

Regards,
Johan

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Jan 20, 2017 at 11:42 AM
1

Another option is the IN operator with a sub query. Something along these lines:

WHERE ItemCode IN (select ItemCode 
                   from OITM 
                   where ItemCode LIKE'NB1233%' /* and/or some other criteria*/)
Share
10 |10000 characters needed characters left characters exceeded
Roy Bright Jan 20, 2017 at 12:39 PM
0

Hi Johan - nice to hear from you again, buddy :)

The first one is EXACTLY what I am looking for buddy - just wasn't sure how to seperate the individual products within the LIKE clause.

Many thanks :)

Regards

Roy

Share
10 |10000 characters needed characters left characters exceeded