RachelW
4Penny.net
Points: 7353

11/10/2017 1:14:44 PM

Return a query where the detail table has all the items in a parameter list

Huh?

Yeah, I know. 

This is a test, see if you can code this actual client request before you look at my solution. 

The requirement is to return a list of orders where the order has ALL of the items supplied in a comma separated list of parameters. So, if the parameter is 'ITEM1,ITEM2', the code would return a list of orders where the order has BOTH ITEM1 and ITEM2.

Our code starts like this

declare @items varchar(max)
declare @count int
 
--declare a table that mimics the SOP LINE table in Dynamics GP
declare @SOP10200 table (SOPNUMBE VARCHAR(21), ITEMNMBR VARCHAR(31))
 
--insert a series of orders
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD001','ITEM01')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD001','ITEM02')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD001','ITEM03')
 
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD002','ITEM02')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD002','ITEM03')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD002','ITEM04')
 
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD003','ITEM03')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD003','ITEM04')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD003','ITEM05')
 
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM03')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM04')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM05')
INSERT INTO @SOP10200 (sopnumbe, ITEMNMBR) values ('ORD004','ITEM06')
 
 
-- the @items parameter holds a comma separated list of items
--To test, vary these items
select @items = 'ITEM03, ITEM04'

So, given items ITEM03, ITEM04', above, we need a query that returns the orders that have those items:

Order ORD001 would not return, because it does not have ITEM004

Code your own solution, then look at mine. 

4Penny.net
Version: All
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3