CODEWITHSUNDEEP
sqlsql-server-2008
datacrunch
datacrunch

Reputation: 23

Like SQL Statement SQL Server 2008R2

I have a simple query but I have mutiple records I need to filter out. I'm using the like statment with wild cards. Is there a better way do do this then writing out each one? Can I create a udf, table that it refrences? How? If I can. Thanks :)

SELECT a.SalesOrderNo ,
       a.ShipExpireDate ,
       a.CustomerNo ,
       b.ItemCode ,
       b.LineKey ,
       b.QuantityOrdered ,
       b.QuantityShipped ,
       b.ItemCodeDesc ,
       b.ExplodedKitItem
FROM dbo.SO_SalesOrderHeader a
LEFT JOIN dbo.SO_SalesOrderDetail b
ON a.SalesOrderNo = b.SalesOrderNo
WHERE b.ItemType = '1'
  AND b.ItemCodeDesc NOT LIKE '%Cert%'
  AND b.ItemCodeDesc NOT LIKE '%Fee%'
  AND b.ItemCodeDesc NOT LIKE '%Tag%'
  AND b.ItemCode NOT LIKE 'GF%'
  AND b.ItemCode NOT LIKE 'PXDIALPREP'
  AND b.ItemCode NOT LIKE '/C%'
  AND a.ShipExpireDate = CONVERT(DATE, GETDATE(), 101)

Upvotes: 1

Views: 1437

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19194

Here's a different design that lets you put ItemCodeDesc in a seperate table (this could also be a TVF). I can't comment on performance though.

On a different note, be aware that because you are outer joining to sales order detail, this table can have NULL records. In turn your b.ItemType = '1' will always be FALSE when ItemType is NULL. So you may as well make it an inner join (and you might find your query plan is doing that anyway)

   SELECT a.SalesOrderNo ,
   a.ShipExpireDate ,
   a.CustomerNo ,
   b.ItemCode ,
   b.LineKey ,
   b.QuantityOrdered ,
   b.QuantityShipped ,
   b.ItemCodeDesc ,
   b.ExplodedKitItem
   FROM dbo.SO_SalesOrderHeader a
   LEFT JOIN dbo.SO_SalesOrderDetail b
   ON a.SalesOrderNo = b.SalesOrderNo
   WHERE b.ItemType = '1'
   AND b.ItemCode NOT LIKE 'GF%'
   AND b.ItemCode NOT LIKE 'PXDIALPREP'
   AND b.ItemCode NOT LIKE '/C%'
   AND a.ShipExpireDate = CONVERT(DATE, GETDATE(), 101) 
   AND NOT EXISTS (
        SELECT 1 FROM dbo.MappingTable MT 
        WHERE b.ItemCodeDesc LIKE MT.ItemCodeDesc
        )

Upvotes: 1

AaronLS
AaronLS

Reputation: 38364

Note: I am guessing that your criteria is meant to filter out item types that can't be shipped (like Fees), adjust as per your requirements.

The problem you are encountering is a result of discrete values being stored in an ID. Looks like you should have a column IsShippable, or better yet a code table for ItemCodeType with rows of Cert, Fee, Tag, etc. and the IsShippable column there. if you had a code table then you'd be able to do 

inner join ItemCodeTypes ict on ict.ItemCodeTypeId = b.ItemCodeTypeId and ict.IsShippable = 1

Cert, Fee, Tag, rows in the ItemCodeTypes table would have IsShippable = 0:

Id | Name |  IsShippable
1    Cert    0
2    Fee     0
3    Tag     0
4    Product 1
5    Book    1

Edit: To more directly answer your question, you could make a view like this, and then when you query from it easily filter on Where IsShippable = 1:

Select CASE 
  When b.ItemCodeDesc LIKE '%Cert%' Then 0
  When b.ItemCodeDesc LIKE '%Fee%' Then 0 
--etc.
  Else 1
END as IsShippable
,* 
From dbo.SO_SalesOrderDetail

Upvotes: 0

Related Questions