PL/SQL :: Check For Any Duplicate Values In COUNT() Column

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


ADVERTISEMENT

SQL & PL/SQL :: Check If Multiple Column Values Are Same?

Sep 16, 2011

I have a table as follows

create table teststr (indname varchar2(20),
counter1 number,counter2 number,counter3 number,counter4 number);
insert into teststr values('a',10,20,30,30);
insert into teststr values('b',10,20,5,3);
insert into teststr values('c',2,4,5,2);
insert into teststr values('d',1,2,3,4);
insert into teststr values('e',4,5,4,4);

Now i need the output if any of the column values are same.

output should be

select indname from teststr where counter1=counter2
or counter1=counter3 or counter1=counter4
or counter2=counter3 or counter2=counter4
or counter3=counter4

a
c
e

Is ther any other way to write the query instead of the numerous or conditions if i want to compare the column values in a table.

View 6 Replies View Related

SQL & PL/SQL :: Count Values From TOTAL_fault Column From Fact Table

Nov 22, 2012

I have two tables with below structures.

desc Fault_Dim
Name Null Type
------------------- -------- ------------------
KEY NOT NULL NUMBER(38)
SYSTEM_NAME VARCHAR2(3 CHAR)
PRIORITY VARCHAR2(40 CHAR)
SEVERITY VARCHAR2(40 CHAR)
TYPE VARCHAR2(40 CHAR)
STATUS VARCHAR2(40 CHAR)
CREATE_DT DATE
SUBTYPE VARCHAR2(255 CHAR)
[code]...

The Severity column has values as below 1,2,3, My requirement is to show results as in the attatched image.Where the count values are values from TOTAL_fault column from the fact table.

I believe this is possible in hierarchial queries and pivorting

View 1 Replies View Related

PL/SQL :: How To Count Duplicate Records In A Table

Jun 15, 2012

query to count the duplicate records and to display duplicate records in a table. Iam using oracle 10g.

View 2 Replies View Related

Forms :: Duplicate Check On Data Block

Jan 19, 2010

I wonder if there is some tiny technique to trace a duplicity on a block label without committing the records ( Maybe on Validate or new record instance)

I have one procedure to check duplicate but what I remember that there is something very smart provided from oracle to do that.

View 7 Replies View Related

SQL & PL/SQL :: How To Check And Delete Duplicate Data From Table

Nov 19, 2010

how i can chk & delete duplicate rows from a table

View 3 Replies View Related

Performance Tuning :: Check Count For Each Partition?

May 2, 2012

We have 525 partitions and we want find out partition wise total count. finding partitionwise count in a particular table.

View 12 Replies View Related

SQL & PL/SQL :: Get Duplicate Values Using Delimiter

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

SQL & PL/SQL :: Duplicate Values In Two Columns?

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

SQL & PL/SQL :: Duplicate Values Removal

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

PL/SQL :: Duplicate Values - Getting Error?

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

Forms :: Display Duplicate Values Just Once

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

SQL & PL/SQL :: How To Eliminate Duplicate Values From Table

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

SQL & PL/SQL :: How To Restrict Duplicate Values From Query

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

Return Minimum Duplicate Values

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

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

PL/SQL :: Remove Duplicate Values In Collections?

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

PL/SQL :: Delete Duplicate Values From A Table?

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

SQL & PL/SQL :: Check For Alphanumeric Values

Sep 8, 2011

i need a function which checks if v_rand carrying a value is alphanumeric if nt this value of 6 alpha numeric characters must be generated again... here is the actual fn.

Function alphanumeric
Return varchar2
is
v_rand varchar2(10) := 0;
Begin
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Get Single Row From Multiple Duplicate Values Of One Field?

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

PL/SQL :: Exclude Duplicate Values On SQL Where Clause Statement?

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

SQL & PL/SQL :: How To Use Or Condition To Check If 6 Col Values In A Row Is NULL

Aug 23, 2010

How can I use OR condition in sql to check whether if the 6 col values in a row is NULL, I need something like below, but idk the syntax of checking if either of the mentioned cols is null using an or condition.

select a.mid
FROM table_a a
JOIN table_b b
ON a.dept = b.dept
JOIN table_c c
ON b.eid = c.eid
WHERE a.tid = :tid
AND (a.i1 or a.i2 or a.i3 or a.n1 or a.n2 or a.n3 or a.t1 or a.t2 or a.t3 ) IS NULL

View 1 Replies View Related

SQL & PL/SQL :: Count Function Over NULL Values

Jun 17, 2010

There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.

But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.

View 3 Replies View Related

Forms :: Passing Check Box Values To WHERE Clause?

Oct 26, 2013

I have created a Data block - 'CONTACTS' (Database data block) and has database item - 'Code', 'Descr'

The number of records displayed is set to 5.

Value When checked - 'Y'
Value When Unchecked - 'N'
Check box mapping of other values - 'unchecked'

The requirement is when i check one or multiple checkboxes, i should pass the 'Code' item values to the WHERE clause.

Right now whenver i am trying to do so, only the current record value is copied to the WHERE clause.

I have tried using basic loop as well as while loop but things havmt worked. Below is a basic code which will work for one record, request to guide me with muliple checkbox ticked.

IF :contacts.cb = 'Y' THEN

IF p_where is null then

p_where := :contacts.code;
else
p_where := p_where ||','||:contacts.code;
end if;
end if;
p_where:= 'where code in ('||p_where||')';

View 6 Replies View Related

Forms :: 10g - Debug And Check Values Of Defined Variables

Sep 22, 2010

I am currently working in form 10g and i m newbie to that technology...I want to debug my form and also check step step by values of variables which i defined.

View 1 Replies View Related

Forms :: How To Select 1st Record From The Duplicate Values In Table Without Using Rownum And Rowid

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

PL/SQL :: Remove Duplicate Values From Concatenated Long String Of State Codes

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

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

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

SQL & PL/SQL :: How To Get Count Of Null Values In Left Outer Join

Oct 16, 2012

I am using left outer join to fetch PRSN_KEY .I need to find null values in B.PRSN_KEY. I am using below query but its giving me 0 count.

select count(*) from (
Select A.PRSN_KEY AS AKEY,B.PRSN_KEY AS BKEY from CD03955P.H_CM_EEST_EEOR A LEFT JOIN CD03955P.H_CM_EEST_EEOR B
ON
A.PRSN_KEY =B.PRSN_KEY
where
A.CAT_ID=111
AND
A.DATA_SOURCE='PEN_CO'
AND
B.CAT_ID = 1 and B.DATA_SOURCE ='PEN_EEST'
AND B.CAT_CD IN ('ACTIVE','LOA','LOAWP','LOAMLP','LOAMLN')
AND B.EFBEGDT < A.EFBEGDT
)
where BKEY IS NULL

View 8 Replies View Related

SQL & PL/SQL :: Check Listener Time Zone Settings / Values In Oracle 10g Database?

Nov 28, 2011

How to check the listener time zone settings/values in Oracle 10g database?

View 5 Replies View Related







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