Home » SQL & PL/SQL » SQL & PL/SQL » PLease help with query Need to select distict from one table insert into another
PLease help with query Need to select distict from one table insert into another [message #212606] Sat, 06 January 2007 08:46 Go to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
Hello, I need to select distinct vendor_site_id from one table and insert into another table I need all the columns inserted. I started out with but its incorrect
select distinct vendor_site_id into po_vendor_sites_t2 from po_vendor_sites_temp;
INSERT INTO PO_VENDOR_SITES_T2(
VENDOR_SITE_ID
, VENDOR_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, VENDOR_SITE_CODE
, LAST_UPDATE_LOGIN
, CREATION_DATE
, CREATED_BY
, PURCHASING_SITE_FLAG
, PAY_SITE_FLAG
, ADDRESS_LINE1
, ADDRESS_LINE2
, ADDRESS_LINE3
, CITY
, STATE
, COUNTRY
, ZIP
, PAYMENT_METHOD_LOOKUP_CODE
, PAYMENT_PRIORITY
, PAY_GROUP_LOOKUP_CODE
, INVOICE_CURRENCY_CODE
, PAYMENT_CURRENCY_CODE
, TERMS_DATE_BASIS
, TERMS_ID
, PAY_DATE_BASIS_LOOKUP_CODE
, TAX_REPORTING_SITE_FLAG)
VALUES(po_vendor_sites_s.nextval

but I need all fields from the po_vendor_sites_temp table but only the distinct vendor_site_id

[Updated on: Sat, 06 January 2007 09:07]

Report message to a moderator

Re: PLease help with query Need to select distict from one table insert into another [message #212611 is a reply to message #212606] Sat, 06 January 2007 09:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, select DISTINCT 'vendor_id'. But what with the rest? Which values do you want to insert besides this unique 'vendor_id' value?

For example, this is your original table:
vendor_id  last_update_date  vendor_site_code  created_by
---------  ----------------  ----------------  ----------
1          01.01.2007.       1234              Heineken
1          03.01.2007.       5678              Montezuma

Now, you'd want to insert 1 as 'vendor_id'. But, what will you select as other values?
Re: PLease help with query Need to select distict from one table insert into another [message #212613 is a reply to message #212611] Sat, 06 January 2007 09:48 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
I want to insert all other values from the fields listed
Re: PLease help with query Need to select distict from one table insert into another [message #212617 is a reply to message #212613] Sat, 06 January 2007 10:10 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And what should their 'vendor_id' be? Continuing my previous example: first inserted record would be this:
vendor_id  last_update_date  vendor_site_code  created_by
---------  ----------------  ----------------  ----------
1          01.01.2007.       1234              Heineken
The second one would be this:
vendor_id  last_update_date  vendor_site_code  created_by
---------  ----------------  ----------------  ----------
?          03.01.2007.       5678              Montezuma
Instead of a question mark, what would you like to have? Obviously, it shouldn't be 1 (because it wouldn't be distinct any more).

P.S. Sorry, now I see that I'm using 'vendor_id' instead of 'vendor_site_id'. But I believe you got what I meant to say.
Re: PLease help with query Need to select distict from one table insert into another [message #212621 is a reply to message #212617] Sat, 06 January 2007 10:38 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
Ok I would like to go to try and sum this up by giving my problem first.. I have multiple sites (org_id) that pay the same vendor. I need to get rid of that duplication. eg
org_id vendor name vendor_site_code vendor_id
1 abc 1234 456
2 abc 1234 456 eliminate
2 efg 5678 678

Hope it helps sorry if I thrown you off somehow. I have two tables desc as
po_vendor:
VENDOR_ID NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
VENDOR_NAME NOT NULL VARCHAR2(80)
VENDOR_NAME_ALT VARCHAR2(320)
SEGMENT1 NOT NULL VARCHAR2(30)
SUMMARY_FLAG NOT NULL VARCHAR2(1)
ENABLED_FLAG NOT NULL VARCHAR2(1)
SEGMENT2 VARCHAR2(30)
SEGMENT3 VARCHAR2(30)
SEGMENT4 VARCHAR2(30)
SEGMENT5 VARCHAR2(30)
LAST_UPDATE_LOGIN NUMBER
CREATION_DATE DATE
CREATED_BY NUMBER
EMPLOYEE_ID NUMBER
VENDOR_TYPE_LOOKUP_CODE VARCHAR2(25)

and po_vendor_sites_all
VENDOR_SITE_ID NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
VENDOR_ID NOT NULL NUMBER
VENDOR_SITE_CODE NOT NULL VARCHAR2(15)
VENDOR_SITE_CODE_ALT VARCHAR2(320)
LAST_UPDATE_LOGIN NUMBER
CREATION_DATE DATE
CREATED_BY NUMBER
PURCHASING_SITE_FLAG VARCHAR2(1)
RFQ_ONLY_SITE_FLAG VARCHAR2(1)
PAY_SITE_FLAG VARCHAR2(1)
ATTENTION_AR_FLAG VARCHAR2(1)
ADDRESS_LINE1 VARCHAR2(35)
ADDRESS_LINES_ALT VARCHAR2(560)
ADDRESS_LINE2 VARCHAR2(35)
ADDRESS_LINE3 VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(25)
ZIP VARCHAR2(20)
PROVINCE VARCHAR2(25)
COUNTRY VARCHAR2(25)
AREA_CODE VARCHAR2(10)
PHONE VARCHAR2(15)
CUSTOMER_NUM VARCHAR2(25)
SHIP_TO_LOCATION_ID NUMBER
BILL_TO_LOCATION_ID NUMBER
SHIP_VIA_LOOKUP_CODE VARCHAR2(25)
FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25)
FOB_LOOKUP_CODE VARCHAR2(25)
INACTIVE_DATE DATE
FAX VARCHAR2(15)
FAX_AREA_CODE VARCHAR2(10)
TELEX VARCHAR2(15)
PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(25)
BANK_ACCOUNT_NAME VARCHAR2(80)
BANK_ACCOUNT_NUM VARCHAR2(30)
BANK_NUM VARCHAR2(25)
BANK_ACCOUNT_TYPE VARCHAR2(25)
TERMS_DATE_BASIS VARCHAR2(25)
CURRENT_CATALOG_NUM VARCHAR2(20)
VAT_CODE VARCHAR2(20)
DISTRIBUTION_SET_ID NUMBER
ACCTS_PAY_CODE_COMBINATION_ID NUMBER
PREPAY_CODE_COMBINATION_ID NUMBER
PAY_GROUP_LOOKUP_CODE VARCHAR2(25)
PAYMENT_PRIORITY NUMBER
TERMS_ID NUMBER
INVOICE_AMOUNT_LIMIT NUMBER
PAY_DATE_BASIS_LOOKUP_CODE VARCHAR2(25)
ALWAYS_TAKE_DISC_FLAG VARCHAR2(1)
INVOICE_CURRENCY_CODE VARCHAR2(15)
PAYMENT_CURRENCY_CODE VARCHAR2(15)
HOLD_ALL_PAYMENTS_FLAG VARCHAR2(1)
HOLD_FUTURE_PAYMENTS_FLAG VARCHAR2(1)
HOLD_REASON VARCHAR2(240)
HOLD_UNMATCHED_INVOICES_FLAG VARCHAR2(1)
AP_TAX_ROUNDING_RULE VARCHAR2(1)
AUTO_TAX_CALC_FLAG VARCHAR2(1)
AUTO_TAX_CALC_OVERRIDE VARCHAR2(1)
AMOUNT_INCLUDES_TAX_FLAG VARCHAR2(1)
EXCLUSIVE_PAYMENT_FLAG VARCHAR2(1)
TAX_REPORTING_SITE_FLAG VARCHAR2(1)
ATTRIBUTE_CATEGORY VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
REQUEST_ID NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
VALIDATION_NUMBER NUMBER
EXCLUDE_FREIGHT_FROM_DISCOUNT VARCHAR2(1)
VAT_REGISTRATION_NUM VARCHAR2(20)
OFFSET_VAT_CODE VARCHAR2(20)
ORG_ID NUMBER

using these tables I need to get dulicate information and delete them because I have multiple org_id that have the same vendor, I only want one the unique vendors across all org_id, after that I will need to update the org_id and put it back into the po_vendors_sites_all table. So I need to update org_id then insert into po_vendor_sites_all table
Re: PLease help with query Need to select distict from one table insert into another [message #212622 is a reply to message #212606] Sat, 06 January 2007 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I read this thread & thought about it while scratching my head.
I went & had a 2nd cup of coffee & thought about it some more.
I am back & thinking about it more & more.
All I have to say is -
HUH?
>I need to get rid of that duplication. eg
org_id vendor name vendor_site_code vendor_id
>1 abc 1234 456
>2 abc 1234 456 eliminate
>2 efg 5678 678
On more than 1 occasion, I've had to "elimiate duplicates" & NEVER, EVER once did it involve using INSERT!!!!!!!!!!!!!!!
I always used DELETE; not INSERT.
http://asktom.oracle.com has many fine coding examples of how find & eliminate "duplicates".
Given what I have seen so far my advice is that you backup and/or export these tables before you attempt any changes.

HTH & YMMV!

[Updated on: Sat, 06 January 2007 12:03] by Moderator

Report message to a moderator

Re: PLease help with query Need to select distict from one table insert into another [message #212624 is a reply to message #212622] Sat, 06 January 2007 12:47 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
i know i have to delete but i need to update an insert the remaining
Re: PLease help with query Need to select distict from one table insert into another [message #212625 is a reply to message #212606] Sat, 06 January 2007 12:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Now I am even more confused than I was earlier.
> but i need to update an insert the remaining
HUH?
INSERT what to where & why?
UPDATE which to what & why?
Re: PLease help with query Need to select distict from one table insert into another [message #212626 is a reply to message #212625] Sat, 06 January 2007 13:04 Go to previous messageGo to next message
lifeisagift
Messages: 12
Registered: December 2006
Junior Member
as i said with org id so they can began using this vendor list
Re: PLease help with query Need to select distict from one table insert into another [message #212658 is a reply to message #212626] Sun, 07 January 2007 05:11 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
These tables are obviously Oracle Apps tables. Regardless of the solution you'll find (I'm confused too... no idea where this will end Wink ), are you aware of the fact that it's not allowed by Oracle support to edit data in Apps tables other than through the forms and/or API's provided by Oracle?
(or with explicit permission by Support).

Regards,
Sabine
Re: PLease help with query Need to select distict from one table insert into another [message #212663 is a reply to message #212658] Sun, 07 January 2007 07:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
skooman
These tables are obviously Oracle Apps tables.

What is obvious to you, isn't obvious to me. I don't know anything about Oracle Apps, but you (obviously Wink) do. Would you mind sharing this little secret: what makes it obvious?

BTW, I still have no idea what is to be done here.
Re: PLease help with query Need to select distict from one table insert into another [message #212738 is a reply to message #212663] Mon, 08 January 2007 01:27 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Littlefoot,

In this thread, apps is apparent in:

(a) Table names
A lot of applications will have something like vendors and perhaps vendor sites, but the vendor_sites_ALL gave me a hint - this is a common practice in apps: po_vendor_sites is a view with a lot of extra's like row based security, multi currency, multi organization, etc. and po_vendor_sites_all is the main underlying table without all the extras.

(b) Typical "apps columns", like attribute1 to 20, use of lookup codes and the combination of last_updated_by and last_update_login (first one is the database user, latter is the apps user id).

All of this can appear in other applications, but the combination made it quite convincing that we're looking at Apps tables.

Regards,
Sabine
Re: PLease help with query Need to select distict from one table insert into another [message #212742 is a reply to message #212738] Mon, 08 January 2007 01:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ah, all right! Definitely, one has to know Oracle Apps in order to notice such a pattern. Thank you for the explanation!
Re: PLease help with query Need to select distict from one table insert into another [message #212751 is a reply to message #212742] Mon, 08 January 2007 03:21 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
So, having cleared that up, now let's get on-topic again Very Happy

OP, let's get back to the problem. There are vendors and there are vendor sites. And some vendor sites appear in the vendor sites table more then once, with different org_id's, right?
Vendor site id is unique, so that one can't appear more then once, so I assume that the "doubles" are the ones with the same vendor id and vendor site code (and different org_id's), right?

Now, what exactly do you want to accomplish? It sounds a bit like you want to insert new rows into vendor_sites, with one new org_id and one row for each available vendor_site (based on vendor_site_code), is that it?
Or do you want to delete rows from vendor_sites? If so, which ones, based on what criterium?

(Like stated before: you're not allowed to edit data in apps tables! But frankly, adding some rows through a sql statement can't do much harm, only deleting rows can - that's why I want to find out what you want exactly).

Regards,
Sabine
Previous Topic: group by
Next Topic: buffer overflow
Goto Forum:
  


Current Time: Sat Dec 10 22:36:20 CST 2016

Total time taken to generate the page: 0.09684 seconds