August 30, 2018 at 9:41 am
I am writing a query to get item data based on the item that has a VENDOR_ID with a minimum value in a column called ITM_VNDR_PRIORITY. The ITM_VNDR_PRIORITY comes from another table that shares the key VENDOR_ID.
Query I am using to get the vendor's associated with an item:
SELECT * FROM PS_ITM_VENDOR
WHERE INV_ITEM_ID = '1003'Example 1. Results from PS_ITM_VENDOR table related to Item 1003:
SETID INV_ITEM_ID VENDOR_SETID VENDOR_ID ITMV_PRIORITY_LOC ITM_ID_VNDR ITM_VNDR_PRIORITY
SHARE 1003 SHARE 84764A 000001 CFG246 3
SHARE 1003 SHARE CAMLAB 000001 CAT895 4
SHARE 1003 SHARE GYRENT 000001 TDOC-7FS 2
SHARE 1003 SHARE RLMED 000001 CAT895 1I want to select the vendor for this Item ID that has the lowest ITM_VNDR_PRIORITY (1 in this example which is for VENDOR_ID "RLMED") and only return that vendor's rows to this query below (which does NOT contain the field ITM_VNDR_PRIORITY )where I am currently returning ALL vendor's associated to the item:
SELECT *FROM PS_ITM_VNDR_UOM UPDEV
WHERE INV_ITEM_ID = '1003'Example 2. Results from PS_ITM_VNDR_UOM related to item 1003:
SETID INV_ITEM_ID VENDOR_SETID VENDOR_ID VNDR_LOC
SHARE 1003 SHARE GYRENT 000001
SHARE 1003 SHARE RLMED 000001
SHARE 1003 SHARE 84764A 000001
SHARE 1003 SHARE CAMLAB 000001
SHARE 1003 SHARE GYRENT 000001
SHARE 1003 SHARE RLMED 000001I want this query to return only the item's associated with Vendor "RLMED" (bolded above) based on that vendor having the lowest (MIN) priority in the other table (PS_ITM_VENDOR) I want this query to return only the item's associated with Vendor "RLMED" based on that vendor having the lowest (MIN) priority in the other table PS_ITM_VENDOR (in example 1). This is what I have written so far as I'm not sure how to only get the vendor Id with the minimum ITM_VNDR_PRIORITY associated to the item.[/code]. This is what I have written so far as I'm not sure how to only get the vendor Id with the minimum ITM_VNDR_PRIORITY associated to the item.
This is the code I have written so far, using a sub-query to try to get the MIN Priority:
SELECT *FROM PS_ITM_VNDR_UOM UPDEV
LEFT OUTER JOIN PS_ITM_VENDOR UPDEC ON UPDEV.SETID = UPDEC.SETID
AND UPDEV.INV_ITEM_ID = UPDEC.INV_ITEM_ID
AND UPDEV.VENDOR_SETID = UPDEC.VENDOR_SETID
AND UPDEV.VENDOR_ID = UPDEC.VENDOR_ID
WHERE UPDEV.INV_ITEM_ID = '1003' AND EXISTS
(SELECT MIN(ITM_VNDR_PRIORITY)
FROM PS_ITM_VENDOR UPDEC
WHERE UPDEC.INV_ITEM_ID = UPDEV.INV_ITEM_ID
AND UPDEC.VENDOR_ID = UPDEC.VENDOR_ID)However I am still getting back all vendor's associated with this item, instead of just RLMED.
Any feedback is appreciated.
August 30, 2018 at 10:33 am
As long as the priority is by Inv_Item_ID
IF OBJECT_ID('tempdb..#Vendor') IS NOT NULL
BEGIN
DROP TABLE #Vendor
END
go
IF OBJECT_ID('tempdb..#Vendor_UOM') IS NOT NULL
BEGIN
DROP TABLE #Vendor_UOM
END
go
Create table #Vendor(
SETID varchar(10),
INV_ITEM_ID int,
VENDOR_SETID varchar(10),
VENDOR_ID varchar(10),
ITMV_PRIORITY_LOC varchar(10),
ITM_ID_VNDR varchar(10),
ITM_VNDR_PRIORITY int)
insert into #Vendor
values
('SHARE',1003,'SHARE','84764A','000001','CFG246',3)
,('SHARE',1003,'SHARE','CAMLAB','000001','CAT895',4 )
,('SHARE',1003,'SHARE','GYRENT','000001','TDOC-7FS',2)
,('SHARE',1003,'SHARE','RLMED', '000001','CAT895',1)
,('SHARE',1004,'SHARE','GYRENT','000001','TDOC-7FS',2)
,('SHARE',1004,'SHARE','RLMED', '000001','CAT895',10)
Create table #Vendor_UOM(
SETID varchar(10),
INV_ITEM_ID int,
VENDOR_SETID varchar(10),
VENDOR_ID varchar(10),
VNDR_LOC varchar(10))
insert into #Vendor_UOM
values
('SHARE',1003,'SHARE','GYRENT','000001')
,('SHARE',1003,'SHARE','RLMED ','000001')
,('SHARE',1003,'SHARE','84764A','000001')
,('SHARE',1003,'SHARE','CAMLAB','000001')
,('SHARE',1003,'SHARE','GYRENT','000001')
,('SHARE',1003,'SHARE','RLMED ','000001')
,('SHARE',1004,'SHARE','GYRENT','000001')
,('SHARE',1004,'SHARE','RLMED ','000001')
SELECT U.*
FROM #Vendor_UOM U
JOIN (select v.Vendor_ID, v.Inv_Item_ID
from #Vendor v
join (select Inv_Item_ID, min(ITM_VNDR_PRIORITY) ITM_VNDR_PRIORITY
from #Vendor
group by Inv_Item_ID) m
on v.Inv_Item_ID = m.Inv_Item_ID
and v.ITM_VNDR_PRIORITY = m.ITM_VNDR_PRIORITY) v
on u.Vendor_ID = v.Vendor_ID
and u.Inv_Item_ID = v.Inv_Item_ID
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 30, 2018 at 10:39 am
Thanks for the reply. Is that the only way to accomplish this is by creating temp tables? I thought there may be a little more simplistic approach...
August 30, 2018 at 11:09 am
kdrymer - Thursday, August 30, 2018 10:39 AMThanks for the reply. Is that the only way to accomplish this is by creating temp tables? I thought there may be a little more simplistic approach...
The temp tables are simply a means by which the code is demonstrated. You would use your actual tables.
Understand that we are volunteers and we don't necessarily have access to systems like yours (which I assume to be a PeopleSoft system based on the table name(s)).
August 30, 2018 at 11:16 am
Thanks - I figured they were there more as proof of concept. I adapted it using the actual tables and it seems to work perfectly and fast!
SELECT
U.*
FROM PS_ITM_VNDR_UOM U
JOIN (SELECT V.VENDOR_ID, V.INV_ITEM_ID
FROM PS_ITM_VENDOR V
JOIN (SELECT INV_ITEM_ID, MIN(ITM_VNDR_PRIORITY) AS ITM_VNDR_PRIORITY
FROM PS_ITM_VENDOR
GROUP BY INV_ITEM_ID) AS M
ON V.INV_ITEM_ID = M.INV_ITEM_ID
AND V.ITM_VNDR_PRIORITY = M.ITM_VNDR_PRIORITY) AS V
ON U.VENDOR_ID = V.VENDOR_ID
AND U.INV_ITEM_ID = V.INV_ITEM_ID
AND U.INV_ITEM_ID = '1003'
AND U.DFLT_UOM = 'Y'
Thanks again!
August 30, 2018 at 11:54 am
With an appropriate index, the following may perform better. The index would be on Vendor(INV_ITEM_ID, ITM_VNDR_PRIORITY).
SELECT vu.*
FROM #Vendor_UOM vu
CROSS APPLY
(
SELECT TOP(1) *
FROM #Vendor v
WHERE v.INV_ITEM_ID = vu.INV_ITEM_ID
ORDER BY v.ITM_VNDR_PRIORITY
) vp
WHERE vu.VENDOR_ID = vp.VENDOR_ID
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply