SQL & PL/SQL :: Updating Sequence With Order Of Old Composite PK

Sep 5, 2011

I am replacing a composite PK with a new single PK as business changed. I am creating sequence to fill the new field that should present the PK but I need to fill it according to the values of the old composite PK.

CREATE TABLE ABC
(
COMP_PK1 NUMBER,
COMP_PK2 NUMBER,
COMP_PK3 VARCHAR2(8),
VAL VARCHAR2(50),
PRIMARY KEY (COMP_PK1, COMP_PK2,COMP_PK3)
);
[code].....

I need to update ABC and set ABC_SERIAL = SEQ_ABC.nextval but i need this to be done according to the order of the old composite primary key... i.e. rows with COMP_PK1 = 1 are filled before rows with COMP_PK1 = 2 and so on..

View 33 Replies


ADVERTISEMENT

SQL & PL/SQL :: Automate The Process Of Updating Sequence?

Jan 18, 2012

I have migrated database from postgresql to oracle...All sequences are migrated with their default values...(Start with 1) I already have 213 entries in a table and I want to begin using this for 214th entry ( replace with "start with 214")

How can I automate the process of updating "Start with" value of sequence with the max no of entry on my table every time I migrate data....

I have created a trigger that will automatically insert the next number from the sequence into the id column.

create trigger test_trigger
before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
/

View 2 Replies View Related

Forms :: Alpha Numeric Sequence Updating

May 18, 2012

We have document serial Nos increment program, which has the below query for selecting and Updating, which generates a Numeric Value;

Select Query:

Select doc_slno
from fin_jv_slno_mas
where jv_month = '05' and
jv_year = '2012'

Output of this Query: 800001

Update Query:

update fin_jv_slno_mas set doc_slno=docno+1 where
jv_year = '2012' and
jv_month = '05'

Output after Updation: 800002.

The above query is a normal Sequence updating for a Numeric Value; now we need to update it as Alpha Numeric as below

Example: 8A0001

How can we give the above update statement for Alpha Numeric updation?

View 16 Replies View Related

Trigger That Will On Updating Table Order And Changing Status To GoodsReceived

Nov 30, 2009

my simplified database schema is:

Order
OrderID
Status
OrderItems
OrderID
EAN
Amount
Store
EAN
Amount

now,I need trigger that will on updating table Order and changing status to "GoodsReceived" increase amounts in Store according to values in OrderItems.

create or replace trigger order_received_trigger
before update
on Order
for each row
begin
if (:old.status=4 and :new.status=1) then
/*
select ean, sum(amount)
from OrderItems
where OrderID=:old.OrderID
group by ean;
*/
end if;
end;

but now i dont know how to apply that select on table Store.

View 2 Replies View Related

RMAN :: List Backup In Order To Get SEQUENCE?

Feb 26, 2013

Suppose we need to run the following: DB is single instance. DB Version 10.2.0.5

run {
SET UNTIL SEQUENCE ##;
duplicate target database to QA;
}

Can we use “list backup of archivelog all” or “list backup" in order to get SEQUENCE?

View 2 Replies View Related

SQL & PL/SQL :: Finding Next Record In Cyclic Order In Table Using Sequence

Sep 4, 2010

I have a table as below:-

CREATE TABLE TEST_EMP_WIP
(
EMP VARCHAR2(20 BYTE),
WIP NUMBER,
MAX_SEQUENCE NUMBER
)

[Code]....

EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 4
011 1 5

I have to find the next EMP whose MAX_SQUENCE needs to be updated with a sequence. In this case the next would be '010' I fetched the EMP who falls after the max value of the column max_sequence in order of EMPCODE.

select emp_next from (
SELECT emp,max_sequence, LEAD(emp, 1, 0) OVER (ORDER BY emp) AS emp_next
from test_emp_wip where wip > 0 )
where max_sequence=(select max(max_sequence) from test_emp_wip where wip > 0)
EMP_NEXT
010
After sometime, the record would look like :-

EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 6 4
011 1 7 5

Now the next update should be done to the EMP '002' in cyclic manner.

tell me a way to do this. I can insert new columns if needed in table for calculation.

View 5 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 Replies View Related

SQL & PL/SQL :: Example Of Composite And Candidate Key

Jul 25, 2013

concept of composite and candidate keys. i have gone through all the definition part but did not understand the concept of creating candidate key for example:-

composite key

create table test(id number,pincode number(4),name varchar2(15),primary key(id,pincode));

table created with composite bcz i took pair of id and pincode.

here base on the same example want to create candidate key with the syntax.

View 24 Replies View Related

PL/SQL :: Composite Primary Key?

Jun 26, 2012

understanding composite primary key.

I know what is composite key and how it stores the data.. but i am not able to find when i have to choose the columns to be composite keys.

View 10 Replies View Related

SQL & PL/SQL :: Querying From A Primary Composite Key

May 20, 2010

I have the following 3 columns in my VERSIONS table.

MAJOR
MINOR
MICRO

The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.

select max(major) as max_major from versions
select max(minor) as max_minor from versions where major = :max_major
select max(micro) as max_micro from versions where minor = max_minor and major = max_major

Is there any way I can query out max(major),max(minor),max(micro) in a single query.

View 7 Replies View Related

SQL & PL/SQL :: Getting Minimum Composite Key From Group By

Oct 11, 2011

I have the following case:

create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)

[Code]....

I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used

select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;

I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.

View 4 Replies View Related

Composite Index - Performance?

Nov 8, 2012

I have a lot of queries on a table with WHERE clause:

WHERE CR_DATE > :y AND CR_VALUE = :x

Actually, there is an index on (CR_DATE) but much more selective index is on (CR_DATE, CR_VALUE). If I do not UPDATE any records on this table, is there any difference in INSERT operation (or another problem) when I replace actual index with multi-column index?

View 1 Replies View Related

SQL & PL/SQL :: ORA-06530 / Reference To Uninitialized Composite

Sep 24, 2013

problem with populating the collection object.

I am getting an error -

'ORA-06530: Reference to uninitialized composite"

My Code is as below

create type test_t as object (empno number, ename varchar2(20))
/
create type test_tt as table of test_t
/
create or replace procedure test_p
is

[code]....

View 13 Replies View Related

SQL & PL/SQL :: Normalization - Concept Of Primary Key / Composite Key

May 17, 2013

I am following Lynda Tutorial for Normalization. I have understand normalization.The core concept of primary key is that it uniquely identifies each record in the table, but here in the given below image the 'COURSE' field is repeating 'SQL101' value again and again but still the teacher in the video is calling it primary key and combination of date & Course column Composite key.how can a field which vales are repeating can be called as Primary Key?

View 12 Replies View Related

SQL & PL/SQL :: Foreign Key References Composite Primary Key

Jul 4, 2013

I have to create six tables as part of my assignment. I have created 5 tables. I am having trouble with the 6th. How can I reference a foreign key to a composite primary key? I have colored red the areas that I think are giving me trouble which is in the 6th table, the Registration Table.

Create Table Student
(
StudentID number (6,0) NOT NULL,
Student_Name varchar2 (20) NOT NULL,
Constraint Student_PK Primary Key (StudentID)
);

Create Table Faculty
(
FacultyID number (5,0) NOT NULL,
Faculty_Name varchar2 (20) NOT NULL,
Constraint Faculty_PK Primary Key (FacultyID)
);

Create Table Course
(
CourseID varchar2 (10) NOT NULL,
Course_Name varchar2 (20) NOT NULL,
Constraint Course_PK Primary Key (CourseID)
);

Create Table Qualified
(
FacultyID number (5,0) NOT NULL,
CourseID varchar2 (10) NOT NULL,
Date_Qualified varchar2 (10) NOT NULL,
Constraint Qualified_PK Primary Key (FacultyID, CourseID),
Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
);

Create Table Section
(
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
CourseID varchar2 (12) NOT NULL,
Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
);

Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
);

This is the error I am receiving:

Error starting at line 1 in command:
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
)

Error at Command Line:8 Column:104
Error report:
SQL Error: ORA-02256: number of referencing columns must match referenced columns
02256. 00000 - "number of referencing columns must match referenced columns"
*Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
*Action: Make sure that the referencing columns match the referenced columns.

View 5 Replies View Related

SQL & PL/SQL :: Add Composite Primary Key To Table That Has Self Referencing Foreign Key

Jan 21, 2012

I have an employee table which has a primary key and a self referencing foreign key, as shown here

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Now if i make the primary key composite, as shown below -

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id, name)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Oracle is throwing the following error -

ORA-02270: no matching unique or primary key for this column-list

How can this error be fixed without changing the composite primary key?

View 10 Replies View Related

Server Administration :: How To Apply Composite Key On A Table

Jul 21, 2011

How can i apply composite key on a table?

View 2 Replies View Related

SQL & PL/SQL :: Creating Composite Unique Key With Only 1 Column Allowed To Be Changed

Jul 3, 2013

I have a table say MY_TAB with columns as below

emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)

insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');

How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.

insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.

View 13 Replies View Related

SQL & PL/SQL :: Composite Index Having Varchar2 Number And CLOB Column

Feb 25, 2012

I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...

[URL]......

Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?

View 11 Replies View Related

Performance Tuning :: Creating Normal / Composite Indexes On Fields

Feb 14, 2013

I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?

11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;

Table dropped.

Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....

- dynamic sampling used for this statement
22 rows selected.

View 8 Replies View Related

Performance Tuning :: Composite Index - Explain Plan Full Table Scan On Lookup_fein?

May 12, 2011

I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.

SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,

[code]...

View 1 Replies View Related

Updating Table With Same Key?

Nov 19, 2008

I am using oracle 9i, and having trouble with updating a table.

I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.

why this error occurs or why this error did not occur on my PC,

[Update sql](key is CD and SDATE)
Update TBL1
set CD = 'A',

[Code]....

View 4 Replies View Related

Updating Using Subqueries

Oct 26, 2010

i was just working on one of my SQL assignments from my database management course, and thus far, this is the first that I just can't figure out. The question is:

Quote: Increase the credit limit of any customer who has any order that exceeds their credit limit. The new credit limit should be set to their maximim order amount plus $1,000. This must be done in 1 SQL statement

The bolded part is what I'm having trouble with.

What I have thus far:

UPDATE Customers
SET    CreditLimit = 1000 + (SELECT MAX(Amount) FROM Orders,  Customers WHERE Cust = CustNum)
WHERE CustNum IN (   
                   SELECT Cust
                   FROM Orders
                   WHERE Cust = CustNum
                   AND CreditLimit < Amount); 

So there's two tables that I'll be working with, Customers (the table I'm updating), and Orders (the table where the order amount is found). With the code I have so far, it does seem to be updating the correct tables at the very least, but not with the correct values. It's essentially updating the CreditLimit column with the new value of 1000 + the maximum amount in the order table, which is very close to what I want it to do, but I want it to be 1000 + the maximum amount for that specific customer.

CustNum is the primary key for the Customers table, and Cust is the foreign key that links each together.

(about the formatting, it looked much prettier in SQL Worksheet Plus)

View 8 Replies View Related

SQL & PL/SQL :: Updating CLOB Value?

Nov 9, 2011

create table top_uid(oldUID number,newUID number);

select * from top_uid;

OLDUID NEWUID
---------------------- ----------------------
1 1001
2 1002
3 1003
4 1004

create table topdUIDXML (uidinfo clob);

insert into topdUIDXML select '<filter name="test" topologyUID="1">' from dual;
insert into topdUIDXML select '<filter name="test2" topologyUID="2">' from dual;
insert into topdUIDXML select '<filter name="ftest" topologyUID="3">' from dual;
insert into topdUIDXML select '<filter name="qtest" topologyUID="4">' from dual;

select * from topdUIDXML

UIDINFO
---------------------------------------
<filter name="test" topologyUID="1">
<filter name="test" topologyUID="2">
<filter name="test" topologyUID="3">
<filter name="test" topologyUID="4">

the topdUIDXML table will contain the oldUID's in the clob XML. need to update the topologyUID in that topdUIDXML with the newUID from the top_uid.

View 5 Replies View Related

SQL & PL/SQL :: Updating Column In Every Row Except For The First

Jan 16, 2012

I am trying to find a way to update all of the rows in a table for a column EXCEPT for the very first row. I am not sure if this can be done while I enter my SET parameter or not. I have also thought about using an EXCEPTION in a stored procedure. For example, say I have the table listed below:

SQL> select * from example1;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ---------------
A 100 A 200 John 20111225
A 100 A 300 John 20111225
A 100 A 500 John 20111225
A 100 A 400 John 20111225
A 100 A 250 John 20111225
A 100 A 700 John 20111225
A 100 A 800 John 20111225
A 100 A 900 John 20111225
A 100 A 1000 John 20111225
A 100 A 1150 John 20111225
A 100 A 1275 John 20111225
A 100 A 3000 John 20111225

12 rows selected

I am wanting to update the table so that if there were more than 3 J_id's on the table for the same I_id then it will set all of the code1's and code2's to a C except for the very first one. Such as:

SQL> select * from example2;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ----------------
A 100 A 200 John 20111225
C 100 C 300 John 20111225
C 100 C 500 John 20111225
C 100 C 400 John 20111225
C 100 C 250 John 20111225
C 100 C 700 John 20111225
C 100 C 800 John 20111225
C 100 C 900 John 20111225
C 100 C 1000 John 20111225
C 100 C 1150 John 20111225
C 100 C 1275 John 20111225
C 100 C 3000 John 20111225

12 rows selected

I have done some searches and haven't seen any results.

View 12 Replies View Related

SQL & PL/SQL :: Updating CLOB

Apr 21, 2011

Ok, CLOB columns are such a hassle.

I have a variable in my script: v_field1 VARCHAR2(32000);

This is part of a cursor record:

v_mf_table IS TABLE OF mf_detail%ROWTYPE INDEX BY BINARY_INTEGER;
v_mf_record v_mf_table;

I use a FORALL to insert the data into a table:

FORALL x IN v_mf_record.FIRST .. v_mf_record.COUNT
INSERT INTO monthly_mf_snapshot VALUES v_mf_record(x);

BUT! v_field1 is > 4000 characters. Does this trash my changes of using FORALL? Do I need to deal with 4k chunks in an UPDATE instead?

View 30 Replies View Related

SQL & PL/SQL :: Updating On Select?

Jun 15, 2010

I have two tables,
CREATE TABLE repos
(
rep_key VARCHAR(10) NOT NULL,
base_term VARCHAR(100) NOT NULL,
blt_key INTEGER NOT NULL

[code]...

gloss table has the unique set of base_term as in repos. BLT_KEY will be primary_key in gloss and foreign key in repos.

Data in gloss table
BLT_KEY BASE_TERM

1 base1
2 base2
3 base3

Now, I need to update the BLT_KEY in gloss to matching entries in repos. Can I do that in a update on select statement? like,
UPDATE repos
SET blt_key = (SELECT gloss.blt_key
FROM repos,
gloss
WHERE repos.base_term = gloss.abase_term) This throws subquery returns more than one row.

And the end of update the repos table should look like,
REP_KEY BASE_TERM BLT_KEY

M001 base1 1
M002 base1 1
M003 base2 2
M004 base1 1

[code]....

Also I need a single query which can update on select as the no of records to be updated are more than 90000 in repos. So two step process would slow down the process

View 13 Replies View Related

PL/SQL :: Updating Records With Different IDs

Jun 6, 2012

Im looking for the posibility to update some records using new id with the column values with another id

example

the table contains these records:

id    gross      net

========================
7     0,1     0,0507749
8     0,2     0,1015499
9     0,5     0,2538748
10     0,83     0,4214
11     0,85     0,4315873

[Code]....

and I would like insert the same gross and net column values of ids 7 to 16 into columns with the ids 40 to 49 in the same order. therefore I would like to obtain the result that I describe below:

id    gross      net

========================
7     0,1     0,0507749
8     0,2     0,1015499
9     0,5     0,2538748
10     0,83     0,4214
11     0,85     0,4315873

[Code]....

View 4 Replies View Related

Updating Two Table Via SQL Loader

Jun 27, 2011

I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:

LOAD DATA
INFILE 'table1.dat'
INFILE 'table2.dat'
APPEND
INTO TABLE table1
WHEN tid='1'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(pid "pid.nextval", p_fname, p_lname, tid)
[code]....

The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:

table1.dat
,John,Doe,1
,Joe,Smith,1

table2.dat
10,,abc,1,
11,,xyz,1,

The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved