PL/SQL :: Split Result Of Table Data Comparison To Key(s) / Column / Old - New Each Row
Nov 8, 2012
I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)
how to compare these two tables to generate something like the following. I can join these two tables to generate the diff but it is one row per account.
client_nbr branch_cd, account_cd, action column, old_value, new_value
8888 123 45678 C account_clsfn_cd 004 005
8888 123 45678 C buy_cd 98 99
8888 012 34546 A sell_cd 12
8888 321 98765 D dividend_cd 1
I am using Oracle 10g so Unpivot cannot be used.
CREATE TABLE T1
(
CLIENT_NBR CHAR(4 BYTE) NOT NULL,
BRANCH_CD CHAR(3 BYTE) NOT NULL,
ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
ACCOUNT_CLSFN_CD CHAR(3 BYTE),
SELL_CD CHAR(2 BYTE),
BUY_CD CHAR(2 BYTE),
I have written a java code which reads 2 millions of data under a particular column from CSV file and store it into a set. Now there is a table in Oracle database which contains 10 millions of records for that particular column. Now, I want to form a SQL query which select those records under that particular column from the database table which is in CSV file but not in database table. For e.g.
If I consider the CSV file name as employee.csv and it has column called employee_name under which the records are as follows
I am having data in this format in my column 1234~2345~3456~4567.
I need a query to split the data in the column based on the identifier '~',so that i can pick out the value after the second occurrence of the identifier.
I've an Oracle Table which has around 300 columns. I've a requirement to split this single table into two tables (150 columns each) by a foreign key.
Now I want to know how to maintain the data integrity while I insert the data into two tables. which means each table should have equal number of rows as we insert the 300 columns data into tables each at a time.
I have a requirement in which the amount need to be split in to multiple period.
For example if there amount of 3000 and start and end date is 01-jan-2013 and 31-mar-2013 then the output of the query should be
Name Start Period End Period Jan-13 Feb-13 Mar-13 ------------------------------------------------------------------------------ XXX 01-JAN-13 31-MAR-13 1000 1000 1000
I have a table having a code column A and a date column D1 which represents the days when the code was inserted. I can have more then 1 date for a code.
The idea is to create another column (an id) which will correspond to all the inserted days of a code for a period of 4 month. if, after 4 month of the first insert, another records appears, then another id will be generated for my code. If a code has the date included in one of the intervals existing already, then it will just receive the corresponding id.
Example: id code date id1 cd1 01/01/2010 id1 cd1 04/03/2010 id1 cd1 22/04/2010 id2 cd1 27/05/2010 id2 cd1 21/06/2010
If a cd1 appears now on 22/05/2010, then i have to give it id2.
I have one issue regarding this column values displaying ,in one column value has contains big length ,total i have 8 columns ,now i want to show all columns with fixed width even data in particular column had big length will display next line not next row.
example: oracle and "oracle,Business,intelligence" are the data in two columns these two columns data column 1 and column 2 now i want to display in column1 'oracle' and column2 first line 'oracle' ,second line 'Business ' and next line 'intelligence' like below
1) Split values from "INST" Column : suppose 23 2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 , It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM 23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST ( IDx number, STARTx number, DURATION number, RESULT number ); insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
i'm working on this database assignment...and basically, no matter what I do my table seems to automatically split into a different table after 8 rows...i've tried googling it and using commands like....
set wrap off; set numwidth 20;
and fiddling with the format of each column but nothing seems to work...i just want my table to show as one table
here's my script create table patient (patient_number number(4) primary key, patient_name varchar(15) NOT NULL, address varchar(30) NOT NULL, telephone number(7) NOT NULL, patient_status char(1) NOT NULL, next_appt date, balance number(5,2), CHECK (balance >= 0), CHECK (patient_status='N' OR patient_status='A' OR patient_status='I')); [code]...
12 rows selected.the columns are actually aligned so don't worry about that...it's just the splitting of the rows.
QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;
NAME DOJ QUAL EMPID ---------- --------------- ---------- ----- sam 21-mar-2012 mca sony 03-jan-2000 mba 10610 maya 21-mar-2012 m.arch sonna 18-mar-2012 mis
[code]...
i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?
There is a table with almost 60 million records. This is a temporary table or rather a staging table. I want to be able to split the table into two tables. This is an example of what the staging table looks like.
1. The straightforward loop. Read each record > Go through table 1 , get a count of similar records> If exists, use that ID to populate detail table > If doesn't exist create a new ID and populate the detail table
2. Use two cursors. First cursor goes through all the records (*). Takes 1 line, creates an ID on 1st table. Second cursor finds all records that are similar to the first line. Populates the detail table using the ID and then deletes currently inserted rows from the staging table.
I have got the first idea working. It works fine but takes forever since it has to go through the whole table for selecting the count for each record.I tried implementing the second idea but I believe the cursor creates a snapshot of table at runtime so within the loop if it finds out record has been deleted, throws me an error.
I'm needing to pull data into a cursor, then split this data into 3 different tables, each having the same number of rows and a select number of columns from the original. i can pull the data, but then i can only access it one row at a time via FETCH, then i can't load into the 3 new CURSORS one row at a time.
I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
SELECT SUM(salary) FROM employees WHERE department_id =60 O/P is : 28800But when i use the above query in anonymous block it gives me 684400 DECLARE v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.
My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).So I could easily drop old data by dropping old partitions and tablespaces.
Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.The problem of this idea is that i have to update billions of rows.
It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.Is there any way to "link" this information to a column in MESSAGE table?
I mean something like this:
alter table MESSAGE add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;
Is there some possibility like that? Would the partitioning on this column / statement work?Would the value of the keep_message be calculated on runtime?
If so will the performance influence be noticeable?If so will the performance also sink if the application is querying all rows except the keep_message?
I have some tables. TABLE1 contains columns called query_id and a column called List_name TABLE2 holds a list of data and the TABLE_NAME = value in TABLE1 held under LIST_NAME TABLE3 contains column with Query_ID and other info.
Is it possible to grab the entry in TABLE1 under LIST_NAME as a TABLE_NAME in a data source?
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
