Trouble filtering records - Need help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trouble filtering records - Need help

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2010-12-22 : 16:27:26

I have the following select statement:


 SELECT DISTINCT (LM.PART_ID) as PARTID, PM.PART_DESC as PARTDESCRIPTION, PM.PART_UM as PARTUM, PM.STD_UNIT_COST as PARTSTDCOST, PM.PART_TYPE as PARTTYPE,  VP.VENDOR_PART_ID as VENDORPARTID, LM.MFG_LOT_ID as VENDORLOT, VP.VENDOR_ID AS VENDORID, LM.LOT_ID as RBMLOTID
 FROM LTFLM LM LEFT OUTER JOIN POFVP VP ON LM.PART_ID = VP.PART_ID
 JOIN ICFPM PM ON PM.PART_ID = LM.PART_ID
 WHERE EXISTS (SELECT 1 FROM POFVP WHERE (VENDOR_ID = PM.PREF_VENDOR))
 ORDER BY LM.PART_ID

The query runs fine. The problem is that with some records I come up with more than 1 value. The reason is that the Lot Master record (LTFLM) may have a record where the Vendor Parts (POFVP) table will have two Vendors that can supply the same part. The records will be exactly identical with the only difference being the Vendor ID.

The software manufacturer did not set up a good relationship to determine which Vendor is correct. However, they do have a very convoluted way of determining which is the correct vendor. They go all the way back to the Purchase Order through several other tables to determine it. I don't need it to be that complicated.

I am downloading this information for an external system and don't really care about which vendor is selected. The vendors are usually the same company anyway, just different locations and the Vendor ID is used as a reference for research anyhow. I only want to grab the first vendor part record which matches the Lot Master record. However, I need to keep everything else intact, because I am pulling data from a third table. Does anyone have any solutions for this? Your help would be greatly appreciated.

BruceT
Yak Posting Veteran

78 Posts

Posted - 2010-12-23 : 10:05:31

Maybe take a look at the

ROW_NUMBER() OVER(PARTITION BY

command. It will essentially let you group by vendor assigning a row number to each row and then select only the rows with a row number of 1.

You can search the forum for examples.

texassynergy
Starting Member

26 Posts

Posted - 2010-12-23 : 12:05:49

Bruce, Thanks for your suggestion. It is a little convoluted, but I have a variation of my script which I believe will reach my goal. I have the following code:


 SELECT ROW_NUMBER() OVER(PARTITION BY VP.PART_ID ORDER BY VP.VENDOR_ID) AS ROWNUM, VP.PART_ID, VP.VENDOR_ID
 FROM POFVP VP, ICFPM PM
 WHERE (VP.PART_ID = PM.PART_ID)  AND ROWNUM = 2
 ORDER BY VP.PART_ID

However, I get the error that ROWNUM is an Invalid column name even though my example looks like dozens that I have reviewed. It doesn't matter if I use () around ROWNUM in the Select statement, I still get the same result. To get around this issue, I have loaded the data into a temporary table. Now I can use my previous code and just join the temp table where ROWNUM = 1, instead of the POFVP table. This should get me where I want.

If you have any thoughts on why ROWNUM is an invalid column name, I would appreciate the comments. Maybe it has something to do with the table joins. None of the examples I reviewed had any table joins.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-23 : 12:18:09

You can't use the result of ROW_NUMBER() directly in the WHERE clause.
Try this instead:

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY VP.PART_ID ORDER BY VP.VENDOR_ID) AS ROWNUM, VP.PART_ID, VP.VENDOR_ID
 FROM POFVP VP, ICFPM PM
 WHERE VP.PART_ID = PM.PART_ID
)dt
WHERE ROWNUM = 2
ORDER BY PART_ID

No, you're never too old to Yak'n'Roll if you're too young to die.

texassynergy
Starting Member

26 Posts

Posted - 2010-12-23 : 16:52:46

Thanks WebFred. That makes sense. I see that now in the examples. Appreciate the input.