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 am selecting around 10000 records in a recordset using an sql query.I need to delete these 10000 records from 10 different tables as each of the table have these records.I can use a FOR Loop for each record one by one to delete these 10K records from each tabel but i was wondering if i can delete there 10K in one go so that the program would Loop Only once rather thatn 10K times.
Mine Delete code looks like:
if x.first is not null then for i in x.first..x.last loop delete tbl_A where trade_id = x(i).trade_id; delete tbl_B where trade_id = x(i).trade_id;
[code]...
Where x is the recordset having around 10K records.Is there any way i can run the loop only once rather than one by one for 10K times.
I am trying to set the min date if there is no gap between dates.compare previous date2 value with current date1,if they are same then my new date will be min(date1).
I am trying to compare records in a table in both Oracle and MSSQL database against a single standard dataset in my test case. However, I'm getting different results for each when I sort using a "order by".
select COL1 from TABLE1 order by COL1
In MSSQL, I get: COL1 ===== A A. A++ A++.
In ORACLE, I get: COL1 ===== A A++ A++. A.
I mean, oracle result some what makes sense, because "." has bigger ascii value than "+". But is there anyway to make the ORACLE sort order look exactly like MSSQL result?
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE CURSOR tranche_balance_cur IS SELECT empno, ename, sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4 Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2: I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13 Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno) AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename) AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal) AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
Can we compare in SQL *Loader control file by using WHEN Clause.I want to load the data when in_no greater than 1300000000. While running below control file i am getting error as:
SQL*Loader-350: Syntax error at line 5. Illegal combination of non-alphanumeric characters WHEN (in_no >= '1300000000') Here is the control file.
ex:
Load Data infile * discardfile 'test_when.dsc' truncate into table test_when WHEN (in_no >= '1300000000') fields terminated by ',' (a,b,c, in_no) [code]....
1) Sal_master structure is voc_no varchar2(7),voc_date date
2) sal_detail structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
3). delivery_master structure is voc_no varchar2(7),voc_date date;
4) delivery_detail structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
I want to compare these four tables i have insert 10 rows in sal_master and sal_detail tables and 5 transaction in delivery tables how to compares 10 records of sal_master,detail with delivery_master and detail if not exist in delivery_master and detail tables then display only sal_master,detail records for example
Voc_no Sale Qty Deliver Qty Remaining Qty
S000075 10 5 5 if data not found from delivery master and detail then answer must be S000075 10 0 10
I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison. e.g. "oracle infotech" and "infotech oracle" are 100% match
INSERT INTO CHK VALUES(TO_DATE('25-JUN-10 04.12.57 AM', 'DD-MON-YY HH:MI:SS AM'));
COMMIT;
[Code].....
TZ_OFFS TZ_OFFS GMT_TIME ------- ------- -04:00 +00:00 25-JUN-10 04.12.57.000000000 AM
EST_TIME ----------- 25-JUN-10 12.12.57.000000000 AM EST
9 am ---- 25-JUN-10 09.00.00.000000000 AM EST
8 pm ----- 25-JUN-10 08.00.00.000000000 PM EST
OUTPUT --------- 30-JUN-11
My doubt here is that even though dt_request_datetime is between 9 am in EST timezone and 8 pm in EST timezone the query output is not according to that.
and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:
input.event_start_time is between EVENTS.event_date and EVENTS.event_end_date and input.event_end_time is between EVENTS.event_date and EVENTS.event_end_date
but to compare only the hours here! (HH24:MI) because the date (dd/mm/yyyy) is checked before..
I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.
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've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.
I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b
where a.Col1 = 11060 and a.Col1=b.Col1 and a.Col2 <> b.Col2 order by Col1, Col2 [code]....
HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.
is it possible to compare two time values in oracle sql ie there is a column say 'tot' with values 8:29,11:35 etc(8hrs29 mins etc) can i compare this column with 03:00hrs i tried select case tot>=3 then 1 as days end from tablename;
I need to compare columns of two tables in oracle 10g. If columns of both tables match with each other, then i need to create new table dynamically with matched column name and datatype. For example, table1 contains name, ID, Phone_no and address table2 contains name, Id, address, area and pincode. now , i need to create table3 which will contains name, ID,address, Phone_no, area and pincode as columns( I mean matched columns should not be repeated in table3). how to do this..
I have oracle base version and our client version. In oracle base version we have "products.fmb" file In our client version "item.fmb" file.it was created using the base version.After that many people made changes to client version. Now I want to compare those two files. But in our system there is no form builder. Is it possible to compare without FORM builder.
I have to compare 2 tables on a columns's value.The 2 tables have same column as Regn_no.I just need to have the result in True Or False from a Query if the particular value of Regn_no is found in both the tables.
Temporarily I using a bit different and not good query which is as follows:
select count(*) from tab1 where regn_no in (select regn_no from tab2) and regn_no = 'UP78AN7890';
I have to generate a report for the audit with in the dates specified(range). I got the set of record for the specified date range by using the following query:
select * from fee_rule_aud where TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))>20090629 and TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))<=20100710 order by fee_rule,audit_date
this query returned some five records, now I have to iterate through this and compare 1st and 2nd row in first iteration (1st row will have the new value and 2nd row will have old vale). If there is any difference then insert into audit_log table which has the following structure:
Fee_rule , column_name, old_value,new_value
This process has to repeat for all the 5 rows like comparing 1st,2nd rows and 2nd,3rd rows and 3rd,4th rows and so on if it has more rows.
2114 rows I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn). I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.
AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).
We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:
CODEemp_salary ---------- emp_id NUMBER(15) NOT NULL effective_date DATE NOT NULL salary NUMBER(15) NOT NULL
Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:
CODEWITH salary_tab AS (SELECT effective_date, salary, (CASE WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) = salary
[code]....
For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:
CODE WITH salary_tab AS (SELECT effective_date, salary, (CASE WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) = salary
I've been assigned a task at work that consists of creating a trigger on a table. This table is used to store temporary query results. I'm trying to make a trigger to clean the table so old results don't accumulate.
There is a column named DATE_UPDATED that stores an Oracle Date for when the row was inserted/updated. If the row is not updated in 12 hours, I want to delete it.
Here is what I have so far.
CREATE OR REPLACE TRIGGER clean_tableName AFTER INSERT OR UPDATE ON tableName
BEGIN
END clean_tableName;
As you can see, I don't have an actual function body yet. What would be the best way to accomplish this? Should I declare a variable to store the current Date+12 hours and then just compare that in a delete statement?
I'm thinking something like:
DECLARE laterDate DATE := SYSDATE+12; // Not sure how to add 12 hours to a date. BEGIN DELETE FROM tableName WHERE DATE_UPDATED > laterDate; END clean_tableName;
But I'm not sure how to add 12 hours to the current date. Is there a way I can do this without defining a variable?
i wanted to compare a date in one of my tables to sysdate. I have a table reservation and a field in it is Date Reserved From, i wanted to compare this to sysdate and returned the results
