Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY to eliminate dups. and only vendors from current year
SQL QUERY to eliminate dups. and only vendors from current year [message #212573] Fri, 05 January 2007 22:12 Go to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
Hello, I need to clean up my vendor list in Oracle financials. PO_Vendors_sites_all. I need to get rid of the duplicate vendors and the vendors that have not been used in the last year I connected the po vendor table with this one but I need some help going forward to make this one sql command
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212574 is a reply to message #212573] Fri, 05 January 2007 22:27 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
and the sql which you used is?
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212576 is a reply to message #212574] Fri, 05 January 2007 22:56 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
I used select distinct on the po vendor site all table but I need to also get rid of the vendors not used with year
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212577 is a reply to message #212576] Fri, 05 January 2007 22:58 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
SELECT DISTINCT PO_PO_VENDORS.VENDOR_ID, PO_PO_VENDORS.VENDOR_NAME, PO_PO_VENDOR_SITES_ALL.INACTIVE_DATE, PO_PO_VENDOR_SITES_ALL.ADDRESS_LINE1, PO_PO_VENDOR_SITES_ALL.ADDRESS_LINES_ALT, PO_PO_VENDOR_SITES_ALL.ADDRESS_LINE2, PO_PO_VENDOR_SITES_ALL.ADDRESS_LINE3, PO_PO_VENDOR_SITES_ALL.CITY, PO_PO_VENDOR_SITES_ALL.STATE, PO_PO_VENDOR_SITES_ALL.ZIP, PO_PO_VENDOR_SITES_ALL.COUNTRY, PO_PO_VENDOR_SITES_ALL.PHONE, PO_PO_VENDORS.TAX_REPORTING_NAME, PO_PO_VENDOR_SITES_ALL.PAYMENT_METHOD_LOOKUP_CODE, PO_PO_VENDOR_SITES_ALL.PAYMENT_CURRENCY_CODE, PO_PO_VENDOR_SITES_ALL.CREATION_DATE, PO_PO_VENDOR_SITES_ALL.CREATED_BY, PO_PO_VENDORS.VENDOR_TYPE_LOOKUP_CODE
FROM PO_PO_VENDORS INNER JOIN PO_PO_VENDOR_SITES_ALL ON PO_PO_VENDORS.VENDOR_ID = PO_PO_VENDOR_SITES_ALL.VENDOR_ID;
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212578 is a reply to message #212577] Fri, 05 January 2007 23:02 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
If I add this to statement will it work not sure where it goes though where last_update_date >#1/1/2006#
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212590 is a reply to message #212578] Sat, 06 January 2007 02:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Punctuation exists - use it! I hope noone messed up your keyboard and pulled out comma, fullstop and similar keys. Without punctuation, your messages are difficult to read.

Also, use [code] tags to format your code. It is unreadable. No wonder you can't find an error in such a mess.

#1/1/2006# is not a valid date format in Oracle. Where did you find it? Also, using such a format suggests that you are about to rely on Oracle's implicit conversion between a string and a date. Do NOT rely on it - it will bring you trouble, sooner or later. Use TO_DATE function instead.

Finally, if it helps, you might try with

... last_update_date > TO_DATE('01.01.2006', 'dd.mm.yyyy')
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212594 is a reply to message #212577] Sat, 06 January 2007 03:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First of all, follow Littlefoot's recommendation on posts.
Your query joins PO_PO_VENDOR_SITES_ALL and PO_PO_VENDORS using VENDOR_ID. So I have a few questions:
1. What is PO_PO_VENDORS? Is it a table or a view on production data (eg. contracts)?
2a) Is VENDOR_ID a primary key or unique index on one of the mentioned tables (suppose PO_PO_VENDOR_SITES_ALL)?
2b) If not, do the tables contain duplications on that column?
2c) If yes, do you want to get rid of those duplications (rows with the same VENDOR_ID)?
3. What is the query you would like to use (generating 'duplicates')?
4. What to do in this scenario? You delete vendors that have not been used in the last year. The vendor wants to make a contract, but you do not have him in your evidence.
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212604 is a reply to message #212594] Sat, 06 January 2007 07:00 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
First of all Im sorry about the query I had duplicated the tables in access
1. production tables
2a. primary key
2b. yes the tables contain dups on that column
2c. No I do not want to get rid of those dups
3. I would like to get rid of duplicates from the org_id and the same vendor name across each org_id
4. Yes I will have them in history I need to update this cleaner list with new org_id for a new site then import them back into my po_vendor_all_site table
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212614 is a reply to message #212573] Sat, 06 January 2007 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>2a. primary key
>2b. yes the tables contain dups on that column
The above two lines are 100% totally contradictory to each other.
Primary key implies uniqueness; which implies NO duplicates.
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212615 is a reply to message #212614] Sat, 06 January 2007 10:02 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
I apologize there is no primary key, the thing is I have these vendors that already exist, I need to convert this list by cleaning it up getting rid of all the duplicate vendors that are the same vendors but different org_ids once thats done I will update the org_id and import to the po_vendor_sites_all table so that only the new org_id can see thoses vendors and not duplicates
Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212850 is a reply to message #212615] Mon, 08 January 2007 09:52 Go to previous messageGo to next message
herodt
Messages: 2
Registered: January 2007
Junior Member
Well, for I have to say this, do not do this to the PO_VENDORS table in Oracle Financials. Doing this is a bad, bad thing and you run the risk of orphaning invoices (AR/AP/PO) which will quickly lead to the path of a support call because nothing will post to GL and your companies financials system will fail to operate. That could lead to you finding work elsewhere after you get fired.

Just a warning in case you were unsure.


The table does not have an oracle enforced primary key on it, making life difficult.

What you are asking to do, simply doesn't make sense. the PO_VENDORS table does not contain the ORG_ID column as vendors are multi-org by design to eliminate duplicate vendors, only the sites is multi-org aware, if one org is seeing the SITE of a vendor (the address) then your org security is not setup properly on the forms, look that up.

If you are aware of all that, and have still been told to go ahead with this, you can join the po_vendor_sites_all and the po_vendors tables together, group by org and vendor_name and then manually remove what you think are duplicates but with the data in our 11.5.9 release, doing that is seriously damaging the system.


Re: SQL QUERY to eliminate dups. and only vendors from current year [message #212870 is a reply to message #212850] Mon, 08 January 2007 12:14 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
herodt wrote on Mon, 08 January 2007 16:52
Just a warning in case you were unsure.


I LOVE sarcasm!
Previous Topic: Need help on the query
Next Topic: diff b/n Join& merge
Goto Forum:
  


Current Time: Sun Dec 04 18:55:19 CST 2016

Total time taken to generate the page: 0.05052 seconds