Skip to Content

Search for multiple products using WHERE clause

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 20, 2017 at 11:37 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 20, 2017 at 11:42 AM

    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*/)
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 20, 2017 at 12:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded