SQL & PL/SQL :: Comparing One Row With Other Within Same Table
May 2, 2012
I have a requirement to get the count of those records whose department is not changed since they have joined the organization.
Script:
CREATE TABLE ddumps(orgid NUMBER, orgdate DATE, orgdept VARCHAR2(10))
/
INSERT INTO ddumps VALUES(1,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2000','ORG2')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2001','ORG2')
/
INSERT INTO ddumps VALUES(2,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2000','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2001','ORG1')
ORGID ORGDATE ORGDEPT
1 1/1/1999 ORG1
1 1/1/2000 ORG2
1 1/1/2001 ORG2
2 1/1/1999 ORG1
2 1/1/2000 ORG1
2 1/1/2001 ORG1
since the orgid 1 has changed the dept from org1 to org2 I do not want this to be appeared in the final count. Results should only include the orgid 2 since it didn't changed any dept.
View 11 Replies
ADVERTISEMENT
Feb 15, 2011
I'm using oracle 10g.I have a table with 4 columns
main_group-----id--------start_date------------end_date
M1-------------1---------07FEB11---------------10FEB11
M1-------------2---------09FEB11---------------11FEB11
M1-------------3---------10FEB11---------------12FEB11
M1-------------4---------13FEB11---------------16FEB11
M2-------------5---------18FEB11---------------21FEB11
M2-------------6---------19FEB11---------------24FEB11
M2-------------7---------26FEB11---------------27FEB11
i need to group the id's which are having overlapping dates and the output should be
main_group-----id--------start_date------------end_date
M1-------------1---------07FEB11---------------10FEB11------G1
M1-------------2---------09FEB11---------------11FEB11------G1
M1-------------3---------10FEB11---------------12FEB11------G1
M1-------------4---------13FEB11---------------16FEB11------G2
M2-------------5---------18FEB11---------------21FEB11------G3
M2-------------6---------19FEB11---------------24FEB11------G3
M2-------------7---------26FEB11---------------27FEB11------G4
I can give you the logic first i'll sort the start_date(already sorted in given example), then i'll compare the 2'nd id start date with 1'st id end date if it is less than the 1'st id end date, which means overlapping is there, then i'll group those 2 id's in to same group if not group them into 2 different groups.
View 7 Replies
View Related
Dec 2, 2012
I am about to compare Indexes and PK's(Constraint) of 3 instances.
What DBA table/s should i use best? The comparison is done to a specific schema only.
PS:Which is better dba_ind_columns or dba_objects?
View 10 Replies
View Related
Aug 25, 2011
I am new to oracle, I have request to build a query,
we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.
Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...
View 12 Replies
View Related
Oct 30, 2011
I'm looking to see if there's a solution to my problem that I can use within the context of my business application interface into an Oracle RDMS. I have access to write custom SQL statements and functions, but I am NOT able to create stored procedures using the interface I have.
The challenge I am having is comparing date ranges. I have a table containing two columns labelled START TS TIME and END TS TIME, both of type 'Date'. I have figured out how to query each row against a given Next Session Start and Next Session End and determine if each row overlaps that row.
I need a procedure that will be recursive: that is, set Next Session Start and Next Session End to START TS TIME and END TS TIME of the first row, compare all rows against it, then set Next Session Start and Next Session End to the next row, compare all rows, ... for all rows in the table. I want to know what the maximum number of matches is (i.e. the most time periods that overlap).
If I could use a stored procedure I could complete this query easily. Is there other techniques (i.e. functions) available to leverage in order compare each row of date ranges against ALL rows in the same table?
View 4 Replies
View Related
Aug 16, 2013
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
View 5 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Jul 26, 2007
I have 2 strings that I want to compare for example
string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'
There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?
View 2 Replies
View Related
Sep 23, 2011
I have two databases.
one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......
View 9 Replies
View Related
Mar 5, 2008
How to select the transactions out of the database that occurred within 70 seconds of each other. The toll_date field is a TIMESTAMP field.
Problem is, I seem to only get transactions that occurred within 70 minutes of each other. On the timestamp field I break the math down into the seconds in a day and I add 70. I then subtract that value and add that value to the timestamp and I should get anything between those values right?
SELECT Acct_ID, Ln, Tag_Rd, COUNT(*)
FROM (
SELECT T1.Account_ID Acct_ID, T1.Tag_Read Tag_Rd, T1.Revenue_Date Rev_Date, T1.Toll_Date, T1.Lane_ID, T1.Plaza, T1.Lane Ln, T2.Toll_Date, T2.Plaza, T2.Lane
FROM Toll T1
JOIN Toll T2
[code]......
View 6 Replies
View Related
Nov 5, 2011
Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.
in the below
V_ERR is error count
V_UPS is un processed count
V_PROCESSED_COUNT is total to be processed.
I am expecting PASS result but it is giving FAIL.
DECLARE
V_ERR NUMBER:=0;
V_UPS NUMBER:=0;
V_PROCESSED_COUNT NUMBER:=0;
NIN NUMBER;
BEGIN
V_PROCESSED_COUNT:=1;
[Code] .......
View 1 Replies
View Related
Mar 16, 2010
I want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using
View 8 Replies
View Related
Apr 13, 2011
As part of our project, we need to perform table comparisons in two different databases. I am currently looking for various options to accomplish this.
One of them is doing minus operation between these two tables. Also, i have looked at the data compare option in toad utility.
View 1 Replies
View Related
Aug 3, 2011
I am working in form 6i, database 9i. I have datablock on table t1.
table t1: name(varchar2), date(varchar2)
datablock: name(varchar2), date(varchar2)[i have insert date with time stamp]
for date column, i am inserting date with time stamp.While querying data, user just enters only date(no time stamp), i should be able to query data. I tried in data block where condition
WHERE: substr(date,1,11)=nvl(:block.date,substr(date,1,11)
i am not able to retrieve data through date, through name it's working fine.how to retrieve data by only entering date (without time stamp)
View 13 Replies
View Related
Feb 10, 2013
Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.
Eg:
R(x,y)
Select count(x)
from R
group by x;
Then I want to run another query to determine which x's have the same count value and output these corresponding x's.
View 6 Replies
View Related
Mar 21, 2013
i executed the below code and found that output should be 5 but it shows 7(always else part). it is not comparing string in case.
declare
check_in_date date;
var_day varchar2(11);
rate_id number;
[code]....
View 3 Replies
View Related
Oct 29, 2008
I have a SQL query which joins several large tables (so indexes matter here) from Oracle database. In the where condition I use IS NULL with one of the date field values. Query takes 40 sec to run and if I comment this one line...it takes 1 sec to run. This date field is an index on the table and I learn that --
1. IS NOT NULL in where clause uses an index
2. IS NULL in where clause does not use an index
Is there any work around to make the query faster...other than changing all the NULL date values in the table to some string. In other words can I force it to use the index.
View 16 Replies
View Related
Jul 1, 2013
SF at oracle.com/technetwork/issue-archive/o53plsql-083350.html states that you can compare two database tables (of the same structure) by defining a nested table type (using %ROWTYPE) and two NT variables of that type, and loading the contents of each table into its respective NT variable, before comparing them using the = operator. Having read the Oracle documentation which states that you can only compare NTs for equality if they don't contain record types, I was surprised to read this, but figured I would try it because I must be misunderstanding SF, but it didn't work.
SCOTT@ORCL> create table empcopy3 as select * from emp;
Table created.
declare
type emp_ntt is table of emp%rowtype;
emp_nt1 emp_ntt;
[Code]....
But SF goes on to say he timed the execution of his NT equality method, comparing it with a SQL-only equivalent, and so I must be missing something. My understanding is that using %ROWTYPE declares a record type.
View 3 Replies
View Related
May 27, 2011
I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )
But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.
So on comparing, it fails as the PL/SQL compares it with English Strings.
View 4 Replies
View Related
Oct 16, 2012
i am fetching one record based on date but not able to fetch the data.
in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.
select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.
View 11 Replies
View Related
Aug 18, 2011
I am trying to compare 2 char columns using trim to avoid space padding
CREATE TABLE TRIAL_A
(ABC CHAR(6));
INSERT ALL
[Code]....
View 11 Replies
View Related
Aug 6, 2010
I have got a table having column a, b , c, d, e and some values are stored in it. e.g. a, c and e have 'Y' and b and d have 'N' value in it.
How can i count with one query , how many columns have 'Y' and how many have 'N'.
cursor is one solution.
View 2 Replies
View Related
Jun 29, 2010
I am having some issues in creating the following trigger. It gets created but with compilation errors.
create or replace trigger min_max_update_trg
after insert on tidlrrep
referencing new as n
for each row
declare
bltMaxSize number;
repMaxSize number;
[code].....
when i tried without the comparison if repMaxSize < bltMaxSize then it got created successfully.
View 12 Replies
View Related
Nov 22, 2010
the problem is: 2 tables - one with 2 million records, and the other with 8000 records.
i need to compare for each record in a table if there's a similar string on the other table.
i've created a procedure that does the following:
opens the first cursor (select col1,col2,col3,col4... from table 1)
loop
opens second cursor (select col1 from table 2)
loop
if utl_match(col1, table2.col1) > 80 then
insert col1,col2,col3,col4... into tableX
end if
close second cursor
close first cursor
the thing is that this procedure takes forever to end...about 8 days.
is it because im using the utl_match function? is there a way to speed this up?
View 13 Replies
View Related
Jul 14, 2013
I have master-detail form I would like to update the emp1 table on the base of current value of dept table deptno and emp table ename .
update emp1
set ename = :emp.ename
where ename =
(select ename from emp e
where e.ename=:emp.ename
and e.deptno=:dept.deptno);
I try the above query but it did not able to update emp1 table.
View 12 Replies
View Related
Apr 8, 2012
we have a two different databases at different locations and on different servers, like one in our company with SID='A' and remote database with SID='B', We have recently implemented new module in database 'A' by creating lots of tables, functions, indexes, sequences, synonyms etc and now we need to install this on 'B' but the problem is we have not documented which tables we created, first we need to create a DB link between these two and then we need a tool to compare what are all the tables that we need to create in database 'B' , is there a tool for doing all this.
View 7 Replies
View Related
Jan 20, 2011
I have two schemas with 149 tables in each schema, what I need to do is to prove that the content(data) between the two schemas is identical. I know that all the table names between the two schemas are the same, just need to prove that there is no difference in data.
So the query needs to prove that Schema A content = Schema B content
I know I cant do a simple select from Schema A.tab1 minus select Schema B.tab1 but since there are 159 tables, I am not sure if this is an efficient way of doing it.
View 14 Replies
View Related
Jul 29, 2013
Oracle v11.1 Standard edition. I am trying to use TRIM function around the columns which are used for outer join and it's not working. However, if I use ANSI syntax, it works. Please check the SQL below.Is there any way, I can use old syntax and TRIM and get the outer join to work?I know, I can fix/trim the data in the tables, but for some reason (need to keep the data exactly same as we received it), that is not possible here.
SQL>SQL> desc tabaName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> desc tabbName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> select a.col1, b.col1 from taba a, tabb b where a.col1 = b.col1 ;
no rows selectedSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD
DSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1) ;
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1)
*ERROR at line 1:ORA-00920: invalid relational operator
SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) (+);
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1)
*ERROR at line 1:ORA-00933: SQL command not properly ended
SQL> select a.col1, b.col1 from taba a left outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD DB
SQL> select a.col1, b.col1 from taba a right outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD D E
View 3 Replies
View Related
Mar 16, 2009
I need to compare data of .csv file with the SELECT query result or with any table data.
Is there any possibilities in ORACLE to fulfill this requirement?
View 3 Replies
View Related
Oct 20, 2011
Do we have measure with Oracle sequence width compare with column width.?
today i have ticket to check that.
Quote:Comparing sequence width to column width.We need to be Pro-active and continuous effort (not one time) meaning, any ccput by developer (for new sequence or new column which has corresponding sequence) need to be verified from this perspective
View 3 Replies
View Related
