SQL & PL/SQL :: Detect And Move Duplicate Values
Mar 16, 2010
Table contains duplicate data . Have to move data to another table. Criteria: check for duplicate values if duplicate exist move all duplicates except one to the history table. While moving to other table see if the record being moved already exists.
source table
SOURCE TABLE : ODS_OWNER
grp_id grp_name face_id address1 city zipcode
3456789 NIKE AERO 457899 707 CROFT GRAND RA 12345
1256789 NIKE AERO CORP 678899 707 CROFT SE GRAND RA 12345
5465455 BB SHIPPING 809708 201 SOUTH CT DESPLAINE 45434
[Code]....
FIRST 4 RECORDS ARE DUPLICATES FROM WHICH 1 RECORD GOES TO w_grp AND ONE GOES TO HISTORY TABLE. THE RECORD WHICH GOES INTO w_grp OUT OF THE DUPLICATES WILL DEPEND ON THE LAST MODIFIED DATE FOR EACH
DISTINCT VALUES GO IN w_grp TABLE
DUPLICATE GO INTO match_his TABLE
View 3 Replies
ADVERTISEMENT
Sep 1, 2010
I have table with below values
SELECT * FROM dup_val
1
1
1
1
2
2
2
2
3
The result set should be like
1-1
1-2
1-3
1-4
2-1
2-1
2-2
2-3
3-1
the query get the above result set.
View 10 Replies
View Related
Apr 25, 2011
How to find the duplicate values in two columns.
Suppose we have two columns A and B and the data looks like this
A B
--- ---
1 One
2 Two
1 One
2 Two
3 Three
I need to write a query in such a way that i should find out the duplicate values which are repeating.
View 7 Replies
View Related
Aug 6, 2010
Yesterday (05/08/2010) i have mistakenly inserted duplicate values in the tables, every value is inserted 2 times in a table.
so upto my knowledge the data were correct upto 30/Jul/2010. hence i need to recover the data from this 30th Jul date.
View 4 Replies
View Related
Jun 19, 2012
I have two tables
Table1
Id1 Name1
1 Jack
2 Jack
3 John
Table2
ID2 Name2
NULL Jack
NULL John
I would be assigning ID2 from ID1 based on name match.As Jack has 2 ids when I use the statement
UPDATE TABLE2
set id2 = (select distinct ID1 from table1 where table1.name1=table2.name2);
I get an error message as select statement would return more than one row and the update statement fails completely. with the sql statement to update the ID2 as error when we have duplicate records and continue with the update for other records.
like
table
ID2 Name2
ERROR Jack
View 2 Replies
View Related
May 13, 2012
i have master-detail form.in master my bill_id gets generated when new form is open and i copy the same bill_id in detail(tabular)for each item.all the items which i enter in detail form get save the same bill_id which got generated.
in another form(which is tabular) i want to display bill_id's from detail form.but in detail form . There are same bill_id's more than once.but i want to display those bill_id's which are more than once only once.
View 1 Replies
View Related
Nov 24, 2011
I need to delete the duplicate values from plsql table OR move the distinct values in plsql table to other plsql table.
how can i do this ?
DECLARE
TYPE alist IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
p_tbl alist;
BEGIN
p_tbl(1) := 'A1';
p_tbl(2) := 'B2J';
p_tbl(3) := 'A1';
[code]......
The p_tb1 table contains all the above values including duplicates. Now I need only distinct values to be copied in another plsql table of same type.
View 14 Replies
View Related
Sep 15, 2012
I have below query.. When i run this query i need to get two rows.. But i am getting two more duplicate rows.. I want to restrict these two rows..
How can i do this.. Here the problem is when i join B query then only i am getting duplicate rows..
SELECT DISTINCT B.TIC_ID, B.TIC_ISS_NO,
B.TIC_NUMBEC||CEV_ID,
B.TIC_NUMBEC, B.CEV_ID, B.AOSTED_DATE,
B.COMAANY_CODE, B.CONTCACTOC_NAME,
B.FC_CODE, B.C_NO, B.FC_TYAE,
[code].......
View 4 Replies
View Related
Dec 16, 2009
I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.
Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.
select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)
View 4 Replies
View Related
Mar 22, 2013
is it possible to remove duplicate values in plsql collections without using multistage operators ?
plsql collections output:
ID NAME
----- -------
001 A
001 A
002 B
003 C
004 D
005 E
005 E
005 E
expected output
ID NAME
----- -------
001 A
002 B
003 C
004 D
005 E
View 3 Replies
View Related
Aug 22, 2012
I have a table like this
table:
id name plan code
1 sam normal 5
1 sam normal 6
1 sam special 5
1 sam Special 6
I need to delete data in such a way that one entry with normal and one entry with special plan should remain and should be with different code. Does not matter whether normal stays with 5 or 6 code.
I tried with rowid but it deletes either both normal or both special or returns same code for normal and special.
View 8 Replies
View Related
Sep 10, 2012
CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));
INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad
2, bb
I used:
select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
View 6 Replies
View Related
May 13, 2013
I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -
1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3
2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.
This might explain what I'm trying to do -
grp_col1, grp_col2, grp_col3, test_data_col
1, A, xyz, HELLO
1, A, xyz, HELLO
1, A, xyz, BYE
1, A, xyz, GOODBYE
2, C, pqr, WELCOME
2, C, pqr, GOOD MORNING
2, C, pqr, BAD MORNING
So for condition 1, I do something like this -
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;
In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3,
/*some logic*/ dup_val
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -
cnt, grp_col_1, grp_col2, grp_col3, dup_val
4, 1, A, xyz, Y
3, 2, C, pqr, N
I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
View 2 Replies
View Related
Jul 15, 2013
Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement? Priview SQL statement
SELECT * FROM
(
select id,hin_id,name,code,valid_date_from,valid_date_to
from diaries
)
[Code]....
In this case i got duplicate of entry TT2 id 50513 In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)
View 5 Replies
View Related
Apr 23, 2010
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87
if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.
View 3 Replies
View Related
Dec 4, 2012
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
View 4 Replies
View Related
Dec 8, 2010
I need to define the type of user's web-browser (Opera, Ie, chrome, firefox), under which launched forms.
View 3 Replies
View Related
Apr 29, 2013
how do I detect I/O before running sql statement?
View 3 Replies
View Related
Mar 5, 2010
I have 2 text fields as search criteria and a push button to search. When the user enter any on first field (or in any text field) and press ENTER key, I want to execute the search.
I tried the key-next-item trigger, but it's firing when I press TAB key.
How can I detect ENTER key?
View 8 Replies
View Related
Jul 15, 2011
I found the data in DB with negative time as below
Quote:
1 SELECT
2 c.time
3 FROM partition c
4 WHERE
5* c.time < to_date('0001-01-01','YYYY-MM-DD')
SQL> /
DEACTIVATION_TIME
------------------------------------------------------------------------
17-JUN-08 04.06.22.893 PM
The data type for c.time is Timestamp.
When I use JDBC to retrieve the data and convert it to mill seconds, and it also shows as negative time. My question is the retrieved time doesn't show the negative date, but obvious it is a BC date and earlier than 0001-01-01.
1. How can we detect/show the date to show correctly for the BC date without checking c.time < to_date('0001-01-01','YYYY-MM-DD')? (I am wondering if there is format configuration to show the time like 17-JUN-08 04.06.22.893 PM BC, or something to show the time is negative )
2.how could we fix it negative time to positive ?
View 6 Replies
View Related
Mar 14, 2012
Is there a way to check which versions or oracle installed on the machine from command line?
The thing is I don't want to login to the database so I can't use SQL Plus also, till now I ran tnsping and checked the output but in case there are several versions of different platform I got only one version that was first on the path env.
View 1 Replies
View Related
Aug 20, 2013
Is there a way to detect bogus characters in the datafile?
SQLLoader on original file
Record 1: Rejected - Error on table DP, column STARTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected
Copy the data in the controlfile using notepad++: no errors
View 13 Replies
View Related
Jul 23, 2008
way to run an update statement through OCI and make it stop on the first offending record and then identify that record? Here is an example of what I need to do: I run an update with 5 records in the input buffer like this:
- the 1st record exists in the table
- the 2st record exists in the table
- the 3rd record does not exist in the table
- the 4rd records exists in the table but is violating some unique constraint
- the 5nd record exists in the table
I need to report that the 4th record from the input buffer has a problem, and then commit records 1, 2 and 3. Do not commit record 5. So far I have tried the following:
- OCIStmtExecute with OCI_DEFAULT. It stops on the first error, but I don't know how to identify the bad record. OCI_ATTR_ROW_COUNT returns the number of affected records in the target table. I didn't find any other attributes that would return 4, for the 4th bad record in the input buffer.
- OCIStmtExecute with OCI_BATCH_ERROR. I can identify the bad records in the input buffer by using OCI_ATTR_DML_ROW_OFFSET. However, OCIStmt Execute does NOT stop on the first violating record. It loads the whole input buffer and then reports which records were in error. I don't know how to commit only up to the first bad record -- the 4th in the example.
View 6 Replies
View Related
Nov 11, 2012
Is it possible to detect the status of Hide / Show Region.
I have three hide/show regions RX,RY,RZ. When an event EX happens, then region RX should expand and the rest of the region should collapse. When an event EY happens, then region RY should expand and the rest of the region should collapse.
........
...
We can use this JS on DA to change the status of a region:
$("RX.uRegionControl").click();
$("RY.uRegionControl").click();
.....But, first we need to detect the status to know where to apply that DA. ? I am working on apex 4.2. theme 25.
View 2 Replies
View Related
Mar 9, 2012
i want to move from 11g Standard to 11g Enterprise (same Version) on Windows.
View 2 Replies
View Related
Feb 11, 2011
Aim was to shrink datafile and got "ORA-03297: file contains used data beyond requested RESIZE value". Datafile size 7GB but used 128MB.
So created new Tablespace and moved all tables and index partitions to new TS. Now I see some entries in USER_INDEXES, which I need to move to new TS. But they are IOT's.
INDEX_NAMEINDEX_TYPETABLE_OWNERTABLE_NAME
SYS_IOT_TOP_1082946IOT - TOPDSI_DPIPESYS_IOT_TRNS_1082943
SYS_IOT_TOP_1082949IOT - TOPDSI_DPIPESYS_IOT_TRNS_162982
how to move these indexes to new TS.
View 28 Replies
View Related
Apr 23, 2012
We want to provide data dump from 3 tables Like A,B,C.
In C we have more than 3 million records.
What is the best way to move this data from our DB to customer DB.
Customer is running on MySql
View 12 Replies
View Related
Oct 8, 2012
create or replace function kardex_ob (f_item_code varchar2,f_date date) return number is
f_ob_quantity kardex.cb_quantity%type;
cursor cur_kardex is
select cb_quantity
[code]...
If the above function is called it shows the first Record in the Table "kardex" for example:
if we run this query which is taken in the above function;
select cb_quantity
from kardex
where item_code = f_item_code and
document_date < f_date
[code]...
so here if i call the function(Kardex_ob) it shows the first Record "8500",well if i wish to show the next record 5000, thenhow to get it through the above Coded function?
View 6 Replies
View Related
May 18, 2012
I want to move partitions and subpartitions to different tablespace but I am getting error when I am trying to move PARTITION tablespace after on move of subpartition tablespace.
CREATE TABLE IN_TEST
(
LINE_SKU_SEQ NUMBER NOT NULL,
REGION_CODE VARCHAR2(10 CHAR) NOT NULL
)
TABLESPACE USER02
PARTITION BY RANGE (REGION_CODE)
SUBPARTITION BY HASH (LINE_SKU_SEQ)
(
PARTITION REGION_AMERICAS VALUES LESS THAN ('AMER_')
STORAGE (
BUFFER_POOL DEFAULT
[code]....
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition
View 1 Replies
View Related
Oct 1, 2010
i have a form and i have just one field on it and it's item_type is "List Item" and list style is "Poplist"...
my requirement is , when i press tab then i want that my cursor move in next row.
View 3 Replies
View Related
