SQL & PL/SQL :: How To Sum Data Of One Table Into Another

Jul 19, 2011

I have this table that stores following information

SQL> select * from data1;

CUST_ID IU OHB
--------- ----- ----------
001 1 10
001 2 10
001 3 20
001 5 25
002 4 30
003 7 10
003 8 35
004 11 40

8 rows selected.

I need to insert data from this table to another table. ( result1)

SQL> desc result1
Name Null? Type
----------------------------------------- -------- ---------------------------

CUST_ID CHAR(9)
SUM_OHB NUMBER

The output when result1 is queried should look like

SQL> select * from result1;

CUST_ID SUM_OHB
--------- ----------
001 65
002 30
003 45
004 40

Want to do in PL/SQL.

I have tried to simplified my original requirement. This is crux of that. Can not post other details.

Edited to add create and insert script

create table data1( cust_id char(9), iu char(5), ohb number);
INSERT INTO DATA1 ( CUST_ID, IU, OHB ) VALUES (
'001 ', '1 ', 10);

[Code]....

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Delete Previously Stored Data And Enter Only Current Data In A Table?

Jul 24, 2012

how to insert data in a table by deleting previous entered data and only inserting current data like:

CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);

[code]....

I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:

INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);

it must show aaa,8 and aaa,9 bt not the previous values.

NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.

View 4 Replies View Related

Forms :: First Form Clear Previous Data And Then Populate Data From Table

May 7, 2012

when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below

go_block('show');
clear_block(NO_VALIDATE);
declare
cursor c1 is select *
from qtr_demand order by 1;
begin
[code]..........

View 26 Replies View Related

Forms :: 6i - One Data Block Based On Table / Fetching Data

Nov 3, 2011

I am working on forms 6i. I have one data block based on table EMP.

Datablock-A: Empno, Ename, Sal

I have FIND Screen: Empno, Ename, FIND Button.

I can search the data through FIND Screen and populate data in Datablock A.

I am using below logic to search data through FIND Screen.

When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.

In Pre-query trigger of DatablockA:

copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');

It's working fine. But below case is failing.

Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.

But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.

why it's failing to pull records if i try to query data in first case only.

View 2 Replies View Related

Inserting Data In New Column Where Table Has Huge Data

May 26, 2013

I am trying to add a new column in a table and insert data from another column of same table.

alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit

As there are huge number of records in the POSITION table ...its taking for ever to execute this query.

View 1 Replies View Related

SQL & PL/SQL :: File Data Versus Table Data Comparison

Jun 21, 2010

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

employee_name
---------------------
Sudip
Kaushik
Joyanta
Soumya
Ritesh
Gautam

And the database table name called cmp_employee and it has column called employee_name under which the records are as follows

employee_name
--------------------
Sudip
Kaushik
Joyanta
Soumen
Souvik
Sahoo

Now, the "SQL Query" should return ---- "Soumya, Ritesh, Gautam" this three names.

View 2 Replies View Related

SQL & PL/SQL :: Import Data From Csv To Temp Then Alter Data As It Goes Into Another Table?

May 30, 2012

I need to import some data from .csv files. There is one file each day, so I want them to be automatically imported into the DB. This is the format it comes in:

DSM,LOD,20120524,01,01,9999AMP02,1.1262240,M,0.6397380,M
DSM,LOD,20120524,01,02,9999AMP02,1.1315700,M,0.6450840,M
DSM,LOD,20120524,01,03,9999AMP02,1.1297880,M,0.6450840,M

I want this data to go into TEMP_TABLE. It then needs to reformatted as it goes from temp_table to my_table:

filename,readingID, field1,field2,date,num1,num2,meterid,read1,m1,read2,m2

so filename is the actual name of the .csv file that this row came from. And reading id is date, num1, num2, meterid combined. And the remaining fields coming from temp_table

This is what I have:

procedure import_data()
begin
TEMPFILENAME CHAR(60)='DSM_2010_Husky_Oil_20120525064122_20120525065011.csv';
create table temp_table
(dsm char(3),lod char(3),usage_date date,he char(2),reading char(2),loc_id char(9),mwh number(15,10),eormmwh char(1),mvar number(15,10),eormmvar char(1));

[code]....

, which does not work at all.

View 2 Replies View Related

PL/SQL :: Queries To Get Data In Batches From A Huge Data Table

Jan 3, 2013

Here is my problem, i need to create some files with my own format(let say 5000 records each) from a huge data table (May contain 5 Million records). And i want this creation to be multi threaded.

so how can i form queries efficiently to fetch records like 1..5000 and 5001..10000 and so on. I can form some thing like select * from table where rownum<5000 and not exists ( already fetched records) . but it is not the efficient one.

View 5 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....

View 24 Replies View Related

SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database

Jul 19, 2010

There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.

Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.

View 19 Replies View Related

SQL & PL/SQL :: Pull In Last Loaded Data From Staging Table To Target Table?

Mar 9, 2011

I have a staging table and a target table. How do I pull in last loaded data from staging table to target table?

View 4 Replies View Related

Forms :: Read Data From Table And Insert To Another Table With A Cursor?

Feb 20, 2013

I have a table with a BLOB column ;

I want read data from table and insert to another table with a cursor

My code is :

procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;

[code]....

when I run form , error FRM-40734 occurred

error in line " fetch .... "

View 1 Replies View Related

SQL & PL/SQL :: Select Data From Test-1 Table Where ID Values In Table Exists In 2?

Aug 31, 2010

I have the below data in table test_1.

select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100

test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3

My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.

SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)

create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)

View 4 Replies View Related

SQL & PL/SQL :: Inserting Data In Target Table From Source Table

Nov 22, 2012

I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.

ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h

How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.

View 4 Replies View Related

SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table

Jul 18, 2012

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.

CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......

View 6 Replies View Related

SQL & PL/SQL :: Table Is Missing In User_segments Data Dictionary Table

May 20, 2012

In my database I found some anomalies like

SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....

The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.

SQL> SELECT * FROM COUNTRIES;

CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...

why the country table is missing in user_segments data dictionary table.

View 8 Replies View Related

PL/SQL :: Load Data From Index By Table To Table

Jun 27, 2013

We need to load data from index by table to table.Below code is working fine. 

declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin

[Code]....

But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............ 

create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;

[Code]....

Its throwing. How to implement this scenario .

View 2 Replies View Related

Moving Data From Table To Table

Dec 13, 2010

I need to move data from non-partinioned table to partitioned. The volume is about 60 millions rows. What is the fastest way to do that? I think about pareller insert and nologging. What do you think about this? May data pump be faster?

View 4 Replies View Related

SQL & PL/SQL :: Data Migration From Table To Table?

Nov 18, 2010

I Have two DB s with same tables, I need to insert the data from db1 tables to db2 tables. Primary ID starts from 1 on both the tables.

EXample:

Table1:
ID Name c3 c4 c5
1 Oracle1 x y b
2 Oracle2 x n b
3 Oracle3 x f b
4 Oracle4 x f b
5 Oracle5 x f b

Table2:

ID Name c3 c4 c5
1 ONT1 t y t
2 ONT2 t m h
3 ONT2 t b n

All the table1 data should be moved to table2..... Desired out put should be like following

ID Name c3 c4 c5
1 ONT1 t y t
2 ONT2 t m h
3 ONT2 t b n
4 Oracle1 x y b
5 Oracle2 x n b
6 Oracle3 x f b
7 Oracle4 x f b
8 Oracle5 x f b

Data volume is huge... There are 1500 tables and on an average there 10 Million records per table. Currently I am using DB links

View 2 Replies View Related

PL/SQL :: Moving Data Into From One Table To Another Table

Sep 2, 2013

 I have a table called Daily_usage (only 1 day data) which contains daily transaction records. After a day I have to move this data to another table named Daily_30days_usage table. this table contained the 30 days data. After 30 days the 31st day data should be deleted from Daily_30days_usage table. 

How can I implement this requirement without INSERT statement?

View 4 Replies View Related

Export/Import/SQL Loader :: Import Table Without Messing Up Existing Data In Table

Sep 6, 2012

table already exist & its little data too, may have to imp rest of lost data, is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y CONSTRAINTS=n

scenerio2 (if have to drop & recreate the entire table) is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y

just for single table imp

View 2 Replies View Related

How Data Is Written In Table

Nov 3, 2012

I have a table which contains 2 main columns apart from other columns. These two columns are REC_ID and ENTRY_DATE_TIME.

In this table data is inserted through a data load process using SQL Loader. REC_ID is populated to table through a sequence and ENTRY_DATE_TIME is populated as a sysdate.

select rec_id, to_char(entry_date_time, 'dd-mon-yyyy hh24:mi:ss') entry_date_time from itpcs_grt_oa_tran_rdpk_stock order by 1;

REC_ID ENTRY_DATE_TIME
1 12-oct-2012 07:06:23
2 12-oct-2012 07:06:31
3 12-oct-2012 07:06:35
4 12-oct-2012 07:06:21
5 12-oct-2012 07:06:32

If we see the data, then time for REC_ID 4 is less than REC_ID 3.

Now the question is if REC_ID is populated through a sequence then sequence 3 must have been created before REC_ID 4 and if so then why the ENRTY_DATE_TIME of REC_ID 4 is less than REC_ID 3.

Further to the question, how data is written in tables. What steps are performed by at database level when we any DML(insert) is performed.

My DB Version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

View 1 Replies View Related

Using Column Data As Table Name?

Nov 5, 2012

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?

View 1 Replies View Related

Data Filter Within Table?

Sep 5, 2013

I am not sure if Oracle has a simple solution for a problem that I have in retrieving data?

Conditions:

As per rule we have to hold data from 7 - 10 years so the data can be searched at any given time.Don't want to purge data and keep the historic data separate so the request can be redirected based on the year.

I am looking for a solution similar to windows .ocx search. When a application is requesting an ocx (active X control) the search happens in C;\windows\system32 and if the ocx doesn't exists then the search is done on the entire system. Similarly can a table be partitioned (just the term I am using not discussing Oracle partition here) so I can partition data from 2011,2012 and 2013 to search first and if the searched data doesn't exists then search the other partition (data from 2003-2010) ?

View 2 Replies View Related

Upload XML Data To Table

Oct 1, 2009

I need to upload data from XML file to oracle database table.For this I have referred some websites in which solutions given in a such a way that loading entire XML file into LOB column inside the table.But I want only data to be extracted from XML file.

Will SQL Loader be best option?

View 1 Replies View Related

SQL & PL/SQL :: Seeing Data As Values Of Table?

Mar 12, 2013

I have to see the information of DATA1,DATA2,DATA3,DATA4,DATA5 .

How can I write the select statement to see THE above AS follows

datas--column name
DATA1
DATA2
DATA3
DATA4
DATA5

View 17 Replies View Related

SQL & PL/SQL :: Combining Data From Same Table?

Jan 17, 2011

I'm having some trouble combining some data. I've done a lot of research into joins and merges, nothing really seemed to do what I needed. I could be over thinking it.

Basically, I want the query to look at the "info" table from a specified time-span (1 to 7). I want it to look at the source of the data and if it is "one_better" always use that data for that specific time. Otherwise, return the data from "one". The results don't care about the source of the data.

The "info" table I'm working with looks like this:

TIMEDATASOURCE
1DATA1one
1DATA1betterone_better
2DATA2one
3DATA3betterone_better
4DATA4one
4DATA4betterone_better
5DATA5 one
7DATA7one

I would like the results to look like this:

TIMEDATA
1DATA1better
2DATA2
3DATA3better
4DATA4better
5DATA5
7DATA7

View 1 Replies View Related

SQL & PL/SQL :: Copy Data From Table A To B?

Apr 30, 2010

i could copy data from tableA to tableB with the scenario below

1) Currently we have no unique key / primary key defined on both of this tables, due to the nature of data, so insert/update will not work
2) We cant truncate/insert as user will be accessing this tables when the copying process takes place, so we dont want to end up having a scenario where the table does not contain data at a certain time

Will materialize view full refresh work in refreshing a table so we could avoid the problem faced in point 1 & 2.

View 7 Replies View Related

SQL & PL/SQL :: Moving Data To Another Table

Jan 21, 2013

I have imported data into database using sqlloader into flat table. Now I need to move the data from this table to another table. This is production system and I must keep it online. So I decided to make script that will move data in small chunks and commit frequently to avoid waits and table locks.

Regarding the script I have question. I can to the bulk load of rowids. Is it possible to optimize the insert and delete in similar way instead of doing insert/delete in loop for each rowid ?

declare
type t_rowids is table of rowid;
rowids t_rowids;
begin
loop
select rowid bulk collect into rowids from ims_old.values_f2 where rownum < 1000;

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Fetch Data From Table

May 11, 2011

I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.

Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.

Is there any way to query that PLSQL table?

View 1 Replies View Related







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