October 1, 2010 at 8:40 am
I'm trying to write a query that will find the most recent price paid for each item and I thought I could accomplish that by using a subquery with a TOP 1. However, it's not working. I'm getting nulls for the item_price. I don't know why it is giving me nulls. Any assistance would be greatly appreciated and I thank you in advance for your time.
select item.company_code, item.item_code, item.standard_cost, po_query.item_price
from IM_ITEM_MASTER_MC item (nolock)
left outer join
(select top 1
det.company_code, det.item_code, det.item_price
from PO_PURCHASE_ORDER_DETAIL_MC det (nolock)
inner join PO_PURCHASE_ORDER_HEADER_MC head (nolock)
on det.Company_Code = head.Company_Code and det.PO_Number = head.PO_Number
where det.Company_Code = 'wor'
order by det.Company_Code, det.Item_Code, head.PO_Date_List1 desc) as po_query
on item.Company_Code = po_query.Company_Code and item.Item_Code = po_query.Item_Code
where item.Company_Code = 'wor'
October 1, 2010 at 8:46 am
You may end up with one non-NULL item code. Your query expression with TOP gets (logically) evaluated before the join. You can fix it with a CROSS APPLY -- or an OUTER APPLY if you want to show unpurchased items -- or use one of the windowing functions.
Is that clear?
October 1, 2010 at 8:52 am
I've never used those commands before. Let me read up on them and I'll get back to you if it doesn't make sense. Thank you for your time.
October 1, 2010 at 10:07 am
(edited because I failed to constrain by company code the first time)
Here, try this. Just saying, this would have been easier for me if you'd done the bit about creating and populating tables.
CREATE TABLE Im_Item_Master_Mc(item_code NCHAR(4)
, company_code NCHAR(3) CHECK (company_code IN ('wor'))
, PRIMARY KEY (company_code, item_code)
, standard_cost MONEY);
GO
INSERT INTO Im_Item_Master_Mc values('elmo', 'wor', 1);
INSERT INTO Im_Item_Master_Mc values('grvr', 'wor', 1);
INSERT INTO Im_Item_Master_Mc values('oskr', 'wor', 1);
CREATE TABLE Po_Purchase_Order_Header_Mc(po_number NCHAR(4)
, company_code NCHAR(3) CHECK (company_code IN ('wor'))
, PRIMARY KEY (company_code, po_number)
, po_date_list1 SMALLDATETIME);
GO
INSERT INTO Po_Purchase_Order_Header_Mc
values('smok', 'wor', '4/25/1968 7:45 PM');
INSERT INTO Po_Purchase_Order_Header_Mc
values('clok', 'wor', '4/25/1968 9:00 PM');
GO
CREATE TABLE Po_Purchase_Order_Detail_Mc (po_number NCHAR(4)
, company_code NCHAR(3) CHECK (company_code IN ('wor'))
, FOREIGN KEY (company_code, po_number)
REFERENCES Po_Purchase_Order_Header_Mc(company_code, po_number)
, item_code NCHAR(4) NOT NULL
, FOREIGN KEY (company_code, item_code)
REFERENCES Im_Item_Master_Mc(company_code, item_code)
, item_price MONEY NOT NULL
)
GO
INSERT INTO Po_Purchase_Order_Detail_Mc values('smok', 'wor', 'elmo', 1);
INSERT INTO Po_Purchase_Order_Detail_Mc values('smok', 'wor', 'grvr', 1);
INSERT INTO Po_Purchase_Order_Detail_Mc values('clok', 'wor', 'elmo', 2);
-- orignal version, with the NULLs
select item.company_code, item.item_code, item.standard_cost, po_query.item_price
from IM_ITEM_MASTER_MC item (nolock)
left outer join
(select top 1
det.company_code, det.item_code, det.item_price
from PO_PURCHASE_ORDER_DETAIL_MC det (nolock)
inner join PO_PURCHASE_ORDER_HEADER_MC head (nolock)
on det.Company_Code = head.Company_Code and det.PO_Number = head.PO_Number
where det.Company_Code = 'wor'
order by det.Company_Code, det.Item_Code, head.PO_Date_List1 desc) as po_query
on item.Company_Code = po_query.Company_Code and item.Item_Code = po_query.Item_Code
where item.Company_Code = 'wor'
-- quick fix using OUTER APPLY
select item.company_code, item.item_code, item.standard_cost, po_query.item_price
from IM_ITEM_MASTER_MC item (nolock)
OUTER apply
(select top 1
det.company_code, det.item_code, det.item_price
from PO_PURCHASE_ORDER_DETAIL_MC det (nolock)
inner join PO_PURCHASE_ORDER_HEADER_MC head (nolock)
on det.Company_Code = head.Company_Code and det.PO_Number = head.PO_Number
where det.Company_Code = 'wor' AND item.Company_Code = det.Company_Code and item.Item_Code = det.Item_Code
order by det.Company_Code, head.PO_Date_List1 desc) as po_query
where item.Company_Code = 'wor'
-- with the windowing function
/* If two PRs were marked with the same time, ROW_NUMBER will
arbitrarily select a instance for you; RANK or DENSE_RANK will show each
instance */
SELECT ITM.company_code, ITM.item_code, ITM.standard_cost, POP.item_price
FROM Im_Item_Master_MC ITM left JOIN
( SELECT row_number()
OVER (partition BY POD.company_code, POD.item_code
ORDER BY POH.po_date_list1 DESC) AS idx
, POD.company_code, POD.item_code, POD.item_price
FROM Po_Purchase_Order_Detail_MC POD INNER JOIN
Po_Purchase_Order_Header_Mc POH ON POD.company_code = POH.company_code
AND POD.po_number = POH.po_number) POP /*purchase order price*/
ON ITM.company_code = POP.company_code AND ITM.item_code = POP.item_code
AND POP.idx = 1
WHERE ITM.company_code = 'wor';
GO
DROP TABLE Po_Purchase_Order_Detail_Mc;
DROP TABLE Po_Purchase_Order_Header_Mc;
DROP TABLE Im_Item_Master_Mc;
The windowing function isn't any faster. It just adds some clarity as to what's going on to the reader, I think.
I did use some trick with TOP to get the data back before we had OUTER APPLY, but I forget what it was. APPLY is pretty slick.
October 1, 2010 at 12:07 pm
That's good stuff. Thank you, Ian!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply