Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In
RachelW 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