I need to compare records in the two columns (varchar). I need to find almost the same names from that columns.
For instance:
'MAPE STUD' <-> MAPE STUDY
And I should see that these records are the same because different is only in the one word.
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.
Col1Col2
1106011060
1106011640
1106011142
1106011095
1106013029
1106014058
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)???.
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..
View 17 Replies View RelatedI 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';
My tables looks like this:
Desc Table A (account)
Account1_id
Account2_id
name,
empid
Table B (Bill )
BillNo
Advertiserid
agencyid
total vvalue
I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.
In other words my output should be like
Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....
objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .
I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:
select.......from a,b
where b.advertiser_id = a.account1_id and b.agencyid = b.account2id
Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.
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 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).
source data
date1 iddate2 new_date
1/2/20111234/2/2011
4/2/20111237/2/2011
7/2/201112310/2/2011
10/2/20111231/2/2012
1/2/20121234/2/2012
4/2/20121237/2/2012
12/17/20121233/17/2013
3/17/20131236/17/2013
and I am expecting the out put like this
date1 id date2 new_date
1/2/20111234/2/20111/2/2011
4/2/20111237/2/20111/2/2011
7/2/201112310/2/20111/2/2011
10/2/20111231/2/20121/2/2011
1/2/20121234/2/20121/2/2011
4/2/20121237/2/20121/2/2011
12/17/20121233/17/201312/17/2012
3/17/20131236/17/201312/17/2012
how to achieve this with SQL
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?
the following proble.The emp table is having 14 records.
SELECT * FROM emp ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
[code]...
The emp table is having 10 records.
SELECT * FROM emp_10 ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020
[code]...
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 want to learn how to make columns out of records?
View 8 Replies View RelatedI am having a requirement like below,
Scene 1:
If duplicate records found for SSN,BWE with SAME DATE_CREATED than take the record with HIGHEST DATE_MODIFIED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 02-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
Scene 2:
If duplicate records found for SSN,BWE with different DATE_CREATED than take the record with HIGHEST DATE_CREATED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
How to achieve this requirement. My source data has 25 million of records like this.
I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed
The following is the example
WITH t1 as
(select 159435 ky from dual)
,t3 as
(select 78 id ,'Z-' rk,'SL' cd from dual union all
select 78 id ,'Z+' rk,'SL' cd from dual union all
select 78 id ,'Z-' rk,'SL' cd from dual union all
[code].....
In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.
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.
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies View Relatedcreate table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...
Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like
CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies View RelatedI am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
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]....
how to compare a value in sqlldr control file.
I want to compare two tables column by column and find out which columns are matching and inserting matching columns value into another table.
View 1 Replies View RelatedI Have Four Tables
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
Do we have any oracle built ins to compare ?
Checkout the following code
CREATE TABLE CHK(dt_request_datetime DATE);
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.
I want to write a function that gets:
1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)
I got a table called EVENTS that got 2 fields:
1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)
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]....
[URL]....
[URL].....
i want to compare this URL and convert them into 1
i.e any url containing rediff word i want to convert that URL into
[URL]......
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;
View 3 Replies View RelatedI have one command compare varchar type with date type by Pl/SQL.
date_entry gave values:" 2012/01/06 22:28:24",
now i want to convert it to date and compare with sysdate.
Select * From qvsystem
Where to_char(to_date(date_entry, 'rrrr-mm-dd hh24:mi:ss'), 'dd-mm-rrrr') = to_char(Sysdate, 'dd-mm-rrrr');
----
note:date_en2012/01/06 22:28:24
after I executed that command It's appear one message .
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.
View 15 Replies View Related