I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE: CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT ( CALENDAR_KEY INTEGER NOT NULL, DAY_TIME_KEY INTEGER NOT NULL, SITE_KEY NUMBER NOT NULL, RESERVATION_AGENT_KEY INTEGER NOT NULL, LOSS_CODE VARCHAR2(30) NOT NULL, PROP_ID VARCHAR2(5) NOT NULL, [code].....
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS del_records NUMBER:=0; BEGIN DELETE FROM candidate c WHERE empid in (select c.empid from employee e, candidate c where e.empid = c.empid and e.emp_stat = 'TERMINATED' ); [code]....
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description from niku.srm_resources res, niku.pac_mnt_resources pac, niku.departments dep where res.unique_name = pac.resource_code and pac.departcode = dep.departcode and res.is_active = 1 and description like 'IMS%' and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be: 1. Identify Support Project 2. Identify Resources allocated to the project team 3. Compare with List of Support Resources 4. If any Resources in that list do NOT appear on the project, then return project.
Type Specification : CREATE OR REPLACE TYPE ArrayCounterSum AS OBJECT ( -- AUTHOR : CLIVE.GREGORY -- CREATED : 03-04-2010 14:44:02 14:44:02 -- Modified : S. Glass - Removed read function to increase performance
[code]...
Type Body : TYPE BODY ArrayCounterSum AS 2 3 4 5
[code]...
So the output will be : 6_10_15_13_14 (Sum of all rows and return as an array)My Goal is to AVG the ROWS and retrun it into array. so what change should I make in above code in order to get the AVG of all records.
I have a table that contains history for vehicle positions. In order to find the latest positions quickly, I've included a LATEST column that is 1 if the record is the latest position and 0 otherwise. The table is maintained via a stored procedure. The procedure first sets the latest record for the vehicle to history...
UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = <vehicle ID> AND latest = 1
Is it possible for me to end up with 2 latest records?Consider this scenario...
Session #1: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1 Session #2: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1 Session #1: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8533, -117.1180, SYSDATE, 1) Session #2: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8534, -117.1178, SYSDATE, 1)
I'd end up with 2 latest records. How can I protect against this? I considered using SELECT FOR UPDATE, but seems like there are too many negatives going that route
I just loaded the 10g client EM on my Windows workstation to connect to our 10g database.Everything works great except I dont see any place where I can edit records. I have to use SQL Plus to edit records but was hoping to use a GUI like I had with 9i client OEM.Before we had 10g, I used to have the 9i OEM and it let me edit records in 9i OEM where I could add, delete and update records with a user friendly interface.
how to retrive alternate records from the table in the answer table the first row wil be in upper case and the second row wil be in lower case ...like wise wat is the querry
WITH DATA AS ( SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL UNION ALL SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL union all [code]......
The above mentioned output is produced by using the following business rules:
-- row no 1 = Valid records -- row no 2 = effective date is 1 day after termination date of row no 1. Means valid records -- row no 3 = effective date is equal to the row no 2 termination date -- Means invalid record -- row no 4 = effective date is 1 day after termination date of row no 2 Means valid records -- row no 5 = The gap between row no 4 termination date and row no 5 effective date is more than 1 day. Means record is invalid.
Query always compare effective date with previous valid record termination date by using the above mentioned condition and return result accordingly.
Summary is that there should be 1 day gap between termination date and next effective date as well as effective date is always less than termination date in same rows. In addition, if record is invalid then next record check with previous valid record termination date.
I have been doing some code in collection for testing. I have been doing the below but getting the error.
SQL> desc t_mine; Name Null? Type ----------------------------------------------------- -------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30)
SQL> ed Wrote file afiedt.buf
1 declare 2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME; 3 v_elapsed PLS_INTEGER; 4 type allobjects_record is record 5 (owner varchar2(1000) [code].......
ERROR at line 15:
ORA-06550: line 15, column 15: PL/SQL: ORA-00947: not enough values ORA-06550: line 15, column 3: PL/SQL: SQL Statement ignored
I have a table zTEST with several products and ID's. Now I want to exclude all products which are in table zTEST_EXCL and have ID's up to ID+2. The Result is then in table zTEST_RESULT.
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:
Oracle Version : Oracle Database 11g Express Edition Release 11.2.0.2.0 - ProductionI have following sample data:
Sample DataWITH DATA AS( SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL UNION ALL SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL UNION ALL SELECT '100' .
[Code]....
Business Rules: I have to get all invalid records according to the below business rules.
- row no 1 and 2 are valid records because row no 2 effective date start after the termination date of row no 1 - row no 3 is invalid record because effective date start with termination date of row no 2 - row no 4 is valid record because effective date is after or greater than previous valid record termination date that is row no 2 (31-jul-2012). - row no 5 is invalid record effective date is smaller than previous termination date - row no 6 is valid record because effective date is greater than previous valid termination date that is row no 4 . - row no 7 is invalid record because same data already exist in row no 6 - row no 8 is valid because effective date is greater than previous valid record that is row no 6 termination date.
I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:
(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER ) OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))
If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.
Currently,I have this objective to meet. I need to query the database for certain results. After done so, I will need to compare the records: For example: the query return me with 10 rows of records, I then need to compare: row 1 with 2, row 2 with 3, row 3 with 4 ... row 9 with 10. The final result that I wish to have is 10 or less than 10 rows of records.
I have one approach currently. I do this within a function, hand have the variables call "previous" and "current". In a loop I will always compare previous and current which I populate through the record set using a cursor.
After I got each row of filtered result, I will then input it into a physical temporary table. After all the results are in this temporary table. I'll do a query on this table and insert the result into a cursor and then returning the cursor.
The problem is: how can I not use a temporary table. I've search through online about using nest tables. but somehow I just could not get it working.
how to replace the temp table with something else? or is there other approach that I can use to compare the row columns with other rows.
I have requirement like we need to send records from one table to another table. for example if i have 4 records in Table A , first i need to send only 2 records to Table B then again rest 2 records to the Table B.
I have a personname table which contains records of millions Person-names. My application has a requirment to return "any" 200 names that match the given Firstname and lastname entered by user.note the NOT actually "top-n", but "Any-N" , i.e. user wants "any" 200 names and NOT in any "specific order".
which is the best option to make most efficient search --
i am using oracle 10g. and my database was in arabic and when i press Ctrl+L the LOV was displaying all the records what i select. but when i want filter the lov by customer name it was not filtering.
