• RELEVANCY SCORE 3.52

    DB:3.52:Find Duplicate Values In A Table Based On Combination Of Two Columns. zx




    In table which have millions of records -
    I have a Table B under Schema A and i have to find the duplicate records in a table. My Table B uses the combination of columns to identify the unique values.
    Scenario is something like this-
    One SV_Id can have multiple SV_Details_Id and i have to find out if there is any duplicate in the table. (I have to use combination of columns to identify the unique values and there is no Primary Key, Foreign Key or Unique Key on the table)

    then wrote the following query -
    select SV_Id,SV_Details_Id count (*) from SchemaA.TableB group by SV_Id,SV_Details_Id having Count (*) 1 ;

    Is it correct as after firing the above query it is returning the rows which is not matching to the duplicate.

    kindly help me.
    Looking forward for some guidance -
    Thanks in advance.

    DB:3.52:Find Duplicate Values In A Table Based On Combination Of Two Columns. zx

    What is the desired output??

    Do you want to see only unique records??

    Or you want to see the duplicate records.

    Giving an example will make it easier to provide the required querty.

    BTW here is an simple query which is used to delete the duplicate records and keep the unique records in table.

    DELETE
    FROM table_name a
    WHERE EXISTS (SELECT 1
    FROM table_name b
    WHERE a.sv_id = b.sv_id
    AND a.sv_detail_id = b.sv_detail_id
    AND a.ROWID b.ROWID
    );Regards
    Arun

  • RELEVANCY SCORE 3.50

    DB:3.50:Duplicate Values fp




    How to retrieve duplicate values from a table

    DB:3.50:Duplicate Values fp

    996661 wrote:
    yes iam getting the output but i dont know how 'over(partition by deptno)' works
    can you plz explain meSee this

    SQL select ename, deptno, count(*) over(partition by deptno) cnt
    2 from emp
    3 order by deptno;

    ENAME DEPTNO CNT
    ------ ---------- ----------
    KING 10 2
    CLARK 10 2
    JONES 20 4
    SCOTT 20 4
    SMITH 20 4
    ADAMS 20 4
    TURNER 30 5
    MARTIN 30 5
    WARD 30 5
    ALLEN 30 5
    BLAKE 30 5

    11 rows selected.

    SQL Here over (partition by deptno) virtually groups the records based on deptno and does the count. The grouping does not happen physically as in GROUP BY clause shown below.

    SQL select deptno, count(*) cnt
    2 from emp
    3 group by deptno
    4 order by deptno;

    DEPTNO CNT
    ---------- ----------
    10 2
    20 4
    30 5

    SQL

  • RELEVANCY SCORE 3.40

    DB:3.40:Need To Find Out Duplicate Values In A Table/Chart. d3





    Hi,

    I have some table where we consider the three differen fields value which is in text and date. If I want to put a condition between all three like if all three fields are same for same passeger that means that ticket is duplicate. And I want to know the ticket number based on all three fields value only if the fields are matching twice or more than that. Could any one please let me know the solution.

    EX;_

    ( route Passenger Dateof departure) 1, that means all the three values are if matching for the same passenger and date of journey and itinary is same that means it should show the duplicate ticket number.

    Please help.

    Best,

    Shubham

    DB:3.40:Need To Find Out Duplicate Values In A Table/Chart. d3


    Hi,

    Can you attach a image of the solution that you need?

    Celambarasan

  • RELEVANCY SCORE 3.38

    DB:3.38:Need Suggestions js


    Hi all,

    I have a table

    desc ITEMS

    Organization id
    segment1
    name..

    I need to find the duplicate values...say for each oraganization id i need to find the duplicate values in segment 1 column?

    thanks,
    baskar.l

    DB:3.38:Need Suggestions js

    Hi,

    baskar.l wrote:
    hi franks thanks again...suggest me a doc or book where i can start learning more abt pl/sql as am working as DBA..Knowing SQL is not absolutely required for learning PL/SQL, but I think your time will be better spent getting a good grasp of SQL first, before learrning PL/SQL.. For example, the problem you asked today could be solved in a different (and much less efficient) way using PL/SQL. If you try to learn PL/SQL now, I fear you'll be tempted to use it to solve problems like this, which will not help you any.

    A long time ago I used a book called Sams Teach Yourself PL/SQL in 21 Days by Gennick and Luers (ISBN 0-672-31798-2) which was pretty good, as are most of the books in the Sams Teach Yourself series. When you're more fluent in SQL and ready for PL/SQL, see if there's a recent edition of this.

  • RELEVANCY SCORE 3.36

    DB:3.36:Hierarchy (Set In Ecc) With Duplicate Values cz



    Hi all,

    I have a set in ECC which has duplicate accounts across different nodes. I created a generic datsource for set (which loads others sets too) and loaded it to BI to 0GL_ACCOUNT. So far so good.

    Now I dont want to load it to 0GL_ACCOUNT rather to ZGLACCOUNT, so I created a new generic datasource for set (I cant use the existing one as it is being used for other sets loading to 0GL_ACCOUNT). Now when I try to load it the system complains of duplicate values in the set.

    So the set loads ok to 0GL_ACCOUNT and not to ZGLACCOUNT. ZGLACCOUNT is created based on the template (not reference - i cant use the same master data table) of 0GL_ACCOUNT. what is that I'm overlooking?

    Thanks,

    Voodi

    DB:3.36:Hierarchy (Set In Ecc) With Duplicate Values cz


    Hi all,

    I have a set in ECC which has duplicate accounts across different nodes. I created a generic datsource for set (which loads others sets too) and loaded it to BI to 0GL_ACCOUNT. So far so good.

    Now I dont want to load it to 0GL_ACCOUNT rather to ZGLACCOUNT, so I created a new generic datasource for set (I cant use the existing one as it is being used for other sets loading to 0GL_ACCOUNT). Now when I try to load it the system complains of duplicate values in the set.

    So the set loads ok to 0GL_ACCOUNT and not to ZGLACCOUNT. ZGLACCOUNT is created based on the template (not reference - i cant use the same master data table) of 0GL_ACCOUNT. what is that I'm overlooking?

    Thanks,

    Voodi

  • RELEVANCY SCORE 3.31

    DB:3.31:Show Duplicate Table Values cj



    Hi.

    I'm trying to show duplicate value's only, from a table in my database. What is the best way to realize this in my QV application?

    I only want to show all the duplicate email addresses from the table and exclude the rest.

    Regards.

    DB:3.31:Show Duplicate Table Values cj


    That's correct Amar. I experimented with that also.

    Tnx.

  • RELEVANCY SCORE 3.26

    DB:3.26:How To Fetch Values From Table Ddshpval50 Based On Sy-Uname sk


    Hi all,Any idea how to fetch values from Table DDSHPVAL50 based on sy-uname.This table is used to store values for personal value list.

    DB:3.26:How To Fetch Values From Table Ddshpval50 Based On Sy-Uname sk

    Solved using Standard perform:PERFORM personal_values_select IN PROGRAM saplsdsd TABLES t_record USING p_shlp_descr .

  • RELEVANCY SCORE 3.26

    DB:3.26:Duplicate Values ca


    Let

    CREATE
    TABLE #tab (ID
    INT, SERIAL
    INT , credit INT
    )

    INSERT
    INTO #tab VALUES
    ( 1,1,1000
    )
    INSERT
    INTO #tab VALUES
    ( 1,1,1000
    )
    INSERT
    INTO #tab VALUES
    ( 1,1,2000
    )
    INSERT
    INTO #tab VALUES
    ( 1,1,3000
    )
    INSERT
    INTO #tab VALUES
    ( 2,1,1000
    )
    INSERT
    INTO #tab VALUES
    ( 2,1,1000
    )
    INSERT
    INTO #tab VALUES
    ( 2,2,1000
    )
    INSERT
    INTO #tab VALUES
    ( 3,1,1000
    )
    INSERT
    INTO #tab VALUES
    ( 3,2,1000
    )

    I want to find the id ,serial,credit where id ,serials,credit are same

    That is result should be like this
    ID
    Serial
    Credit
    1
    1
    1000
    2
    1
    1000Regards, Muhammad Bilal.

    DB:3.26:Duplicate Values ca

    Then try Manu's solution without cte:
    select ID, SERIAL, credit from #tab
    group by ID, SERIAL, credit
    having count(*) 1

  • RELEVANCY SCORE 3.26

    DB:3.26:Find Duplicate With Sum=0 x8


    I need to find duplicates (ID) but the sum of column VALUES has tu be zero. can anyone help me please ?

    I HAVE TABLE

    ID VALUE
    ------------------
    100 -100
    100 100
    100 100
    200 50

    --------------- THE DESIRED RESULT IS
    ID VALUE
    ---------------
    100 -100
    100 100
    ------------

    DB:3.26:Find Duplicate With Sum=0 x8

    Hi ,

    pre class="jive-pre"

    with tab as
    (
    select 100 id,100 "value" from dual
    union all
    select 100 id,-100 "value" from dual
    union all
    select 200 id,-100 "value" from dual
    union all
    select 300 id,-50 "value" from dual
    union all
    select 300 id,50 "value" from dual
    )
    select id,sum("value") from tab
    group by id
    having sum("value")=0

    /pre

  • RELEVANCY SCORE 3.23

    DB:3.23:Control Duplicate Values....Through Valueset 99


    hi

    one table contains duplicate value i want to control through valueset...

    thanks in advances...

    DB:3.23:Control Duplicate Values....Through Valueset 99

    hi

    one table contains duplicate value i want to control through valueset...

    thanks in advances...

  • RELEVANCY SCORE 3.18

    DB:3.18:Mark On Report If The Record Is Part Of A Duplicate Pair s1


    Hi,
    I have a table with 300 records. I ran a find duplicates query based on the table. The query shows a fewduplicate records. A duplicate recordis defined asthere being two or more records in the table with the same number in a specific
    field. (the other field values can be different)
    I now want to create a report showing the 300 records. In the report, I want to display an asterisk or some other indicator besides those records that show up in the duplicates query.
    How can I do that?
    Thank you!
    Craig

  • RELEVANCY SCORE 3.18

    DB:3.18:Delete Duplicate Records k1


    Hi, How I can delete the duplicate records based on Grp1 and grp_name.
    If grp1 and grp_name are same then record should be deleted.
    create table #Grp (Grp1 char(3), grp2 char(3), grp_name char(20))
    insert into #Grp values ('001','ABC','Casual Auto Con')
    insert into #Grp values ('001','DBC','Casual Auto Con')
    insert into #Grp values ('001','ADC','Casual Auto')
    insert into #Grp values ('001','ADG','Casual Auto Center')
    insert into #Grp values ('002','CCC','United Air Security')
    insert into #Grp values ('002','CCD','United Air Center')
    insert into #Grp values ('002','CDD','United Air Center')
    Delete from #Grp where ....
    --Desired Results
    Select * from #Grp
    Grp1 grp2 grp_name

    ---- ---- --------------------
    001 ADC Casual Auto
    001 ADG Casual Auto Center
    002 CCC United Air Security

  • RELEVANCY SCORE 3.15

    DB:3.15:Removal Of Duplicate - In That The Maximum Value dc


    Hi,

    I have a requirement, wherein I have a table, with different values, in that, I need to find out the duplicate records and see which one has the highest value. How can I find that.

    Please help

    Regards

    DB:3.15:Removal Of Duplicate - In That The Maximum Value dc

    Can you please try yourself as you have got an understanding of how to solve your issue. If you get stuck then post your problem and any forum member will surely help.

  • RELEVANCY SCORE 3.15

    DB:3.15:How Too Get Sum Of Duplicate Values In Table Of Access c7


    I have a column with duplicate values (text field)I just want the sum of duplicate
    Values in the next column

    DB:3.15:How Too Get Sum Of Duplicate Values In Table Of Access c7


    ID Single /Multiple First/Duplicate

    10048380-EN2_18-406 Multiple First

    10048380-EN2_18-405 Single First
    10048380-EN2_18-406 Multiple Duplicate
    10048380-EN2_18-406 Multiple Duplicate

    If the id is repeating i want the Single/Multiple field to be displayed as Multiple
    and if not repeating it should display Single

    First/Duplicate Field to be displayed as : First if the ID is Appearing for the first time and as Duplicate if the ID is coming for second and third and... time

    Access stores records like a barrel of bricks - no particular order. To determine what you want the records must be in an order to make the determination. Do you have a field to sort by?

  • RELEVANCY SCORE 3.14

    DB:3.14:Any Standard Source Based On Rpsco Table? ms



    Any standard source based on RPSCO table to get sum of values for WLP00?

    DB:3.14:Any Standard Source Based On Rpsco Table? ms


    Hi Naiduz, Hope so it helps 0CO_OM_OPA_50

    Regards,

  • RELEVANCY SCORE 3.14

    DB:3.14:Duplicate Values 9p


    I have a question regarding duplicate records,I need find duplicate records in 5columns and 4 should be have same values and one should be different

    DB:3.14:Duplicate Values 9p

    Thank you all, Finally I got the query.
    select A.last_name, A.first_name,
    A.Dec_num, A.Dec_Date, A.Address
    from State_Dis A
    Cross
    join State_Dis B
    where A.state
    = B.state

    and
    (
    (A.last_name
    = B.last_name
    and A.first_name
    = B.first_name and A.Dec_num
    = B.Dec_num
    and A.Dec_Date = B.Dec_Date
    and A.Address
    b.ADDRESS)
    or

    (A.last_name
    = B.last_name
    and A.first_name
    = B.first_name and A.Dec_num
    = B.Dec_num
    and A.Dec_Date B.Dec_Date
    and A.Address
    = b.ADDRESS)
    or

    (A.last_name
    = B.last_name
    and A.first_name
    = B.first_name and A.Dec_num
    B.Dec_num
    and A.Dec_Date
    = B.Dec_Date and A.Address
    = b.ADDRESS)
    or

    (A.last_name
    = B.last_name
    and A.first_name
    B.first_name
    and A.Dec_num = B.Dec_num
    and A.Dec_Date
    = B.Dec_Date and A.Address
    = b.ADDRESS)
    or

    (A.last_name
    B.last_name
    and A.first_name
    = B.first_name and A.Dec_num
    = B.Dec_num
    and A.Dec_Date = B.Dec_Date
    and A.Address
    = b.ADDRESS))
    order
    by a.Address,a.last_name,a.first_name

  • RELEVANCY SCORE 3.13

    DB:3.13:Need Help To Delete Duplicate Row (Should Not Consider The Primary Key Valu mm


    Hi,

    I have a table called "work_info".

    In this table , "Work_Order_No" is a Primary Key.

    I inserted Work_order_no value based on Sequce No.

    I want to find the Duplicate records Without considering the Work_order_no column.

    can u pls help how to find the duplicate records without considering the Primary Key?

    DB:3.13:Need Help To Delete Duplicate Row (Should Not Consider The Primary Key Valu mm

    you might find this example useful.

    SQL select * from work_info;

    WORK_ORDER_NO WORK_ITEM
    ------------- ----------------------------------------
    1 Job 1
    2 Job 1
    3 Job 2
    4 Job 3
    5 Job 3
    6 Job 4
    7 Job 5
    8 Job 5

    8 rows selected

    SQL select work_order_no, work_item,
    2 row_number() over (partition by work_item order by work_order_no, work_item) as rn
    3 from work_info;

    WORK_ORDER_NO WORK_ITEM RN
    ------------- ---------------------------------------- ----------
    1 Job 1 1
    2 Job 1 2
    3 Job 2 1
    4 Job 3 1
    5 Job 3 2
    6 Job 4 1
    7 Job 5 1
    8 Job 5 2

    8 rows selected

    SQL delete from work_info
    2 Where rowid in (Select work.rid
    3 From (Select work_order_no, work_item,
    4 rowid as rid,
    5 row_number() over (partition by work_item
    6 order by work_order_no, work_item) as rn
    7 From work_info) work
    8 Where work.rn 1);

    3 rows deleted

    SQL select * from work_info;

    WORK_ORDER_NO WORK_ITEM
    ------------- ----------------------------------------
    1 Job 1
    3 Job 2
    4 Job 3
    6 Job 4
    7 Job 5

    SQL

  • RELEVANCY SCORE 3.09

    DB:3.09:Duplicate Primary Key Error a3


    Hi,
     
    I get the following error when I try to insert a stored procedure in an SQL-server.
     
    Violation of PRIMARY KEY constraint 'PK_Login'. Cannot insert duplicate key in object 'Login'.
     
    My question if this is the real problem or the symtom of something else? I find it hard to believe that try to insert double key values. The table Login doesn´t contain any values.
     
    Thanks in advance!
     

    DB:3.09:Duplicate Primary Key Error a3

    Sankar, thanks I really appreciate the help! I removed the name lgnMemberNo from the insert statement and replaced it with @@identity. Now it´s working  Thanks a lot for the help!

  • RELEVANCY SCORE 3.08

    DB:3.08:How To Send Response Back To Db Adapter m7


    Hi,

    I have a requirement where I need to update a status field and last activity time field ( time of instance completed) in a database table. I used Polling option in database adapter, but it is updating the status field immediately once it is read. I don't want like that, I want to update the status field after completion of instance. Also, how to update the last activity time field ( i mean the field has to update at what time the instance got completed successfully). Actually, i am using

    1. A database adapter for polling followed by receive activity, assign activity, invoke activity and this invoke activity invokes one more database adapter ( the adapter selects a table row with the ID given by database adapter 1).
    2. Followed by transform activity, and again a invoke activity which invokes a webservice ( I am passing output values of 2nd database adapter as a input to webservice).

    Let me explain clearly, I have a base table and duplicate table. When ever values gets inserted or updated in base table it needs to be reflected in duplicate table ( wrote a trigger for this and it is working fine). Apart from this I added two more fields in duplicate table ( with column names status, and instance completion time). I am using two database adapters one is for polling from duplicate table and other one for selection of values from base table based on ID provided by duplicate table. Further, the invoke activity receives the output from database adapter and again i am passing the output values as a input to external webservice. My question is how can I update the status field and instance completion time in duplicate table. Someone please help me.

    Thanks,

    DB:3.08:How To Send Response Back To Db Adapter m7

    Hi,

    Use your function like this.
    xsl:value-of select="format-dateTime($input, '[D01] [MN,*-3] [Y0001]')"/Also ensure that you have the value in the $input variable.

    Regards,
    Neeraj Sehgal

  • RELEVANCY SCORE 3.07

    DB:3.07:Can I Insert Duplicate Record In A Table With Primary Key Anyhow?? 1c


    Hello All
    table :Emp(empno number primary key,ename varchar2(40));

    insert into emp values(1,'suneel');

    insert into emp values(1,'suneel');
    // will it be successful anyhow??

    Can I insert duplicate record in the above table anyhow??
    Thanks In Advance

    Suneel

    DB:3.07:Can I Insert Duplicate Record In A Table With Primary Key Anyhow?? 1c

    It's odd how some people always want to create exceptions to rules. But let's be open-minded. Can you explain why you want this duplicate value inserted in this table, please?

    Regards,

    -v-

  • RELEVANCY SCORE 3.06

    DB:3.06:Query On Deletion Of Duplicate Data Based On Insertion Time m1


    Hi All,

    I have loaded nearly 3 crore records in a table using SQL*Loader
    (There are four datafiles)
    Nearly 10 Lakhs records of a particular data file are duplicated on the database due to loading it by mistake another time.

    Table has no constraints defined on it.

    I know the approximate insertion time of the records.

    Is there any way to find the rows based on insertion time and delete those duplicate rows?

    Thanks,
    Kishore

    DB:3.06:Query On Deletion Of Duplicate Data Based On Insertion Time m1

    Nope...
    Row Movement was not enabled on that table...

    I will try to truncate the table and reload it.
    Although its timetaking, i think its the only option left out with me

    Thanks for your response

    Kishore

  • RELEVANCY SCORE 3.06

    DB:3.06:Duplication In Columns j1


    Hi all,
    I have two tables.
    1::User_companies -having attributes(company_id,application_id,user_id)
    There are duplicate values for each column in the table.
    2::Application -having attributes(id,application_name,code)

    I have two combine these two tables.I need to select application_name from "Application table"
    based on the condition User_companies.application_id=Application.id.My query is like this

    select distinct User_companies .application_id,User_companies.company_id,Application.name
    from User_companies,Application
    where User_companies.application_id=Application.id
    In the query result I get these columns with duplicate values.But I want
    distinct values for each column.How to achive this task?

    Plz help

    Thanks

    DB:3.06:Duplication In Columns j1

    Thank you very much for the quick response.I have posted DDL for the table

    1:CompantTable::

    CREATE TABLE "COMMON"."USER_COMPANIES"
    (
    "USER_ID" NUMBER(9,0) CONSTRAINT "USER_COMPANIES_NN1" NOT NULL ENABLE,
    "COMPANY_ID" NUMBER(9,0) CONSTRAINT "USER_COMPANIES_NN2" NOT NULL ENABLE,
    "APPLICATION_ID" NUMBER(7,0) CONSTRAINT "USER_COMPANIES_NN3" NOT NULL ENABLE,
    CONSTRAINT "USER_COMPANIES_UN1" UNIQUE ("USER_ID", "COMPANY_ID", "APPLICATION_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_TS2" ENABLE,
    CONSTRAINT "USER_COMPANIES_RF3" FOREIGN KEY ("APPLICATION_ID") REFERENCES "COMMON"."ADMIN_STRUCTURE" ("ID") ON
    DELETE CASCADE ENABLE,
    CONSTRAINT "USER_COMPANIES_RF1" FOREIGN KEY ("USER_ID") REFERENCES "COMMON"."USERS" ("ID") ON
    DELETE CASCADE ENABLE,
    CONSTRAINT "USER_COMPANIES_RF2" FOREIGN KEY ("COMPANY_ID") REFERENCES "COMMON"."COMPANIES" ("ID") ON
    DELETE CASCADE ENABLE
    )
    PCTFREE 5 PCTUSED 80 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE
    (
    INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS_TS1" ROWDEPENDENCIES ;
    CREATE UNIQUE INDEX "COMMON"."USER_COMPANIES_UN1" ON "COMMON"."USER_COMPANIES"
    (
    "USER_ID", "COMPANY_ID", "APPLICATION_ID"
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE
    (
    INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS_TS2" ;

    The application is view created from another table Admin_structure.Its too lengthy.Shall I post it??

    The result I get is like this

    APPLICATION_ID COMPANY_ID NAME
    ---------------------- ---------------------- ----------------------------------------------------------------
    3896 381 Crewing PAL
    3895 161 Accounts PAL
    3898 1 PMS PAL
    3895 1 Accounts PAL
    3900 1687 Admin PAL
    2350 334 TimeSheet
    2350 161 TimeSheet
    3896 534 Crewing PAL
    3900 334 Admin PAL
    3897 376 Purchase PAL
    3897 21 Purchase PAL
    3896 500 Crewing PAL
    3896 161 Crewing PAL
    3896 530 Crewing PAL
    4 381 Initial Setting up
    3895 379 Accounts PAL
    3895 381 Accounts PAL
    3899 1 Payroll PAL
    3900 381 Admin PAL
    3898 381 PMS PAL
    2350 381 TimeSheet
    2350 1 TimeSheet
    2350 21 TimeSheet
    3896 536 Crewing PAL
    The result I want

    APPLICATION_ID COMPANY_ID NAME
    ---------------------- ---------------------- ----------------------------------------------------------------
    3896 381 Crewing PAL
    3895 161 Accounts PAL
    3898 1 PMS PAL
    2351 2 Purcahse
    3900 1687 Admin PAL
    2350 334 TimeSheet
    ------- ------- -----

    I want these columns without duplication.

    Thanks

    Edited by: Swapna on Aug 20, 2011 1:17 AM

  • RELEVANCY SCORE 3.06

    DB:3.06:Delete Duplicate Records 1p


    Hi,

    I am confused in writing a query, Table in my database has duplicate records. I need to delete the repeated records and retain only the single record. Actually there are 15 columns on the table i am just giving a sample example of the table with mini structure

    Data inside the table is shown as below

    ID EMP_ID UNIQUE_ID CLIENT_NAME CLIENT_ADDRESS
    1 97 I567 ABC CA
    2 97 I567 ABC CA1
    3 97 I567 ABC CA2
    4 97 I567 ABC CA

    In the above table 1 and 4 duplicate we can identify using client_address we can find duplicate records still there are other 15 columns in that any one among may be different and I need to find the duplicate records

    Please suggest me some methods to delete duplicate records based on all the columns comparison in the table and get the duplicate records.

    Thanks
    Sudhir

    DB:3.06:Delete Duplicate Records 1p

    Hi TDP-Opitz-Consulting,

    great solution I'll keep that in mind for the future.

    Just a small addition:
    group by EMP_ID UNIQUE_ID CLIENT_NAME CLIENT_ADDRESS
    -- should be
    group by EMP_ID, UNIQUE_ID, CLIENT_NAME, CLIENT_ADDRESS -- commas addedBye

    stratmo

  • RELEVANCY SCORE 3.05

    DB:3.05:Questions On Obiee Report / Dashboards sf


    What is report based total?
    while using PIVOT TABLE we find this option of the measures section more options

    Where can we set suppress or repeat default settings?
    On Column properties for duplicate values suppression. I would like to know the difference between the suppress and default

    Remove My Dashboard completely.
    My dashboard is the default dashboard appearing to the users. If I will have to remove this and set another dashboard as default, what is the option

    I would like to the usage procedure for the personalized option on Delivers(IBots)

    Thanks in advance,
    Samson.

    Edited by: user630453 on Apr 25, 2009 9:26 AM

    DB:3.05:Questions On Obiee Report / Dashboards sf

    What is report based total?
    while using PIVOT TABLE we find this option of the measures section more options

    Where can we set suppress or repeat default settings?
    On Column properties for duplicate values suppression. I would like to know the difference between the suppress and default

    Remove My Dashboard completely.
    My dashboard is the default dashboard appearing to the users. If I will have to remove this and set another dashboard as default, what is the option

    I would like to the usage procedure for the personalized option on Delivers(IBots)

    Thanks in advance,
    Samson.

    Edited by: user630453 on Apr 25, 2009 9:26 AM

  • RELEVANCY SCORE 3.03

    DB:3.03:Duplicate Records Find In Table 33



    Hi ALL,
    I want to find the dynamically check the primary key values and remove the duplicate records.. please suggest it.

    Regards
    Ram

    DB:3.03:Duplicate Records Find In Table 33

    Is column Name will provide in runtime??? Based on that column Name, u need to find the columnNames with duplicate??
    You need to use dynamic sql... see the below example.
    And also when u r asking a question, try to give some examples, your table structure and some sample insert statements,.

    declare @ColumnName varchar(100)
    declare @sql nvarchar(max)
    set @sql = select + @ColumnName + from TableName group by + @ColumnName + having count(*) 1
    exec(@sql)

  • RELEVANCY SCORE 3.03

    DB:3.03:How To Create A Table With Distinct Values ? cf


    I want to create a new table based on an existing table distinct values only, how can I avoid to create the duplicate values ?

    e.g.
    table1
    field1 field2
    0001 ABD
    0001 ABD
    0002 DCF
    0002 DCF

    new table
    field1 field2
    0001 ABD
    0002 DCF

    thanks

  • RELEVANCY SCORE 3.03

    DB:3.03:How To Find Duplicate Records In A Table Using Partition Clause k8


    How to find duplicate records in a table using Partition

    DB:3.03:How To Find Duplicate Records In A Table Using Partition Clause k8

    SELECT ID, LastName, FirstName,
    RANK() OVER (PARTITION BY LastName,
    FirstName ORDER BY ID) AS SeqNumber
    FROM
    (SELECT ID, LastName, FirstName
    FROM Customers
    WHERE (LastName, FirstName) IN
    (SELECT LastName, FirstName
    FROM Customers
    GROUP BY LastName, FirstName
    HAVING COUNT(*) 1)

  • RELEVANCY SCORE 3.02

    DB:3.02:Facing Issue With Createwithparams Operation jp


    Hello,

    I am using Jdeveloper 11g (11.1.2.1.0). My requirement is duplicate few column values from the above row.

    I created ADF Table and used the Operation CreateWithParams. It is working; But the issue is, Always it is copying the values from first row in the table and It is inserting the new record at beginning of the ADF Table.

    I want to duplicate the value from the current record position and insert the record just below that.

    I referred *[ADF Code Corner 013. How-to declaratively create new table rows based on existing row content|http://www.oracle.com/technetwork/developer-tools/adf/learnmore/13-create-with-params-169140.pdf]* steps. Any suggestions.

    Regards
    Surendra

    DB:3.02:Facing Issue With Createwithparams Operation jp

    I have a similar problem. The field is manditory, but I don't want to provide the value at creation time. I don't have a reasonable default value and I want to make sure the user enters a value. If I relax the Manditory requriement, then how do I find all the dirty entity objects and apply the manditory validation?

  • RELEVANCY SCORE 3.02

    DB:3.02:Re: Duplicate Values 39


    What do you mean by duplicate values? Can you provide an example? I'm kind of confused given that you have a primary key defined on this table that encompasses almost all the columns.

    DB:3.02:Re: Duplicate Values 39

    872435 wrote:
    It is showing the same result as previous post in which i have provided the table dataIf you ran my query and got a result that would imply that the resulting records already exist in the table then.

  • RELEVANCY SCORE 3.00

    DB:3.00:How Can We Find Duplicate Record Of Table px


    Dear All

    How can we find the duplicate record in a table.

    Regards

    Ashish

    DB:3.00:How Can We Find Duplicate Record Of Table px

    Dear All

    How can we find the duplicate record in a table.

    Regards

    Ashish

  • RELEVANCY SCORE 3.00

    DB:3.00:Find Duplicate Data jx


    I have a table say students with data
    snmae mark
    a 100
    a 100
    a 58
    b 58

    I want to find the repeating values. like only the rows 12 should be listed. How to proceed with this.Is there a way other than putting loops for each check.
    Please help me

    DB:3.00:Find Duplicate Data jx

    try to post the table structures. thanks.

  • RELEVANCY SCORE 3.00

    DB:3.00:Duplicate Values In A Table f8


    How to retrieve only duplicate values in a table, not unique values.

    Thanks,
    Mahesh

  • RELEVANCY SCORE 3.00

    DB:3.00:How To Find The Table In The Dictionary? x7



    Hi,

    I have to get the values of kunnr corresponding to the values of vkorg,vtweg and spart i..e.

    I have to fetch the customer data based upon the values of sales organization,channel and division..

    Is thr any table which includes all these 4 fields together??...

    I found one table A007,its a pooled table..

    are there any other tables??...

    Thanks,

    Sri

    DB:3.00:How To Find The Table In The Dictionary? x7


    Hi,

    KNVV table has all the fields. Check it..

    Thanks.

  • RELEVANCY SCORE 2.99

    DB:2.99:Deleting Data Based On Date 83


    Hi, I am just looking for some help in regards to a table of data I have. Currently I have a list of 500k Job Ids and Time Values. I have managed to identify that I have alot of duplicate job ids and I was looking to find out how i can write a query to give me the first job id with the min date.An example of the data table is the following:Job ID | TimeStamp123ab | 18-MAY-13 21:00:06123fg | 18-MAY-13 21:05:32123ab | 19-MAY-13 11:10:05If somebody could please advise, would really appreciate it.

    DB:2.99:Deleting Data Based On Date 83

    If you just want a TOP-N query to get the first row of each group, you can use a couple of different methods e.g.
    SQL select deptno, hiredate from emp order by deptno, hiredate;

    DEPTNO HIREDATE---------- ---------- 10 09/06/1981 10 17/11/1981 10 23/01/1982 20 17/12/1980 20 02/04/1981 20 03/12/1981 20 19/04/1987 20 23/05/1987 30 20/02/1981 30 22/02/1981 30 01/05/1981 30 08/09/1981 30 28/09/1981 30 03/12/1981

    14 rows selected.

    SQL edWrote file afiedt.buf
    1 select deptno, hiredate 2 from (select deptno, hiredate, row_number() over (partition by deptno order by hiredate) as rn 3 from emp 4 ) 5* where rn = 1SQL /

    DEPTNO HIREDATE---------- ---------- 10 09/06/1981 20 17/12/1980 30 20/02/1981

    SQL edWrote file afiedt.buf
    1 select deptno, max(hiredate) keep (dense_rank first order by hiredate) as hiredate 2 from emp 3* group by deptnoSQL /

    DEPTNO HIREDATE---------- ---------- 10 09/06/1981 20 17/12/1980 30 20/02/1981
    Now if you want to actually delete duplicate records, that's a different matter. A search of the forums for "delete duplicate" should give you plenty of examples.

  • RELEVANCY SCORE 2.99

    DB:2.99:Deletion Of Duplicate Records 1f


    Hi All,

    Can any one let me know how to find duplicate records in a table then we delete duplicate entries?

    Thanks

    DB:2.99:Deletion Of Duplicate Records 1f

    gqmallah wrote:
    My question is that there are duplicate rows in the table and I want to delete second row only. I want from 2 duplicate rows 1 row should remain in table and extra row should be deleted.What is your definition of 2 ^nd^ row ?

    You can keep one row out of the duplicate rows

    Another approach.
    CREATE table new_tab as
    select Distinct col1, col2,.... from your_table ;
    drop your_table ;
    rename new_tab to your_table ;
    -- Create necessary indexes and constraints for your_table SS
    http://db-oracl.blogspot.com

  • RELEVANCY SCORE 2.99

    DB:2.99:Help Fixing A Combo Box Return Duplicate Values Only Once 37


    I have created a lookup list for one table based on data from another table. The list contains duplicate values. How can I fix this? Here is a copy of the SQL

    SELECT [Rabbit Information].ID, [Rabbit Information].Gender FROM [Rabbit Information];

    Any ideas would be very appreciated.

    Susan

    DB:2.99:Help Fixing A Combo Box Return Duplicate Values Only Once 37


    Don,

    I originally had this field set to a list box with values I typed (fixed list), but I had to change it to a lookup based on the other table to create a relationship between the two tables so that when I entered a new rabbit into the rabbit info table this
    table would update also.

    Note: Doing this added an expansion icon at the left-side of all my records in Rabbit Information table and I have to also select the gender there to get the Rabbit Name (Master) table to update with new entries. The Rabbit Name (Master) table supplies two
    different fields (Sire and Dam) with a list of names based on gender to select from for these fields. This is necessary for me to be able to prove the pedigree of my rabbits.

    Tried your code and now the box returns no values.? Any thoughts on how to fix this? Also any way to stop having to choose the gender in the expansion (what I mean is can I just choose the gender in the Rabbit Info table and have the Rabbit Name (Master)
    table update with both fields of information)?

    Susan

    It's hard to answer without knowing your data structure. Please go to your Relationships window and hit [PrtScr] on your keyboard, then paste it into a graphics program, save it, then attach itto your reply.

  • RELEVANCY SCORE 2.96

    DB:2.96:Want To Sort Hashmap Based On Values With Duplicate Values 7j


    Hi
    I want to sort the hashmap based on the values. But i have the duplicate values.

    Thanks.

    DB:2.96:Want To Sort Hashmap Based On Values With Duplicate Values 7j

    You can sort by using some other collection classes. E.g.

    - if only the values are of interest, you can create a List of values and sort that:
    List valuelist = new ArrayList(hashmap.values());
    Collections.sort(valuelist);You may also use a comparator to define the sort order.

    - if you need the key/value pairs also in the ordered list, then you could use the entrySet of the map. In this case, you have to write your own comparator, as the elements of the entryset are of type Map.Entry:
    List entrylist = new ArrayList(hashmap.entrySet());
    Collections.sort(entrylist,new ValueComparator());

    class ValueComparator implements Comparator {
    public int compare(Object o1, Object o2) }
    Map.Entry e1 = (Map.Entry) o1;
    Map.Entry e2 = (Map.Entry) o2;
    Comparable c1 = (Comparable)e1.getValue();
    Comparable c2 = (Comparable)e2.getValue();
    return c1.compareTo(c2);
    }
    }This code assumes, that your objects implement Comparable and does no error checking at all. You should be a little bit more cautious and use some try/catch. It's just to show the approach.

  • RELEVANCY SCORE 2.96

    DB:2.96:How To Prevent Duplicate Values Showing In Parameters List ms


    In reporting services 2005 I'm creating a parameter which is based on a query.  I have a table with many rows and trying to use one of the columns as my parameter value options.  However there are only 6 unique values in te 900 rows of data... my parameter list is showing parameter for every row, and not limiting to the unique values only.How do I make sure that the parameters displayed are only the unique values within the column?

    DB:2.96:How To Prevent Duplicate Values Showing In Parameters List ms

    Thanks - That worked awesome... took me a while to adjust and work out for my data but I got it!

  • RELEVANCY SCORE 2.96

    DB:2.96:Table Control 9x



    how can you find duplicate entries in a column of table control?

    DB:2.96:Table Control 9x


    Hi,

    Before the Table control is assigned Data, use a similar ligic as follows :

    TYPES:
    BEGIN OF strtab,
    carrid TYPE s_carr_id, "------ column whose duplicate entry needs to be found
    END OF strtab.

    DATA it_tab TYPE TABLE OF strtab.

    SELECT carrid FROM spfli "------ your table
    INTO CORRESPONDING FIELDS OF TABLE it_tab
    GROUP BY carrid HAVING COUNT( * ) 1.

  • RELEVANCY SCORE 2.96

    DB:2.96:Confusing Results From Using ! Find Duplicate Values 1p



    I have composite field as the unique identifier for a table. I used ! find duplicate values on this filed to identify duplicate records. I got the found set with the uniqueID field as in the following:

    64-01-3monthCur

    64-01-3monthIni

    The two records are not duplicate in my point of view. Why is Filemaker returning them as duplicats? Did I miss something here?

    Thank you for your inputs.

    DB:2.96:Confusing Results From Using ! Find Duplicate Values 1p


    Tim and Jerry,

    Thank you for pointing it out. I changed it to text and it returns no duplicate now.

    Liyan

  • RELEVANCY SCORE 2.93

    DB:2.93:Update Query Help? 81


    Hi All,
    We have table which has duplicate records based on id is:-
    CREATE TABLE #TEMP
    (
    ID INT,
    CH VARCHAR(20)
    )

    INSERT INTO #TEMP VALUES (1, 'Principal')
    INSERT INTO #TEMP VALUES (1, 'Teacher')
    INSERT INTO #TEMP VALUES (1, 'Director')
    INSERT INTO #TEMP VALUES (1, 'Student')
    INSERT INTO #TEMP VALUES (1, 'Something')
    INSERT INTO #TEMP VALUES (1, 'Pieon')
    INSERT INTO #TEMP VALUES (2, 'Student')
    INSERT INTO #TEMP VALUES (2, 'Something')
    INSERT INTO #TEMP VALUES (2, 'Pieon')
    INSERT INTO #TEMP VALUES (3, 'Teacher')
    INSERT INTO #TEMP VALUES (3, 'Pieon')
    INSERT INTO #TEMP VALUES (3, 'blablabla')
    INSERT INTO #TEMP VALUES (4, 'blablabla')

    SELECT *
    FROM #TEMP

    DROP TABLE #TEMP
    We need to update above table with the update queryto make it with unique id value based on ch column, our chronological order is:-
    Director, Principal, Teacher, Student, Something, Pieon, blablabla
    Expected Output:-
    ID CH
    1 Director
    2 Student
    3 Teacher
    4 blablabla

    Thanks
    Regards,
    Kumar

    DB:2.93:Update Query Help? 81

    I'll will try you query Naomi, and reply you back ASAP

    Thanks

  • RELEVANCY SCORE 2.93

    DB:2.93:How To Find Last Value Of A Duplicate Row 3x


    Hi,

    I have 2 values in a table

    10 ABC
    10 DEF

    I want to get the last value i.e 10 DEF

    Any help will be needful for me

    DB:2.93:How To Find Last Value Of A Duplicate Row 3x

    Which column defines the order of the row. Assuming it is the second column you can do this.

    with t
    as
    (
    select 10 no, 'abc' val from dual
    union all
    select 10 no, 'def' val from dual
    )
    select no, val
    from (
    select no, val, row_number() over(order by val) rno, count(*) over() cnt
    from t)
    where rno = cnt

  • RELEVANCY SCORE 2.93

    DB:2.93:Populate Specific Field Data From Table In Variable(Lookup Select Box) px



    Hi All,

    We have OS Distro(u_os_distro) field in image table,the field contain repeatable values.

    now i have to use OS Distro field data in variable(LOOKUP SELECT BOX) with unique value(no duplicate) and the values need to change based on other variable selection.

    DB:2.93:Populate Specific Field Data From Table In Variable(Lookup Select Box) px


    Hi All,

    We have OS Distro(u_os_distro) field in image table,the field contain repeatable values.

    now i have to use OS Distro field data in variable(LOOKUP SELECT BOX) with unique value(no duplicate) and the values need to change based on other variable selection.

  • RELEVANCY SCORE 2.92

    DB:2.92:Is There A Way To Differentiate Two Same Sys_Choice Entries From User's Preferred Language? 1k



    Hi,

    There are entries in sys_choice table which are duplicate. Those entries are corresponding to the preferred_language column in sys_user table. So in User form language drop-down i can see duplicate language values. I have checked all the duplicate languages are used by users. Even i can filter out users based on those duplicate valuesin Service Now. This same thing i want to do using Script (Business rules etc.). I found sys_choice maintain sys_id for each entries but I'm not sure how Service Now is mapping preferred_language column value in sys_user with the sys_choice entries. So my question is how to find out number or users who used each of the duplicate language value using script?

    DB:2.92:Is There A Way To Differentiate Two Same Sys_Choice Entries From User's Preferred Language? 1k


    Hi Sanoajul,

    Here is the query for how the platform builds the preferred language choice list:

    name=sys_user^element=preferred_language^language=en^inactive=false^ORinactiveISEMPTY

    If you run that query, do you see duplicates? If so, can you attach the list so I can understand your problem better?

    Thanks.

    Will

  • RELEVANCY SCORE 2.92

    DB:2.92:Infopath 2010 Sp List Form Repeating Table j1


    Hi,
    It's Regards Infopath 2010 SP list form with repeating table,
    Actaully I'veinfopath form with repeating tableattached toSP list. I'd like to make one column as duplicate, it shouldhave same value on every row like When user entered value in the column at frist row and it should replicate
    same value when userInserting new rows in repeating table.
    Other question, i'm tyring to limit repeating table rows based on number of items in dropdown list (values in drop drow will change, based on master dropdown selection)
    Please provide your suggestions, Thanks in Advance.

    DB:2.92:Infopath 2010 Sp List Form Repeating Table j1


    Hey, i got answer for frist question. FYI
    http://www.bizsupportonline.net/infopath2007/automatically-populate-repeating-table-infopath-sequential-dates.htm
    Thanks!!

  • RELEVANCY SCORE 2.91

    DB:2.91:Power Pivot Creating A Relationship With Columns With Duplicate Values xc


    I am creating my first Power Pivot. I have two excel imports related to payroll. Both have employee numbers referenced and one has employee names so I wanted to create a relationship. These exports cover the entire year thus there are
    duplicate rows for each employee based upon the pay period. When I try to create a relations ship I get the error that relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique
    values....I will later create a pivot table that gives me total payroll by employee etc. What am I doing wrong...

    DB:2.91:Power Pivot Creating A Relationship With Columns With Duplicate Values xc

    Hi,
    Relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.
    The table’s relationship error is coming from each column contains duplicate values. You need have at least one column that contains only unique values. You will need to eliminate the duplicate, otherwise the Power pivot will not work. One way for resolving
    this issue is in your table use an aggregate function if these duplicate records are no useful. The simplest method is adding new column extra to my Excel sheet and using an auto number like SeanDza mentioned.
    Here is a similar topic thread for your reference:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/78d3a631-efea-40b7-9e82-6fbe2f3239d1/excel-powerpivot-create-relationship-between-2-excel-tables-files-located-in-my-desktop
    Regards,We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

    Click
    HERE to participate the survey.

  • RELEVANCY SCORE 2.91

    DB:2.91:Retrieving Only Duplicate Values dx


    I have a table.In that table i have empno column(shown below).

    That column contains duplicate rows and non duplicate rows.

    Now i want to retrieve only duplicate rows...HOW?

    examle:
    Empno
    101
    102
    103
    101
    102
    104
    101
    101
    105
    106

    DB:2.91:Retrieving Only Duplicate Values dx

    Hi,
    Try below

    SQL insert into t_test values(101);

    1 row created.

    SQL insert into t_test values(102);

    1 row created.

    SQL insert into t_test values(103);

    1 row created.

    SQL insert into t_test values(101);

    1 row created.

    SQL insert into t_test values(102);

    1 row created.

    SQL insert into t_test values(104);

    1 row created.

    SQL insert into t_test values(101);

    1 row created.

    SQL insert into t_test values(101);

    1 row created.

    SQL insert into t_test values(105);

    1 row created.

    SQL insert into t_test values(106);

    1 row created.

    SQL
    SQL select empno,count(1) from t_test
    2 group by empno
    3 having count(1)1;

    EMPNO COUNT(1)
    ---------- ----------
    102 2
    101 4

    SQLif you want to find out duplicate values in multiple columns then add it in the select clause and gruop by clause.

  • RELEVANCY SCORE 2.91

    DB:2.91:Duplicate Values Based On Top Value d3


    Hi
    I would like to use PowerQuery to duplicate values. I received a long table with information that's deduplicated on column level (see example below).
    I want to duplicate values so I can sort/filter (see below).
    Thanks for your help!
    Koen

    DB:2.91:Duplicate Values Based On Top Value d3

    You can use Table.FillDown to do this. There's no UI support for this yet, so you'll need to enter a new expression manually:
    =Table.FillDown(Source, {Level, Sublevel})
    A complete program demonstrating this with your data looks like this:
    let
    Source = Table.FromRows({{Belgium, Marketing, Person 1, 10}, {null, null, Person 2, 20}, {null, null, Person 3, 30}, {null, Communication, Person 4, 40}, {France, Communication, Person 5, 50}, {null, null, Person 6,
    60}, {null, null,Person 7, 70}, {null, Marketing, Person 8, 80}, {null, null, Person 9, 90}}, {Level, Sublevel, Person, Amount}),
    Filled = Table.FillDown(Source, {Level, Sublevel})
    in
    Filled

  • RELEVANCY SCORE 2.91

    DB:2.91:Identify Duplicate Rows And Update A Flag In Atable ds


     
    Hi All,
     
    I have a table mytable. It has a table structure as given below.
     
    Name           ID        Address        dept     Flag      remarksaaaa           001       sdfsdf          10           null        null   aaaa           001       sdfsdf          10           null        null            aaaa           001       sdfsdf          10           null        null           bbbb           002       qweqwe       20           null        null
     
    The table has no primary key.
    I have check for duplicate rows based on the name and Id and then update the fields flag and remarks as Y and duplicate.
     
    So after the check mytable should look like
    Name           ID        Address        dept     Flag      remarksaaaa           001       sdfsdf          10            Y       duplicateaaaa           001       sdfsdf          10            Y       duplicate   aaaa           001       sdfsdf          10            Y       duplicate bbbb           002       qweqwe       20          null          null
     
    I dont want to have temporary table or recreate the table in the process . Is it possible to straightaway find the duplicate and update the fields using htis tbale alone.
     
    I dont want any duplicate rows to be deleted or moved. i want only the flag and remarks updated.
     
    Please help to write the query as i am new to SQL
     
     
    Thanks in Advance.
     
     

  • RELEVANCY SCORE 2.91

    DB:2.91:Finding All Duplicates ps


    Hello Guys,

    I want to know how to find all the duplicate records from a set of data, in my application a duplicate is a combination of two coloumns, i dont want to group them I just want to know all the duplicates records

    example

    CREATE TABLE EMPLOYER
    (
    ID NUMBER(12),
    SSN VARCHAR2(9 BYTE),
    WGE NUMBER(10,2),
    ERR_flg VARCHAR2(1 BYTE)
    )

    INSERT INTO EMPLOYER(ID,SSN,WGE) VALUES(1,222222222,2532)
    INSERT INTO EMPLOYER(ID,SSN,WGE) VALUES(2,111111111,2352)
    INSERT INTO EMPLOYER(ID,SSN,WGE) VALUES(3,555555555,232)
    INSERT INTO EMPLOYER(ID,SSN,WGE) VALUES(4,555555555,232)

    3RD AND 4TH RECORDS ARE DUPLICATE..I WANT TO SHOW THEM IN MY QUERY AND UPDATE THE ERR_FLG TO 1

    thanks

  • RELEVANCY SCORE 2.91

    DB:2.91:How To Insert Only Distinct Values From A Flat File k9


     
    I have to insert the Values from the Flat Files , My table structures have  Primary keys  , how do i insert only the distinct values into the table  without  the ERROR VIOLATION OF Primary Key already exists a record.
     
    Dropping and Adding Relationships after insert is a way but doesnt serve the whole purpose  is there a way we can  eliminate duplicate records  based  on their Primary key  before inserting them into  the Database.

    DB:2.91:How To Insert Only Distinct Values From A Flat File k9

    I am trying to Prevent the Duplicate Records from getting inserted . i am working on it  will post back with the results. Thanks!!!
     

  • RELEVANCY SCORE 2.90

    DB:2.90:Finding Duplicate Values In A Column With Different Values In A Different C p7


    I'm finding duplicate values in a column of my table:

    select ba.CLAIM_NUMBER from bsi_auto_vw ba
    group by ba.CLAIM_NUMBER
    having count(*) 1;

    How can I modify this query to find duplicate values in that column where I DON'T have duplicate values in a different specific column in that table (the column CLMT_NO in my case can't have duplicates.)?

    DB:2.90:Finding Duplicate Values In A Column With Different Values In A Different C p7

    Well, you can use analytics assuming you don't mind full scanning the table.....

    select
    count(owner) over (partition by object_type),
    count(object_type) over (partition by owner)
    from all_objects;You just need to intelligently (i didn't here) find your "window" (partition clause) to sort the data over, then make use of that (the analytics would be in a nested SQL and you'd then evaluate it in outside).

    Should be applicable if i understand what you're after.

    If you post some sample data we can mock up a SQL statement for you (if what i tried to convey wasn't understandable).

  • RELEVANCY SCORE 2.90

    DB:2.90:How Can I Find Duplicate Values Within A Repeating Attribute? pm



    I want to check all dm_document objects to find out if they have any duplicate items in a repeating attribute.. is this possible?

  • RELEVANCY SCORE 2.90

    DB:2.90:Find Duplicates 9m


    I want to retrieve all those records which have duplicate values in a table. I have TNo and Name fields in a table. I want to view all records that have duplicate TNo and Name records.

    DB:2.90:Find Duplicates 9m

    this examples might be of some help.
    SQL select * from employees;

    YEAR EM NAME PO
    ---- -- ---------- --
    2001 02 Scott 91
    2001 02 Scott 01
    2001 02 Scott 07
    2001 03 Tom 81
    2001 03 Tom 84
    2001 03 Tom 87

    6 rows selected.

    SQL select year, empcode, name, position,
    2 row_number() over (partition by year, empcode, name
    3 order by year, empcode, name, position) as rn
    4 from employees;

    YEAR EM NAME PO RN
    ---- -- ---------- -- ----------
    2001 02 Scott 01 1
    2001 02 Scott 07 2
    2001 02 Scott 91 3
    2001 03 Tom 81 1
    2001 03 Tom 84 2
    2001 03 Tom 87 3

    6 rows selected.

    SQL Select year, empcode, name, position
    2 From (Select year, empcode, name, position,
    3 row_number() over (partition by year, empcode, name
    4 order by year, empcode, name, position) as rn
    5 From employees) emp
    6 Where rn = 1;

    YEAR EM NAME PO
    ---- -- ---------- --
    2001 02 Scott 01
    2001 03 Tom 81

  • RELEVANCY SCORE 2.90

    DB:2.90:Deleting Duplicate Values From Internal Table m1


    hi experts,i have fetched some values into internal table along with these values some duplicate values are also coming and i want to delete them how can i do thisi have used the code 'delete adjecent dulicate values'still the problem is not solvedguide meregardssiri

    DB:2.90:Deleting Duplicate Values From Internal Table m1

    Hi,Use this sort internal table by field.Delete adjacent duplicate values from itab comparing field1 field2or Delete adjacent duplicate values from itab comparing all fields.regards,Pavan.

  • RELEVANCY SCORE 2.89

    DB:2.89:De-Duplication cp


    Hi Masters,I have a master table which is contains duplicate records. I wanted to insert original record into one table, and remaining duplicate records into another table. I wanted to write a pl/sql blockfor this senerio. Please help me.create table sample(name varchar2(10),sal number(5));insert into sample values('ABC',100);insert into sample values('BCA',200);insert into sample values('CAB',300);insert into sample values('ABC',100);insert into sample values('BCA',200);insert into sample values('CAB',300);insert into sample values('ABC',100);insert into sample values('BCA',200);insert into sample values('CAB',300);create table union_a (name varchar2(10),number(5));create table dup_b (name varchar2(10), number(5));please find above the table script and insert statements..I am using Oracle 11g(11.2.01). I wanted to insert one distinct record into table union_a .. duplicate records into dup_b table.Thanks in adv.RegardsSA

  • RELEVANCY SCORE 2.88

    DB:2.88:Compare Previous And Next Record In A Table 33


    I have dataset which looks like below:

    Name || DocType

    --------------------------

    APDF
    ADOC
    BPDF
    BPDF
    BXLS
    CPDF
    DXLS
    DPDF
    DXLS
    FPDF
    FPDF
    GPDF
    H XLS

    Need a way to be able to add additional column based on values in column Name and Doctype.

    Name || DocType || Status

    ----------------------------------------------------

    APDF Duplicate
    ADOC Duplicate
    BPDF Duplicate - Multi PDF
    BPDF Duplicate - Multi PDF
    BXLS Duplicate - Multi PDF
    CPDF Unqiue
    DXLS Duplicate
    DPDF Duplicate
    DXLS Duplicate
    FPDF Duplicate - Multi PDF
    FPDF Duplicate - Multi PDF
    GPDF Unique
    H XLS Unique

    Logic to be used is, Check Name column, if there are multiple entries with same Name further check DocType, If there are mulitple entries for PDF then mark Status as 'Duplicate - Multi PDF'.
    If there are multiple entries for Name column, but only one of these entries is PDF , then mark status as Duplicate.
    If there is only one entry per name, no matter what the Doctype, mark the status as "Single".

    Let me know if the problem needs further explaination.
    Thanks.

    Edited by: 934827 on May 16, 2012 2:03 PM

    DB:2.88:Compare Previous And Next Record In A Table 33

    Wow! This worked like a charm!

    Thanks a tonne!! You saved my day.

    And to answer your question, it doesnt matter if there are no PDFs when there are multiple rows with same name.

    Edited by: 934827 on May 16, 2012 2:39 PM

  • RELEVANCY SCORE 2.88

    DB:2.88:Finding Duplicates In Sql md


    I have to find duplicate clients and duplicate associates both are contained in a contact table, they are also some of them associated with one another, plus the duplicates have associated other contacts examples... nurses, rns, etc.... so I have duplicate
    associates with duplicate clients and duplicate clients matched up with multiple associates which are in the same contact table and they are associated by there ID's in a contactassociation table, each duplicate also has a rolekey which specifies there role
    I.E client or I.E and associate. So they each have an role and based on there roles they have different events, and they also have events based in an an events table, so I have to find duplicate events and duplicate associates and on there events they have
    notes on a table called eventnotes so based on there events I have to find possible dup notes and if the client or associates have different events I have to transfer the events into the events table from the duplicate client or associate I have decided I
    dont want based on who has more active events or who has more associates, becuase each client or associate could have multiple associates. if there associates are different I need to update thecontactassociation table with the associates or client depending
    on which one I am working with. I am using views functions and stored procedures.
    Does anyone have any easier way of dealing with this?
    Basically there are 4 tables contact,contactassociations,eventdefintion,eventnotes and I have to find duplicate clients and then associates .
    Keep in in mind I have to do clients and associates separately becuase they have different rolekeys based on whether they are a client or associate.
    example client rolekey1 on contactassociation=1 and for an associate rolekey2=2 same with ID the Id matches the same one on contact table
    however in the contactassociation table its ID1 and ID2 this way it associates the two ids found in the contact table.
    Any Ideas on how to fixed this duplicate issue. ??

    DB:2.88:Finding Duplicates In Sql md

    Please check is this Useful...
    WITH CTE (COl1,Col2, DuplicateCount)
    AS
    (
    SELECT COl1,Col2,
    ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
    FROM DuplicateRcordTable
    )
    DELETE
    FROM CTE
    WHERE DuplicateCount 1
    Rgards, Sagar

  • RELEVANCY SCORE 2.88

    DB:2.88:Re: *Urgent Ora-01427 Single Row Subquery zz


    Check that project table have duplicate values.

    DB:2.88:Re: *Urgent Ora-01427 Single Row Subquery zz

    Hi ,UPDATE projectdetails SET projectdetails.projectname= (SELECT projectid FROM project WHERE projectdetails.projectname = project.projectname);For any query, to test and excecute do as follow.Break the query like1. UPDATE projectdetails SET projectdetails.projectname= 2. (SELECT projectid FROM project WHERE projectdetails.projectname = project.projectname);Run the select statement and you can identify ..as the error defines the select statement is getting more than 1 value.3. Check the 'Where' conditions each and every line, by commenting line by line and running the query.You can resolve step by step, be confident bro we are with you.I suggest you before updating all the rows, just try to update one row and test, if its working fine and you can update all.Regards,Afzal.

  • RELEVANCY SCORE 2.87

    DB:2.87:Duplicate Values In An Internal Table ds



    Hi,

    Is it possible to delete records having Duplicate Values in a field, in an Internal Table, but in the situation when the Internal Table is NOT sorted on that field ?

    Regards,

    Pankaj.

  • RELEVANCY SCORE 2.87

    DB:2.87:Duplicate Values In Search Help 73



    Hi,

    I have to create a search for filed which does not have a check table/value table. when i create search for this field, i am getting duplicate values. Is there anay way to avoid duplicate values when you press F4.

    Thanks

    Rajavardhana Reddy

    DB:2.87:Duplicate Values In Search Help 73


    HI,

    YOu can create a Search Help Exit and process your data as per your requirement.

    Clikc the below link to know more on Search Help Exit.

    bhttp://help.sap.com/saphelp_47x200/helpdata/en/cf/21ee52446011d189700000e8322d00/content.htm/b

    Regards,

    Vara

  • RELEVANCY SCORE 2.87

    DB:2.87:Deleting Duplicate Records Based On Condition 9f


    Hi All,

    I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records.

    The customer_id or contract_number data in the test table are not unique.

    Sample Table Script:

    CREATE TABLE TEST
    (
    CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
    CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
    CREATION_DATE DATE NOT NULL
    );Sample data script

    insert into TEST values('ANTON','123',sysdate-9);
    insert into TEST values('ANTON','123',sysdate-9);
    insert into TEST values('ANTON','123',sysdate-9);
    insert into TEST values('RAMKI','223',sysdate-2);
    insert into TEST values('RAMKI','225',sysdate-2);
    insert into TEST values('RAMKI','225',sysdate);
    insert into TEST values('PUGI','235',sysdate);
    insert into TEST values('ANTON','123',sysdate);
    insert into TEST values('ANTON','123',sysdate);
    insert into TEST values('ANTON','123',sysdate);
    insert into TEST values('ANTON','143',sysdate);
    insert into TEST values('ANTON','143',sysdate);
    insert into TEST values('ANTON','143',sysdate);
    insert into TEST values('RAMKI','223',sysdate);
    insert into TEST values('RAMKI','225',sysdate);
    insert into TEST values('RAMKI','225',sysdate);
    insert into TEST values('PUGI','235',sysdate);I have played around with some scripts, still no success, This displayed results somewhat near to what i am expecting.

    select * from
    test
    where rowid in
    (select rowid from
    (select
    rowid,
    row_number()
    over
    (partition by customer_id order by customer_id) dup
    from test)
    where dup 3);

    DB:2.87:Deleting Duplicate Records Based On Condition 9f

    Thanks, you understood me well, this is what i had in mind, thanks a lot, this worked.

  • RELEVANCY SCORE 2.86

    DB:2.86:Powerpivot Table Fails To Update If Source Excel Table Is Filtered dk


    I have a PowerPivot table based off an Excel table that includes data from 2010through to 2013. The Powerpivot table will update ifthe Excel table has no filters on. However, the Powerpivot table fails to update if the Excel table
    is filtered by date and does not include Feb. 2012. The Powerpivot table will update if the Excel table is filtered for any combination of dates as long as Feb. 2012 is included. If Feb. 2012 is not included in the filter criteria I get the following
    message when trying to update the Powerpivot table:
    Unable to Update
    The following tables could not be updated: Concerns
    Linked table might not be updated because of conflicting data types or duplicate values in a column. Ensure theat the data type of the selected content matches the data type of the target columns. If the table is used in a relationship as a lookup
    table, ensure that there are not duplicate values in the column that is specified for that relationship.
    I'm not finding this message helpful because I can't understand why things work if data from Feb 2012 is included in my filter criteria. Everything was working fine until the middle of Dec. 2012. How can I find and fix the cause of this error?

    DB:2.86:Powerpivot Table Fails To Update If Source Excel Table Is Filtered dk

    This is not a SmallBasic question.
    You should ask this in the forum where you asked other SQL questions.Jan [ WhTurner ] The Netherlands

  • RELEVANCY SCORE 2.86

    DB:2.86:Finding One Of Two Or Mor Duplicate Records x7


    Given the following table sample can someone explain how to find
    only those records that have duplicate values in field value1 and select the duplicate that contains contains the highest value in value2?
    id value1 value2 value3
    1 990 123 456
    2 333 898 777
    3 444 444 232
    4 990 645 888
    5 990 453 233

    DB:2.86:Finding One Of Two Or Mor Duplicate Records x7


    ;with cte as (select *, row_number() over (partition by Value1 order by Value2 desc) as Rn,
    count(*) over (partition by Value1) as cntDups
    FROM Results)

    select * from cte where cntDups 1 and Rn = 1

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

  • RELEVANCY SCORE 2.86

    DB:2.86:Duplicate Values fx


    I am inserting data from one table to another table but there are duplicate values and I am getting an error. Can anyone tell
    me how I can handle this in an exception that ignore 2nd row when duplicate value. Or can have the cursor itself to get one
    record.

    Thanks in advance.

    DB:2.86:Duplicate Values fx

    Competition: how many different ways are thee to solve this problem?

    You could disable the unique constraint, bring over all the rows, then re-enable the constraint using the EXCEPTIONS INTO clause and delete the recalcitrant rows.

    Cheers, APC

  • RELEVANCY SCORE 2.86

    DB:2.86:How To Find Out Duplicate Name In Table? s1


    Please tell me

    How to find out Duplicate name in Table

    DB:2.86:How To Find Out Duplicate Name In Table? s1

    Do you mean:

    select name from table
    group by name having count(*) 1?

    Regards
    Peter

  • RELEVANCY SCORE 2.85

    DB:2.85:How To Avoid Duplicate Value In List Collector sz



    I need to remove the Duplicate value from list collector.

    Since the reference table contains some duplicate values.

    DB:2.85:How To Avoid Duplicate Value In List Collector sz


    Hi Shanmuga,

    You can use the add filters in the list collector.

    http://wiki.servicenow.com/index.php?title=Variable_Types

  • RELEVANCY SCORE 2.85

    DB:2.85:Flatfile m3


    Hai, i have falt file. i am trying for remove the duplicate vales. Distnct values send to one destination table and duplicate values send to another destination table.
    i was trying with aggregate transformation. i didn't send duplicate values to another destination.
    plz let me know how can we achieve this

    DB:2.85:Flatfile m3

    with the idea of Todd
    http://toddmcdermid.blogspot.com/2009/01/eliminating-duplicate-primary-keys-in.html
    and maybe some changes you can get your answer.Sincerely SH --MCTS 2005 & 2008 MCITP 2008 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s)

  • RELEVANCY SCORE 2.85

    DB:2.85:Selection Based On Other Values In Table f7



    Hello,

    I have a table that looks like this:

    DB:2.85:Selection Based On Other Values In Table f7


    Looks like it's working! Thanks! I added a field that checks if there's dublicates of the TOM date in order to only mark those as the once where TYPE = T is also used.

    Thanks a million!

    //A.

  • RELEVANCY SCORE 2.85

    DB:2.85:Duplicate Records 7k



    I'm loading into QV two spreadsheets where I want to identify duplicates. Column names are similar to

    Table 1

    Date

    Site

    Breach

    Score

    Table2

    Date

    Site

    Reason

    Value

    I want to find all the duplicate records where all values match, so there will be some text in the breach and reason colunm that should match, same with date etc. Each table has about 200 records.

    Anyone got any suggestions. Thanks

    DB:2.85:Duplicate Records 7k


    I'm loading into QV two spreadsheets where I want to identify duplicates. Column names are similar to

    Table 1

    Date

    Site

    Breach

    Score

    Table2

    Date

    Site

    Reason

    Value

    I want to find all the duplicate records where all values match, so there will be some text in the breach and reason colunm that should match, same with date etc. Each table has about 200 records.

    Anyone got any suggestions. Thanks

  • RELEVANCY SCORE 2.85

    DB:2.85:Select_Minimum_Value_From_A_Comma_Separated,List z7


    Hi all,

    I have a requirement wherein i catch values based on certain calculations in different variables and then i want to find minimum value out of these values (in the variables)...

    ever-since min() sql function compels me to insert these values in a temporary table and then use this function. can i do it without inserting the values in a temporary table thing

    regards
    shabir

    DB:2.85:Select_Minimum_Value_From_A_Comma_Separated,List z7

    Given the thread title it's not clear if you're dealing with this :
    v_string := '9,3,1,5' ;and wanting to find the min value (i.e. "1") - in this case see Etbin's suggestion.

    Or, you're actually having the values in separate variables :
    v1 := 1 ;
    v2 := 9 ;
    v3 := 5 ;in this case the LEAST function would give you the min :
    v_min := least(v1, v2, v3);

  • RELEVANCY SCORE 2.84

    DB:2.84:How Can I Find Duplicate Rows In A Field Of A Table 77


    hi all,

    how can i able to find duplicate values in a field of a table????

    Regards

    DB:2.84:How Can I Find Duplicate Rows In A Field Of A Table 77

    test@ORA92
    test@ORA92 with x as (
    2 select 1 as p, 'a' as q from dual union all
    3 select 1 as p, 'b' as q from dual union all
    4 select 2 as p, 'c' as q from dual union all
    5 select 3 as p, 'd' as q from dual union all
    6 select 3 as p, 'e' as q from dual union all
    7 select 3 as p, 'd' as q from dual
    8 )
    9 select p, q,
    10 case row_number() over (partition by p order by q)
    11 when 1 then null
    12 else 'Duplicate value: '||p||' of column p'
    13 end as msg
    14 from x;

    P Q MSG
    ---------- - ---------------------------------------------------------------------
    1 a
    1 b Duplicate value: 1 of column p
    2 c
    3 d
    3 d Duplicate value: 3 of column p
    3 e Duplicate value: 3 of column p

    6 rows selected.

    test@ORA92
    test@ORA92
    test@ORA92cheers,
    pratz

  • RELEVANCY SCORE 2.84

    DB:2.84:Remove Duplicate Values mc



    I have duplicate values in my table, and I am trying to remove them, I have used Distinct load on both the table and the Generic Load Table.

  • RELEVANCY SCORE 2.84

    DB:2.84:Streams Replication With Duplicate Data xz


    Hi

    Oracle 11.1.0.7

    I would like to know , is there a workaround to configure Streams where tables have duplicate values (No primary Key).

    Also i want to replicate data from from source database based on certain condition , for example :

    In scott.emp table there is an INSERT/UPDATE/DELETE :

    I want to replicate all the data except those records where SALARY is 1000 . So suppose 2 rows were inserted in which one row has salary 1001 , this should be replicated and second row has salary 1000 , this should not be replicated.

    Thanks

    DB:2.84:Streams Replication With Duplicate Data xz

    Please , can somebody share views if its possible ?

  • RELEVANCY SCORE 2.83

    DB:2.83:How To Update Duplicate Row From Table cf


    Hi,

    how to update duplicate row from table?

    First to find duplicate row then update duplicate row with no to that duplicate row in oracle.

    can you give me suggestion on it?

    Thanks in advance.

    your early response is appreciated...

    DB:2.83:How To Update Duplicate Row From Table cf

    Hi,

    You can use the following method to find the duplicate records and also update same using the update script ganarate by the same sql statement.

    assume you have 3 column table and use this one.

    select
    col1,
    col2,
    col3,
    max(rowid),
    *('update sample_table set col3='no' where rowid='||max(rowid)||';') as update_dup_rec*
    from sample_table
    group by col1,col2,col3
    having count(*) 1

    Cheers,
    Kanchana.

  • RELEVANCY SCORE 2.83

    DB:2.83:Duplicates d8


    How do I check duplicate values in a spatial table.

    Table foo
    column id number;
    column x mdsys.sdo_geometry (duplicate polygons here)

    cheers
    Matt

    DB:2.83:Duplicates d8

    Hi Matt,

    A single geometry with external polygons that overlap is not
    legal. Oracle 9i release 2 will have new validation functions
    that catch this and return the context of where the polygons
    overlap.

    If you want to know if multiple polygons in the same table are
    equal (or equivalent, they may have different start points or
    there may be slight mismatches that are smaller than the
    tolerance values) then sdo_geom.relate with the equal mask will
    tell you if the geometries are the same.

    Hope this helps,

    dan

  • RELEVANCY SCORE 2.83

    DB:2.83:Duplicate Column Value xf


    How to find the duplicate column value in a table using a sql query?

    Thank You.

    KK

    DB:2.83:Duplicate Column Value xf

    It was easy and quick. Thanks a lot!!

  • RELEVANCY SCORE 2.82

    DB:2.82:Select Distinct Values 1f


    I have a table with some columns like this .
    companyid userid csa
    123a abc 1
    123a abc1
    123a efg 0
    245a lkm 1
    245a pqr 1
    245a hgk 0
    so if you see the data for a company we have some duplicate users who have csa enabled. but here i want to find out company with more than one user having csa
    enabled. if you need anything can help. but basically i need to find out this but i couldn't group them cos we have duplicate values.
    urgent help is appreciated. thanks

    DB:2.82:Select Distinct Values 1f

    Another method

    DECLARE @t TABLE(companyid VARCHAR(20),userid VARCHAR(20),csa BIT)
    INSERT INTO @t
    SELECT 123a,abc,1 UNION ALL
    SELECT 123a,abc,1 UNION ALL
    SELECT 123a,efg,0 UNION ALL
    SELECT 245a,lkm,1 UNION ALL
    SELECT 245a,pqr,1 UNION ALL
    SELECT 245a,hgk,0

    SELECT *
    FROM (SELECT companyID,COUNT(DISTINCT userid) AS C
    FROM @t
    WHERE csa = 1
    GROUP BY companyid) T
    WHERE C 1

  • RELEVANCY SCORE 2.82

    DB:2.82:Find Duplicates From Different Tables j3


    I need to write a sql query to find out in which table
    holds the duplicate data.
    Duplicate : AAA company should be present(mapped) only once with XXX Partner. If it is repeated then would be considered
    as duplicate
    Please help in writing this query

    Table A
    Table B
    Table C
    company Partners company Partners
    company Partners
    AAA XXXAAA
    XXX
    AAA XXX
    BBB YYY BBB
    YYY
    BBB YYY
    AAA XXXX (duplicate Data)
    Expected o/p :
    table A contains duplicate data , or Table B contains Duplicate Data ETc...

    DB:2.82:Find Duplicates From Different Tables j3

    thanks
    Jingyang Li
    yes .. i wanted to compare all the tables and then pick up which table has duplicate data ..

  • RELEVANCY SCORE 2.82

    DB:2.82:Validation To Compare A Value That Exist. a1


    Hi Everybody,

    I am using JDeveloper11.1.1.4. I want to implement a validation based on the comparison.

    I have an editable table in a .jspx file. This table is created by making use of an Entity based view object. And, this view object has some conditions in its where clause.

    Now since my page has editable table, I want a validation to happen in such a way that the user does not duplicate a value while editing/creating a new record in the form. Please note we cannot set Unique constraint because the table can have duplicate values. And hence, only the users context should not have a duplicate values. In short, the column in the table in which the user currently in, should not have the duplicate values.

    I tried with comparison operator with query result but came to know that we cannot pass parameters to the query.

    Can somebody suggest me what the best way of doing this?

    Regards,
    V. Praveen

    DB:2.82:Validation To Compare A Value That Exist. a1

    Praveen,

    What do you mean by the "user's context?" It would seem to me that the declarative way of doing this would be a proper unique index/unique constraint in the database that takes into account the "user's context."

    John

  • RELEVANCY SCORE 2.82

    DB:2.82:Showing Similar Items Based On An External List k7


    I have an External list based on a SQL Server table A . Now there is a second SQL Table B which may contain similar records to table A. So if there is record in table A which exists in table B, the user should be able to go to the external list
    and just change the status column of that record to Duplicate and this will update the status column in table A for that record.
    Now for the user to find the duplicate record , I need to somehow display all the similar records from Table B along with the record in question from table A.

    Any help is much appreciated.

    DB:2.82:Showing Similar Items Based On An External List k7

    Wow! Great find. The only challenge is that I need to edit in Table A Web part each item. This is possible if the table A was List , but in this case it is a web part. Is editing each row possible in Web part.

  • RELEVANCY SCORE 2.82

    DB:2.82:Re: Update Values In Table 2 Based On Condition Of Values In Table1 9k


    Then you should write trigger on leave table not leaveentitle

    DB:2.82:Re: Update Values In Table 2 Based On Condition Of Values In Table1 9k

    Awaiting help response. Any body there!!!

    Ok here is the thing Once again:

    I have two tables as

    Table LEAVE
    Column Type Null Description
    APP_NO Number(6,0) Not Null PK Leave Application Number
    ECN Number(6,0) Not Null FK Employee Code Number
    APP_Date Date Not Null Date of Application
    From_Date Date Not Null Date from which the leave starts
    TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
    NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
    LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
    Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
    Remark VARCHAR2(100) Nullable Reason to be put if status is rejected

    Table LEAVEENTITLE
    Column Type Null Description
    ECN Number(6,0) Not Null PK Employee Code Number
    SL_ENTITLED Number(2,0) Not Null No of Sick Leave (SL) entitled to the employee
    SL_USED Number(2,0) Nullable No of SL used by the employee
    SL_UNUSED Number(2,0) Not Null =SL_ENTITLED - SL_USED
    CL_ENTITLED Number(2,0) Not Null No of Casual Leave (CL) entitled to the employee
    CL_USED Number(2,0) Nullable No of CL used by the employee
    CL_UNUSED Number(2,0) Not Null =CL_ENTITLED - CL_USED
    LTA_ENTITLED Number(2,0) Not Null No of Leave Travel(LTA) entitled to the employee
    LTA_USED Number(2,0) Nullable No of LTA used by the employee
    LTA_UNUSED Number(2,0) Not Null =LTA_ENTITLED - LTA_USED
    LWP Number(2,0) Nullable No of Leave Without Pay

    What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the USED values of the corresponding LEAVETYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.

    For example if 3 rows are inserted in the LEAVES table as
    INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
    INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
    INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');

    Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'

    I tried with the following trigger, but is compiling with a warning (not showing what the warning is)

    CREATE OR REPLACE TRIGGER leaveentitle
    AFTER INSERT ON LEAVES
    FOR EACH ROW
    BEGIN
    UPDATE LEAVEENTITLE LVE
    SET LVE.SL_USED = SL_USED+(CASE
    WHEN :NEW.LEAVE_TYPE = 'SL' NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    SL_UNUSED=SL_ENTITLED - SL_USED
    ELSE 0
    END),

    LVE.CL_USED = CL_USED + (CASE
    WHEN :NEW.LEAVE_TYPE = 'CL' NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    CL_UNUSED=CL_ENTITLED - CL_USED
    ELSE 0
    END),

    LVE.LTA_USED = LTA_USED + (CASE
    WHEN :NEW.LEAVE_TYPE = 'LTA' NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    LTA_UNUSED=LTA_ENTITLED - LTA_USED
    ELSE 0
    END),

    LVE.LWP = LWP + (CASE
    WHEN :NEW.LEAVE_TYPE = 'LWP' NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    ELSE 0
    END),

    WHERE LVE.ECN=:NEW.ECN;
    END;

  • RELEVANCY SCORE 2.81

    DB:2.81:Duplicate Rows Based Upon Condition 8k


    Oracle 11.2.0.1Windowscreate table regsales (billno number,itemno number,paymode varchar2(10));insert into regsales values (12345,10,'cash');insert into regsales values (12345,11,'cash');insert into regsales values (12346,11,'cash');insert into regsales values (12347,10,'credit');insert into regsales values (12348,14,'cash');insert into regsales values (12348,15,'cash');insert into regsales values (12345,12,'cash');insert into regsales values (12349,10,'cash');insert into regsales values (12345,10,'credit');insert into regsales values (12350,11,'cash');insert into regsales values (12351,12,'cash');insert into regsales values (12352,11,'cash');insert into regsales values (12350,11,'credit');Required Output please : BILLNO ITEMNO PAYMODE
    ---------- ---------- ----------
    12345 11 cash
    12345 11 credit
    12350 10 cash
    12350 10 credit
    i.e. in which same billnos same itemno has been sold in cash and credit.Thank you.

    DB:2.81:Duplicate Rows Based Upon Condition 8k

    Thank you frank sir. It is what I was looking. I just did little correction; with where cnt = 2, because as you said, there are chances that there can be more than 2 paymodes in the table.Thanks once again.

  • RELEVANCY SCORE 2.81

    DB:2.81:Re: How To Find Duplicate Records In A Table f3


    can u please expain count(1)1

    DB:2.81:Re: How To Find Duplicate Records In A Table f3

    Hi,

    Assuming that you are connecting ( APEX and sql*plus) to the same DB version the query will run in both.
    The only time it won't run is if APEX and sql*plus connect to different versions and you use commands that are version specific
    like regexp_count for 11g, other regular expressions for 10g/11g.

    In your case chr(39) is equal to apostrophe (')

    here are examples on its use

    SQL with t as -- "sample data"
    2 (select 'd''silva' name_ from dual union all
    3 select 'john' name_ from dual union all
    4 select 'd''abo' name_ from dual union all
    5 select 'a''abo' name_ from dual
    6 ) -- "end sample data"
    7 select name_
    8 from t
    9 where name_ like 'd'||chr(39)||'%' -- equal to look for rows that start with d and an apostrophe (d')
    10 /

    NAME_
    -------
    d'silva
    d'abo

    SQL
    SQL with t as -- "sample data"
    2 (select 'd''silva' name_ from dual union all
    3 select 'john' name_ from dual union all
    4 select 'd''abo' name_ from dual union all
    5 select 'a''abo' name_ from dual
    6 ) -- "end sample data"
    7 select name_
    8 from t
    9 where name_ like '%'||chr(39)||'%' -- equal to look for rows that has an apostrophe (') anywhere in the string
    10 /

    NAME_
    -------
    d'silva
    d'abo
    a'abo

    SQL
    SQL with t as -- "sample data"
    2 (select 'd''silva' name_ from dual union all
    3 select 'john' name_ from dual union all
    4 select 'd''abo' name_ from dual union all
    5 select 'xx''abo' name_ from dual union all
    6 select 'a''abo' name_ from dual
    7 ) -- "end sample data"
    8 select name_
    9 from t
    10 where name_ like '_'||chr(39)||'%' -- equal to look for rows where 2nd character is an apostrophe (_')
    11 /

    NAME_
    -------
    d'silva
    d'abo
    a'abo

  • RELEVANCY SCORE 2.81

    DB:2.81:Duplicate Values Problem. j8



    Hello

    The problem is i want to show vendor master table ....but the vendor ID should not repeat obviously......

    In that case i can do that by supressing null speciality values but the sme vendor Id really do not have this

    speciality they also get suppressed.. i dont want that please hlp how do i tackle this....

    Find the sample qvw attached.,..

    Thanks and Regards,

    Rohit

  • RELEVANCY SCORE 2.81

    DB:2.81:How To Find Same Records From Internal Table? kx



    Hi Experts,

    I have to compare a key field with other three fields. If the values of three fields are same for more then one key field then that should be marked as duplicate. means I have to check each field with every record of the table.

    Can anybody help me to find?

    Thanks a ton in advance...

  • RELEVANCY SCORE 2.81

    DB:2.81:Need Help: Duplicate Records Based On Particular Columns jd


    Hi All,

    I need to select from a table some records that are duplicate only based on specific columns. For example, if I have this table:

    10 23 45 SA Paul
    10 23 45 SA Jean
    11 21 54 SA Lisa
    45 87 22 CA John
    45 87 22 CA Jake
    45 87 22 CA Phil

    The first 2 records have the same values in their first 4 columns therefore they are duplicate even if the entire record is not really a duplicate. In this case, I need only the first record like so:

    10 23 45 SA Paul
    11 21 54 SA Lisa
    45 87 22 CA John

    Note also that the same is true with the last 3 records.

    Can anyone please help me?

    Thanks.

    DB:2.81:Need Help: Duplicate Records Based On Particular Columns jd

    In that case, you don't need of a subquery :
    SQL with t as (
    2 select 1 c1,2 c2, 3 c3, 'a' c4 from dual union all
    3 select 1,2,3,'b' from dual union all
    4 select 1,2,3,'a' from dual union all
    5 select 8,7,6,'X' from dual union all
    6 select 9,8,7,'Y' from dual union all
    7 select 9,8,7,'A' from dual union all
    8 select 6,6,6,'X' from dual union all
    9 select 6,6,6,'Y' from dual union all
    10 select 6,6,6,'W' from dual)
    11 select c1, c2, c3, min(c4)
    12 from t
    13 group by c1, c2, c3;

    C1 C2 C3 M
    ---------- ---------- ---------- -
    8 7 6 X
    6 6 6 W
    1 2 3 a
    9 8 7 A

    SQL Nicolas.

  • RELEVANCY SCORE 2.81

    DB:2.81:Query To Find The Alternate Row From A Table And Also Query kf


    Give me the query that fetches

    1. alternate row from a table

    and also query

    2. that constraints a table to contain duplicates with Unique values.i.e. that table has already duplicate values. we need to alter the table add a constraint that the column no way receives such duplicates here after.

    Pls Reply as these questitions were framed in an interview to my friend.

  • RELEVANCY SCORE 2.80

    DB:2.80:Deleting From A Table Based On Values In A Second Table kc


    Is it possible to DELETE from (or for that matter do an UPDATE to) a table based on values in another table? I have gone through the online documentation but can't seem to find anything. I'm trying to delete rows from table A where A.field1 = B.field1 and B.field2 = 'X'. (B being the second table)

    DB:2.80:Deleting From A Table Based On Values In A Second Table kc

    It is done using subqueries in the where clause.

    delete from A
    where A.field1 in (select B.field1 from B where B.field2 = 'X');

    delete from A
    where exists (select 'x' from B where B.field1 = A.field1 and B.field2 = 'X');

    delete from A
    where A.rowid in (select A.ROWID from from A, B where B.field1 = A.field1 and B.field2 = 'X');

    And many other varieties. Eg. more specialised:
    delete from A
    where A.txdate (select B.prune_date FROM B where B.field1 = A.field1 and B.field2 = 'X')
    and A.txstate in (select S.txstate from S where S.prodlass=a.prodclass and s.deletable='Y');

  • RELEVANCY SCORE 2.80

    DB:2.80:Show Distinct String Values In Straight Table ? m9



    I have string based column field with many duplicate values. I wish to show only the Distinct or unique values. sounds simple or is it?

    I tried Concat(DISTINCT Name) aslo Only() none have worked. Please help.

    The reason why I need to use a Straight table is the need to show dates side by side.

    Thank you all.

    DB:2.80:Show Distinct String Values In Straight Table ? m9


    The reason I forgot to mention for having a table is dates are associated with the Name.

    QV doesn't seem to have many string functions that handle Tables well.

  • RELEVANCY SCORE 2.80

    DB:2.80:Pivot Table On Sorted Data mm


    Hi!

    I have a data sheet which gets updated regularly. I run a macro on it to sort the data, and then remove duplicates.

    based on this sheet, i am trying to create a pivot table. however, the pivot table is picking up the duplicate values as well.
    i checked, but these duplicate values are not visible in the main data sheet.

    Please help!!!

    DB:2.80:Pivot Table On Sorted Data mm

    Are you sure the source of the pivot table is correct?

    Hard to diagnose without seeing it.

    Can you upload a copy to a website (skydrive, dropbox or equivalent) so we can see it, and tell us what you are seeing in the pivot table that you think is wrong?

  • RELEVANCY SCORE 2.80

    DB:2.80:Distinct Values 9m



    Hello Community members,

    I have a colum in my straight table in which the values are repeating , how to avoid the duplicate values. Can anyone please suggest me

    Regards,

    Ramesh

    DB:2.80:Distinct Values 9m


    If they are repeating then the records are not unique. This can happen when for example you format a date as a month. The underlying date values are still dates, but the display format is month. That's when you see repeating month values because the actual values are still distinct dates. Check if that's the case in your document. You may have to create extra fields in the script, for example month(MyDate) as Month instead of date(MyDate, 'MMM') as Month. The latter only changes the display format, the former creates a new Month field with distinct month values.

  • RELEVANCY SCORE 2.80

    DB:2.80:Inconsistent Counting Results In Script And Export zd



    Hello. I am observing inconsistent--relative to my expectations--results around counting of unique/distinct values in a field. I need to verify that a table contains no duplicates in a single field. This is in preparation to denormalize/flatten data into a single table. Unfortunately, it will require a great deal of effort to remove prioritary data in order to post a .qvw here. I'm hopeful that my description below is sufficent to explain and diagnose what's going on. I want to know whether I'm validating uniquenss incorrectly or if I've misunderstood QV's handling of counts and uniques.

    My expectation is that there should be no duplicate values in a specific field; I have already done all necessary scrubbing. I use NoOfRows to find values in my scrubbed table:

    LET vCountPrices = NOOFROWS('Pricing');

    This tells me there are 743,846 rows (vCountPrices= 743,846). These should be already be unique on the field in question, but I want to validate. To validate that there are no uniques, I first tried to pull all distinct values into their own table and find out how many there are:

    Count_ID_x_Price:

    NOCONCATENATE

    LOAD

    DISTINCT [ID_x_Price] AS [Unique]

    RESIDENT Pricing

    ;

    LET vUniquePrices = NOOFROWS('Count_ID_x_Price');

    This results in vUniquePrices = 738,862. I already have a problem! 738,862 != 743,846, so the raw numbers are telling me that I have duplicate values in my original table! Interestingly, when I create a table box on Pricing and export to .csv, I get 738,862 lines. This tells me that NOOFROWS('Pricing') is giving inaccurate data... the QV formula seems to reflect more rows than are actually there! When I put a statistics box on this field, I get text and total count of 743,846; the null and missing counts are both 0. Can anyone shed light on what's going on here?

    As a sanity check, I tried a different way of counting Pricing using fieldvaluecount. This returns 738,862 (v2ndCountFV = 738,862).

    LET v2ndCountFV = FieldValueCount('ID_x_Price');

    Based on all of this observed behavior, I can be fairly confident that there are 738,862 records--all unique--in my Pricing table. I think the 743,846 is a false value. I'm trying to understand what/why I get that bad value. Help!

    DB:2.80:Inconsistent Counting Results In Script And Export zd

    My expectation is that there should be no duplicate values in a specific field; I have already done all necessary scrubbing.

    What have you done to ensure this?

    If ID_x_Price is not a key field, you can create a list box of that field, check show frequency options in properties and sort by frequency desc. This may give you an hint, which values are duplicated (and then maybe also why).

  • RELEVANCY SCORE 2.79

    DB:2.79:Query To Find Duplicate Rows... k8


    Let's say a table has 5 columns and some duplicate rows. How would one write a query to find these duplicate rows (in order to delete them later)?

    Thanks.

    DB:2.79:Query To Find Duplicate Rows... k8

    Thanks. As usual, you guys came through for me again!

  • RELEVANCY SCORE 2.79

    DB:2.79:Finding Duplicate Rows cp


    i have a table emp with column emp_no
    I want to find out all the values in this field which has values such as
    34567 and 134567
    another ex. 67453 and 167453 i.e all digits except the first digit is different
    How can i get these values from the table

    DB:2.79:Finding Duplicate Rows cp

    Replying to Hoek

    The first digit can be anything.....the rest of the digits are of equal length

    Well Franks query helped me alot...Thankyou everyone for replying

  • RELEVANCY SCORE 2.79

    DB:2.79:Re: How To Restrict The Duplicate Values In Lov Column Of Vo Based Adv Table ck


    Sombit,

    When there is a empty row and you are trying to save data then method "checkRespId" seems returning "No" and while saving empty row in DB may be you are getting the error.

    regards,
    Anand

    DB:2.79:Re: How To Restrict The Duplicate Values In Lov Column Of Vo Based Adv Table ck

    Sombit

    Your code shows you are getting reqidnumber from
    reqidnumber = new Number(Integer.parseInt(reqid));can you post the code you are using to get reqid that you are passing as int to it.
    After taking VO here
    OAViewObject vo = (OAViewObject)findViewObject("RespLineVO1");Fire rest of code if it is null like

    if (vo!=null)
    {
    //your code here
    }

    Thanks
    AJ

  • RELEVANCY SCORE 2.79

    DB:2.79:How To Compare Duplicates On Text Field Of Size (Char (2000)) 3x


    I have a table with many columns . I need to remove duplicates from the table and my only column which I can use to compare two rows is a text field . So I need a query to compare any duplicates in this tables based on that text field column. That text field is a desciptive column with size varchar greater than 2000 char .

    eg: Table A (col1 number , col2 varchar(2000)) I can compare two duplicate values based only on col2 value .
    Thanks

    Edited by: user 1778 on Feb 9, 2009 2:20 PM

    DB:2.79:How To Compare Duplicates On Text Field Of Size (Char (2000)) 3x

    I cannot give you sample data . But can provide you an example

    colA (datetime) , colB (char)
    timeX AA
    timeY BB
    timeZ AA
    timeX CC
    timeA DD
    timeB EE
    timeA DD

    My situation is such that I am trying to figure out the duplicates based on datetime. This field is updated in such a way that it records the time the moment someone in my XYZ application enters the submit button in the front end application . The submit button updates the colA , datetime and colB , a descriptive column . I can only catch hold of duplicates based on the descriptive column as somehow if someone submits once sometimes its getting updated twice or more than twice resulting in multiple duplicate rows. So only my colB will clearly show me the duplicates and not colA. There are several other colukmns in my table but cannot be used for checking the duplicates .
    Thanks

    Edited by: user 1778 on Feb 11, 2009 7:50 AM

  • RELEVANCY SCORE 2.79

    DB:2.79:Delete Last Record In Plsql Table 9d


    TYPE r_LOOPElement IS RECORD (TermID NUMBER
    );

    TYPE t_LOOPType IS TABLE OF r_LOOPElement INDEX BY BINARY_INTEGER;
    i_CustomerLoop t_LOOPType ;

    i_CustomerLoop(1).TermID=1;
    i_CustomerLoop(2).TermID=2;
    i_CustomerLoop(3).TermID=3;based on some conditions, i need to delete the last record from i_CustomerLoop

    whichi s the best way to do it. because i will sometimes have duplicate values for TermID in this plsql table.

    DB:2.79:Delete Last Record In Plsql Table 9d

    Hi,

    See [Deleting Collection Elements (DELETE Method)|http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/collections.htm#CJAFGFIG]

    Regards,

  • RELEVANCY SCORE 2.79

    DB:2.79:How Do I Delete Duplicates Which Are Based On 1 Field While Remain The First Duplicate. pp


    the table have about 10 fileds but only 1field that contains duplicate, the rest are of different values. How do I delete duplicated rows based only on this 1 field while leaving the 1 first duplicate untouched.

  • RELEVANCY SCORE 2.79

    DB:2.79:How To Find Duplicate Rows In Flat File ? kj


    In the FLAT FILE source, I have to find the duplicate rows based on the two fields say, bill number  invoice date.
     
    The rows within flat file has like bill number which is duplicated on the same invoice date.
     
    If duplicate rows found then move the duplicate rows into another Flat File.
     
    If not found then move the rows into Sql Server Table.
     
    Pls provide the solution. Thank you

    DB:2.79:How To Find Duplicate Rows In Flat File ? kj


    is there any direct solution by using any component within SSIS ?

    Here is an example of redirecting duplicates with a
    Script Component. In this example the rows where deduplicated on the first three columns:
    Please mark the post as answered if it answers your question | My SSIS Blog:
    http://microsoft-ssis.blogspot.com

  • RELEVANCY SCORE 2.78

    DB:2.78:Find Duplicate Values jf


    Hello,
    I have a table that has 4839 rows, 4807 of which are distinct values in column x. I want to see just the duplicate values
    (ie, the 32 rows)

    DB:2.78:Find Duplicate Values jf

    If your table consists of that only one column x, then u can use any of the below queries, all will give same kind of performance...

    ;with cte as
    (
    select x,ROW_NUMBER() over (partition by x order by (select 1)) as rn from tablename
    )
    select * from cte where rn 1

    ;with cte as
    (
    select x,ROW_NUMBER() over (partition by x order by x) as rn from tablename
    )
    select * from cte where rn 1

    ;with cte as
    (
    select x,ROW_NUMBER() over (partition by x order by newid()) as rn from tablename
    )
    select * from cte where rn 1

    IF you have any other column say y, which decides the Order of the table rows, if u wants to treat first value as unique and other values as duplicates, then use the below query

    ;with cte as
    (
    select x,ROW_NUMBER() over (partition by x order by y) as rn from tablename
    )
    select * from cte where rn 1

  • RELEVANCY SCORE 2.78

    DB:2.78:Return Only Duplicate From A Column 1z


    Hello can some one help to return only the duplicate values from a column of given table. Thanx

    DB:2.78:Return Only Duplicate From A Column 1z

    try something as

    select table_name, count(*)
    from cat
    where table_type = 'TABLE'
    group by table_name
    having count(*) 1

    try to identify something unique for the group of double records, p.e. the records that may have the same table name (example above).
    Just an example....

    Good luck!

    Rm.