SQL & PL/SQL :: Duplicate Rows Displayed For A Column
Mar 30, 2013
column sid format 'a5'
column serial# format 'a10'
column mins_running format 'a15'
column sql_text format 'a100'
set linesize 200
set pagesize 30
[Code]..
I am running this code, and the output shows multiple lines.
TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70)
---------------------------------------- ---------------------------------------- --------------- ----------------------------------------------------------------
700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else
700 46592 242.08 'PASS' end as result
from (SELECT cv.code_value
FROM code_valu
[Code]...
Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?
View 14 Replies
ADVERTISEMENT
Jan 27, 2009
I have to eliminate duplicate pairs from the following data set.
for ex. Adney Vaughan and Berkly Wassen appears in both AG1 and AG2. how can i get rid of these repititive rows?
AG1 ----------- AG2
Acton Wibert ---- Currier Barhydt
Adney Vaughan --- Luella Edmund
Adney Vaughan --- Berkly Wassen
Alden Anstruther --- Courtney Gavet
Ashley Alvord --- Saunders Buel
Aswin Wilbraham --- Dale Cooper
Barnum Sears --- Grayson Lightfoot
Berkly Wassen --- Luella Edmund
Berkly Wassen --- Adney Vaughan
Bersh Musgrave --- Derward Knight
Berthilda Darrell --- Broderick Reynold
Broderick Reynold --- Berthilda Darrell
View 1 Replies
View Related
Feb 6, 2011
I having a problem with duplicate rows with a query.
What I have so far is
SELECT D.Student_Id,
E.Latest_Reg_Date,
E.Attendance_Count,
[Code].....
View 1 Replies
View Related
Oct 25, 2011
getting sql query to get the result below.If the Key repeats then I need to set it to O if for each key New_link doesnot match.
My Present table
Key New_Link
1 4
3 2
3 5
5 1
5 1
RESULT
Key New_Link
1 4
3 0
5 1
View 5 Replies
View Related
Jul 3, 2012
removing duplicate rows from a table.
We have the following table:
SSD@ermd> desc person_pos_history
Name Null? Type
------------------------------------------------------------------------ -------- ------------------------
PERSON_POSITION_HISTORY_ID NOT NULL NUMBER(10)
POSITION_TYPE_ID NOT NULL NUMBER(10)
PERSON_ID NOT NULL NUMBER(10)
EVENT_ID NOT NULL NUMBER(10)
USER_INFO_ID NUMBER(10)
TIMESTAMP NOT NULL DATE
We found out that few person_id's are repeating for a particular event (3):
select PERSON_ID, count(*)
from person_pos_history
group by PERSON_ID, EVENT_ID
having event_id=3
and count(*) > 1
order by 2
PERSON_ID COUNT(*)
---------- ----------
217045 356
216993 356
226198 356
217248 364
[Code]...
If we look at the 1st person id "217045", we can see that it is repeating 356 times for event id 3.
SSD@ermd> select POSITION_ASSIGNMENT_HISTORY_ID, POSITION_TYPE_ID, PERSON_ID,EVENT_ID, to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS')
2 from person_pos_history
3 where EVENT_ID=3
4 and person_id=217045
5 order by timestamp;
PERSON_POSITION_HISTORY_ID POSITION_TYPE_ID PERSON_ID EVENT_ID TO_CHAR(TIMESTAMP,'
------------------------------ ---------------- ---------- ---------- -------------------
222775 38 217045 03 2012-05-07 10:29:49
222774 18 217045 03 2012-05-07 10:29:49
222773 8 217045 03 2012-05-07 10:29:49
[Code]...
356 rows selected.It is safe to assume that the person id/event id with the earliest timestamp is the one that was loaded 1st, hence, the one we want to keep and the rest should be deleted.
sql to achieve the duplicate removal.
View 6 Replies
View Related
May 8, 2013
how to remove duplicate rows from table?
View 6 Replies
View Related
Jun 14, 2010
How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.
View 5 Replies
View Related
Oct 16, 2010
I have two tables A and B
CREATE TABLE A(EMP_ID NUMBER, EMP_NAME VARCHAR2(100))
CREATE TABLE B(EMP_ID NUMBER, EMP_ATT1 VARCHAR2(10), EMP_ATT2 VARCHAR2(10))
INSERT INTO A VALUES(1, 'ONE');
INSERT INTO A VALUES(2, 'TWO');
INSERT INTO A VALUES(3, 'THREE');
[Code]....
This query returns all the matching row of A and B
SELECT A.EMP_ID, A.EMP_NAME, B.EMP_ATT1, B.EMP_ATT2
FROM A
INNER JOIN B ON A.EMP_ID=B.EMP_ID
The output for this shows:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
2 TWO 2ATT1.1 2ATT2.1
3 THREE 3ATT1 3ATT2
The requirement is to avoid duplicate rows even if matched:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
3 THREE 3ATT1 3ATT2
View 8 Replies
View Related
Sep 16, 2011
I am trying to find sum for one record for each partition but while taking that timestamp giving me bit trouble, i have tried to reproduce the table and some little data
CREATE TABLE TEST_COUNT
(END_TIME DATE
,SUCCESSFUL_ROWS NUMBER
,FAILED_ROWS NUMBER
,TBL_NAME VARCHAR (4)
,PARTITION_NAME VARCHAR (240) )
[code]....
View 11 Replies
View Related
Dec 19, 2012
I am trying to write an sql which shows the running total for records which has duplicate.
sample rows:
col1 col2 col3
1 A 2
1 A 2
1 A 2
1 B 3
1 B 3
1 C 5
1 D 2
1 D 2o
p required:
col1 col2 col3 cumulative_tot
1 A 2 2
1 A 2 2
1 A 2 2
1 B 3 5
1 B 3 5
1 C 5 10
1 D 2 12
1 D 2 12
View 3 Replies
View Related
Mar 14, 2013
In a table i have some duplicate rows
I can get it through this query : select PARTY_ID from XXWFS_CUSTOMER_EXT group by PARTY_ID having count (PARTY_ID) > 1;
Now for the records which i got for each duplicate row i want to update the second row with a specific value.. so that duplicate rows does not exist anymore
Ex: I got party id's 12, 14, 16, 18 two times each
Now as 12 is two times.. i want to update the second row of 12 with some x value same is the case for other values like 14,16, etc
how can i write a procedure for this
View 3 Replies
View Related
Jun 14, 2011
I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.
my requirement in view like
And..........
And ((select count(*) from table A where conditions)=1 )then name is null
AND
in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.
View 4 Replies
View Related
Apr 16, 2012
The view below creates, however displays duplicate rows. Why is this may I ask?
CREATE OR REPLACE VIEW customer_order_vw
AS
SELECT
a.customer_id,
[Code]....
View 1 Replies
View Related
Jul 5, 2010
I have a table:
Name
_____
Smith Street
Smith Street
John Street
Ed Street
Ed Street
Ed Street
and need to assign sequence numbers only when the record (Name) changes, e.g. :
Name Seq
_____ ____
Smith Street 1
Smith Street 1
John Street 2
Ed Street 3
Ed Street 3
Ed Street 3
I have experimented with row_number partition but then i just get the sequence returning to 1 when the name value changes.
If I grouped the records by Name I would like to have unique, sequential numbers: 1, 2, 3 but where there is the same name I would like the sequence to stop and the number to replicate?
View 9 Replies
View Related
Mar 1, 2010
I want to delete the duplicate rows in a table without using ROWID.
I have the following data.
SNO SNAME SECTION
1 RAM A
2 SHYAM B
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
5 RAJESH F
The Output Should be like this.
SNO SNAME SECTION
1 RAM A
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
View 8 Replies
View Related
Dec 21, 2009
I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table
transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)
I want to write SQL which should not inset duplicate record.
e.g.
I have written one as bellow:
insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;
Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)
Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.
View 21 Replies
View Related
Mar 17, 2010
My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.
create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...
I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.
select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....
View 20 Replies
View Related
May 2, 2013
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.
View 9 Replies
View Related
Feb 6, 2012
below are the create and insert statement, making duplicate row, i mean In table emp_detail, we have row like
ENAMEJOBDEPTNODETAIL
RAJ CS 10 RAJ IS IN CARE OF ROHIT
We need to insert the same record but divide the last column value on the basis of "IN CARE OF" any word after this should come in next new row with the same value for all other column, like
ENAMEJOBDEPTNODETAIL
RAJCS10RAJ IS IN CARE OF
RAJCS10ROHIT
CREATE TABLE "EMP_DETAIL"
("ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"DEPTNO" NUMBER(2,0),
"DETAIL" VARCHAR2(100)
[code]...
View 3 Replies
View Related
Sep 23, 2010
I have a table with multiple columns and I need to retrieve the ones where column a stores duplications for records where column b is discrepant
this is the table
NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12345 +41 22 595 5555
12342 +41 22 595 5500
12340 +41 22 595 5555
the query should return
NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12340 +41 22 595 5555
The closest I got was with the below, but this also returns duplicates within the same NAME_ID.
select phone_number, name_id
from name_phone
where (phone_number) in
(select phone_number
from name_phone
group by phone_number
having count(*) > 1)
group by phone_number, name_id
order by phone_number
View 3 Replies
View Related
Dec 16, 2010
I am trying to write SQL which finds records which are duplicated in more than one column.
Requirement : When ever i have duplicates in Col2 and Col3 both i need that record...
My Source table:
COL1COL2COL3COL4
163kg87
263fh87
1ab23
2ab24
3cd98
4fg87
5xy77
6xy67
Desired Output
COL1COL2COL3COL4
1ab23
2ab24
5xy77
6xy67
For Table generation
CREATE TABLE TEMP_TEST
(
COL1 NUMBER,
COL2 VARCHAR2(10 BYTE),
COL3 VARCHAR2(10 BYTE),
COL4 NUMBER
)
Insert statements
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
(163, 'k', 'g', 87);
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
[code]....
View 4 Replies
View Related
Mar 8, 2010
I want to avoid duplication on old_nic_no and new_nic_no number in a Table, for this i am using a following trigger
create or replace TRIGGER chk_ip_duplications
BEFORE UPDATE
ON cb_insured_person_bak
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
[Code]...
when i use following command
update cb_insured_person_bak set new_nic_no = '34601-0774284-9' where eobi_no = '4300G043746'
34601-0774284-9 already exist with another eobi_no , i expect error described in rise_application_error clause but i give following error
ORA-04091: Table cb_insured_person_bak is mutating..........
How i can resolve this problem.....
View 15 Replies
View Related
Mar 18, 2010
I am working on a assignment, How to prevent duplication with Trigger. I create following compound trigger for this, but it is not doing so.
CREATE OR REPLACE TRIGGER CHK_DUPLICATION FOR INSERT ON TST COMPOUND TRIGGER
R_BCOUNT NUMBER;
R_ACOUNT NUMBER;
D_VAL NUMBER;
BEFORE STATEMENT IS
BEGIN
[code].....
View 12 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
Mar 28, 2013
I have a form which utilizes 2 canvases that the user can toggle between. There is a database column that I would like to have appear on both and be updatable from either place. In my search here first, I found where I could set up a non-database item and copy to it at the point of Post-Query...and that comes close, but I need both columns to not just reflect the db column but be able to update it.
I am about to try using a second trigger to move things from a non-DB column to a DB-column next, but just wondered if there is a better way. When I first compiled with the designer the duplicate column I set up as a DB column also. It only gave me warnings (that I could have lived with) but the ultimate compile my system does outside of the designer calls it an error.
View 7 Replies
View Related
Mar 23, 2013
I'm getting an error as follows .
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
where
a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
and a.SRC_SYS_DESC = 'FACET');
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
*
ERROR at line 1:
ORA-00957: duplicate column name
View 1 Replies
View Related
Mar 27, 2013
I have 4 reports on the same page. I have added a checkbox column to each one. On clicking I want to populate a collection with the value of the id of the corresponding row and then have a button acting on the whole list.
My code to get the id of the item that contains the value I want is this
id = 'f02_' + $(this.triggeringElement ).attr('id').substr(4,4);
But there is an f02_0001 for each report, and the code just finds the first one, rather than the one in the context of the report being clicked.
<input name="f02" id="f02_0001" type="hidden" value="2072"/>
....
<input name="f02" id="f02_0001" type="hidden" value="2052"/>
...
etc etc.
All that is ever picked up is 2072
They have different table ids but I don't know how to reference the correct ones in my code.
View 3 Replies
View Related
Oct 25, 2013
I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.
for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,
[Code].....
View 6 Replies
View Related
Mar 18, 2011
I have following table.
CREATE TABLE ORAFAQ
(
DPT NUMBER,
EMP NUMBER,
SAL NUMBER
)
[Code]....
DPTEMPSAL
1110
12100
131000
241500
25100
26500
27100
but i need data like
Dept
1
1 2 3 --3 columns show each employee
10 100 1000 --3 columns show salary below each employee
2
4 5 6 7
1500 100 500 100
View 1 Replies
View Related
Jul 14, 2012
I have two tables as follows:
TABLE_1
ID ENTRY_DATE VALUE
------- --------------- ----------
1 1-JUN-12 21
1 2-JUN-12 51
1 3-JUN-12 232
1 4-JUN-12 221
1 5-JUN-12 424
.
.
.
.
1 26-JUN-12 52
1 27-JUN-12 0
1 28-JUN-12 247
1 29-JUN-12 528
1 30-JUN-12 489
2 1-JUN-12
2 2-JUN-12
2 3-JUN-12
2 4-JUN-12
2 5-JUN-12
.
.
.
.
2 26-JUN-12
2 27-JUN-12
2 28-JUN-12
2 29-JUN-12
2 30-JUN-12
TABLE_2
ID DAY_1 DAY_2 DAY_3 DAY_4 DATE_5 .......... DAY_26 DAY_27 DAY_28 DAY_29 DAY_30
----- ---------- --------- --------- ---------- ---------- --------- --------- -------- -------- --------
1 21 51 232 221 424 52 0 247 528 489
2
There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.
View 1 Replies
View Related
