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 have to load data file into a table. And the requirement is as below:
Input Data:
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|Siva 1234
The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234
I have to implement a new logic such that , Old logic which pulls data will stay in place.now New logic pull data form SAP . So i have implemented the new logic in to the existing cursor as below.
i.e.
Cursor new_sap old_query Union New_query;
I have 3 new cursor like dis. How to process these old and new cursors in FOR loop.. Ex:
For c1 in C2(order_number) if order_source is sap then ( dis filters old data which is not in SAP) new query in cursor should process else old query in cursor should process end if;
A basic select and group by query I am optimising for my Database course has returned results that indicate it will perform better on a clustered index when returning a smaller number of rows (5% of the largest table) and on a hash clustered index when returning higher volumes (50% and 80%). I understand that it is possible to use more than one index type on a table to improve performance, but I am struggling to understand how I might establish a hash cluster and a cluster on the same table? and then use hints to drive the query down one access path or the other.
1. I Wnat to analyze the buffer cache hit ratio. This is what i did.
DECLARE bufcac NUMBER(10, 2); BEGIN
[Code]....
2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad
Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?
e.g.
DECLARE name := 'BOB'; BEGIN insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES); END;
In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.
If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?
e.g.
name := 'BOB'; insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);
In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
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]..........
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.
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 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:
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
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.
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.
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.
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)
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.
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.
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.
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;
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?
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?
