SQL & PL/SQL :: Joining But Avoiding Duplicate Rows

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


ADVERTISEMENT

SQL & PL/SQL :: Concatenating Two Rows While Joining

Sep 21, 2011

I have two tables

Tab1 columns c1,c2,c3 data as
t1r1c1,t1r1c2,t1r1c3
t1r2c1,t1r2c2,t1r2c3
t1r3c1,t1r3c2,t1r3c3

Tab2 columns c1,c2,c4 data as
t1r1c1,t1r1c2,t2r1c3

i want to get results like below
t1r1c1,t1r1c2,t1r1c3_t1r2c3_t1r3c3

join columns will be c1 and c2 columns.

View 2 Replies View Related

SQL & PL/SQL :: Joining Subsequent Rows In A Table

Mar 29, 2010

I create a second "DATE_PAIRS" table, and join a few subqueries. It works with conditions (ID=1), but scanning the whole table (2+ million records) is abysmal.

Table:
ID Date Price
---------------------
1 2010/1/1 100
1 2010/1/2 125
1 2010/1/5 115
2 2010/1/1 115
2 2010/1/2 105
2 2010/1/5 125

I'd like to return

ID Date1 Date2 Price1 Price2
--------------------------------
1 2010/1/1 2010/1/2 100 125
1 2010/1/2 2010/1/5 125 115

etc ..

View 11 Replies View Related

SQL & PL/SQL :: Joining One Row To Multiple Rows In Same Table?

Feb 8, 2011

I have two tables. The first contains all Segment information within Oracle i.e;

Table 1
Segment Description Flex_Value
1 North 1234
1 South 1235
1 East 1236
2 Car C001
2 Boat B001

and the second table contains financial data, but only the segment code;

Table 2
Date Segment1 Segment2 Value
01/01/11 1234 C001 10,000
02/01/11 1235 C001 10,000

what I want to return is some of the columns within table two as well as additional columns for the segment descriptions.

Below is an extract of what I have put together, and it does return what I need, but I'm sure there is a much more efficient way of creating the query;

SELECT

b.DATE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,

[code].....

View 6 Replies View Related

Eliminate Duplicate Rows

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

Delete Duplicate Rows?

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

SQL & PL/SQL :: Update Duplicate Rows To 0?

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

PL/SQL :: Duplicate Rows Removal

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

SQL & PL/SQL :: How To Remove Duplicate Rows From Table

May 8, 2013

how to remove duplicate rows from table?

View 6 Replies View Related

SQL & PL/SQL :: How To Retrieve Duplicate Rows From A Table

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

SQL & PL/SQL :: Calculating Sum From Duplicate Rows With Timestamp?

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

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 View Related

PL/SQL :: Running Total For Duplicate Rows

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

PL/SQL :: Procedure To Update Duplicate Rows?

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

SQL & PL/SQL :: To Remove Duplicate Rows From Output

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

View - Join Displays Duplicate Rows?

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

Duplicate Sequence Number For Identical Rows

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

SQL & PL/SQL :: Delete The Duplicate Rows In A Table Without Using ROWID?

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

SQL & PL/SQL :: How To Avoid Duplicate Rows From Being Inserted In The Table

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

SQL & PL/SQL :: Removing Duplicate Rows When Condition Is Matched

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

Application Express :: What Would Cause Oracle To Insert Duplicate Rows Into A Table

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

SQL & PL/SQL :: Avoiding Space

Jan 5, 2012

I have table like below.

Name Gender
----- ------
f1 Female
f2 Female
m1 Male
m2 Male
f3 Female
m3 Male
m4 Male

but I need the output like below

Male Female
----- ------
m1 f1
m2 f2
m3 f3
m4

I have tried to get the above O/p but getting along with null values.

SQL> ed
Wrote file afiedt.buf

1 select case when gender='male' then name end male,
2 case when gender='female' then name end female
3* from details s1
4 /

MALE FEMALE
--------------- ---------------
f1
f2
m1
m2
f3
m3
m4

7 rows selected.

how the get the above o/p with out null values.

View 9 Replies View Related

SQL & PL/SQL :: Avoiding Insert Into Table

Nov 21, 2010

I've got situation where one of the procedure causing insert into table rows but some of them are not needed Temporary I need to disable this and exclude some type of data being inserted by changing procedure.

I wouldn't want to change the procedure is there a way to write a trigger that would not allow to insert a row into a table if condition is met? Something of a logic:

CREATE OR REPLACE TRIGGER avoid_insert
BEFORE (AFTER?) INSERT
if entity_id = 123 DO NOT DO THE INSERT (DO NOT DO ANYTHING)

View 6 Replies View Related

PL/SQL :: SQL To Sum A Column While Avoiding Repeating Values

Jan 31, 2013

I have these records in a table:

CODE AMOUNT DESCRIPTION
AAA 5 five dollars for pizza
AAA 2 two dollars for tips
AAA 1 one dollar for dogsitting
BBB 6 six dollars for babysitting
BBB 1 one dollar for tips

My goal is to list all records, "grouping" by code, with sum(amount), but the final display has to show all descriptions, one for row, avoiding to repeat the "CODE" column and "sum(AMOUNT)" column.The result should be like this:

CODE SUM(AMOUNT) DESCRIPTION
AAA 8 five dollars for pizza
two dollars for tips
one dollar for dogsitting
BBB 7 six dollars for babysitting
one dollar for tips

That is, the "CODE" is displayed only the first row, with its sum of "amount".I think I have to use the analytics functions, but I was a little stuck.

View 4 Replies View Related

SQL & PL/SQL :: Avoiding Repetition Of Condition In Not Exist Clause?

Dec 7, 2010

Given two tables in a 1:N relationship, I want to retrieve, for each parent row, any child row which either satisfies some condition cond1 or, if no child rows matching cond1 exist, which satisfies another condition cond2.

I can easily write such a query using NOT EXISTS, but I am forced to repeat the condition cond1. This is inconvenient, as the repeated condition is quite long (involving other tables as well), and I would like to write it only once.

For example (using the HR schema included in Oracle 10g XE): I want to retrieve, for each department, all the "new" employees (hired after a certain date) or, if no "new" employees exist, the department manager.

SELECT d.department_id,
d.department_name,
d.manager_id,
e.employee_id,
e.first_name,
e.last_name,

[code].....

View 3 Replies View Related

Performance Tuning :: Avoiding Bind Variable From Sql In Java Code?

Jun 15, 2010

We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?

As example lets say the query is:

SQL> variable vn varchar2(20);
SQL> EXEC :vn :='ADAMS';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

1 row selected.

SQL> EXEC :vn :='KING';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

1 row selected.

But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?

View 9 Replies View Related

Joining Two Tables

May 19, 2011

can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?

View 1 Replies View Related

SQL & PL/SQL :: Joining Two Tables

Apr 26, 2013

I have a table (promo_custom) with following colimns (id, id_promo, button_promo, button_submit, css, user, date) and other table (promo_buttons) with following columns (id, id_design, name, url, username, date) .

Columns promo_custom.button_promo and promo_custom.button_submit are foreign keys (references promo_buttons.id).

I want to create a view showing table promo_custom but instead of columns button_promo and button_submit I would like to show the column url of them.

View 11 Replies View Related

PL/SQL :: Joining The Same Table Twice

Oct 14, 2013

Shouldn't they produce the same output? 

SELECT c1.c_no, c1.c_name, c1.city
FROM customers c1 LEFT OUTER JOIN customers c2 ON (c1.city = c2.city AND c1.c_name<>c2.c_name); SELECT c1.c_no, c1.c_name, c1.city
FROM customers c1 RIGHT OUTER JOIN customers c2 ON (c1.city = c2.city AND c1.c_name<>c2.c_name);

View 8 Replies View Related

Joining Two Columns In Same Table?

Jan 30, 2007

We have a table for reports. If user A submits a report ...and say the sequence # is 242. When this report goes to Admin ...he submits this request then in same table we add another row with say sequence # 245. THEN we update column called 'Asctd ID' for 242 and add 245 in there. and then update Asctd ID for 245 and add 242 in there.

(This table has many fields, one of which is report Name field)

Now i am running a query like this...

SELECT b.JOB_ID, a.DESC, TO_CHAR(a.CREATE_DATE,'MM/DD/YYYY'),
DECODE(a.DLVRY_TYPE,'','PDF',a.DLVRY_TYPE), DECODE(a.FLG,'','N/A',
a.FLG), ((TO_DATE(a.CREATE_DATE,'DD-MON-YY')) + 21) -
TO_DATE(SYSDATE, 'DD-MON-YY'), c.STATUS_DESC, a.SIZE_NUM,
b.PRVDR_ID, a.asctd_id, d.NM
FROM REQUEST_DIM a, PROVIDER_DIM b, STATUS_DIM c, DIM d
WHERE a.FLAG = 'P' and RTRIM(a.RPT_RQST_USER_ID) = 'TEST02' AND a.RPT_RQST_TYPE = 'D'
AND a.RPT_RQST_ACTIVE_IND = 'A' and a.asctd_id is not null and a.RPT_RQST_ID = b.RPT_RQST_ID
and b.JOB_ID = c.JOB_ID and b.PRVDR_ID = d.PRVDR_ID
AND c.CREATE_DT = (SELECT MAX(d.CREATE_DT)
FROM STATUS_DIM d
WHERE d.RPT_RQST_ID = b.RPT_RQST_ID and d.JOB_ID = b.JOB_ID )
ORDER BY a.RPT_RQST_ID, a.CREATE_DATE

Now this query is run by the admin (job 245) ...it returns a bunch of stuff and also the name of the report that the admin gave this. But when admin sees this we want to be able to displace the report name that user A gave it (asctd ID 242). so the row 245 HAS a asctd ID 242. and there is a row 242 from which we can get the name easily. But i dont know if this is possible in ONE QUERY??

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved