Results 1 to 4 of 4

Thread: Query Problem

  1. #1
    Join Date
    Jul 2003
    Posts
    11

    Unanswered: Query Problem

    Dear Friends,
    Iam running following Query:-

    select rtrim(txt_acct_oper_instrs1)+rtrim(txt_acct_oper_i nstrs2)+rtrim(txt_acct_oper_instrs3)+rtrim(txt_acc t_oper_instrs4) OperatingInstructions ,
    count(*) from ci_acct_oper_instrs
    where flg_mnt_status = 'A'
    group by rtrim(txt_acct_oper_instrs1)+rtrim(txt_acct_oper_i nstrs2)+rtrim(txt_acct_oper_instrs3)+rtrim(txt_acc t_oper_instrs4)
    having count(*) > 1
    order by count(*) desc

    and it given me following error :-

    Msg 414, Level 16, State 1:
    Server 'DPCSER', Line 1:
    The current query would generate a key size of 1020 for a work table. This exce
    eds the maximum allowable limit of 600.

    I want to delete duplicate rows from the table.

    Iam sending the attached table structure and index and datatypes.
    ************************************************** ******************
    create table ci_acct_oper_instrs
    (cod_acct_no ut_num_account NOT NULL
    ,txt_acct_oper_instrs1 char(255) NULL
    ,txt_acct_oper_instrs2 char(255) NULL
    ,txt_acct_oper_instrs3 char(255) NULL
    ,txt_acct_oper_instrs4 char(255) NULL
    ,dat_acct_oper_instrs ut_datetime NULL
    ,flg_mnt_status ut_flag_char NULL
    ,cod_mnt_action ut_flag_char NULL
    ,cod_last_mnt_makerid ut_userid NULL
    ,cod_last_mnt_chkrid ut_userid NULL
    ,dat_last_mnt ut_datetime NULL
    ,ctr_updat_srlno ut_int4 NULL
    )
    on onlineseg
    go

    INDEX
    ********
    clustered, unique located on onlineseg
    cod_acct_no, dat_acct_oper_instrs, flg_mnt_status


    DATATYPES
    **********
    execute sp_addtype ut_num_account, 'char(16)', 'nonull'
    go

    execute sp_addtype ut_datetime, 'datetime', 'nonull'
    go
    execute sp_addtype ut_flag_char, 'char(1)', 'nonull'
    go

    Kindly help me to remove duplicate rows without using group by

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Re: Query Problem

    Can you try a "SELECT distinct" ? Also, is there any specific reason why the fields are char(255) instead of varchar? rtrims are an overhead to queries.

  3. #3
    Join Date
    Jul 2003
    Posts
    11
    Thanks fore reply.
    I have acheived of getting the unique rows but the only problem is 'count(*).
    I don't know without group by how will i get count(*) .
    I bcp out table rows and created index with ignore_duplicate_row and then bcp in the data.
    but how to get count(*). Can we acheive it with char datatype only.

    Regards
    SUraj

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try it without concatenating the columns? --
    Code:
    select txt_acct_oper_instrs1
         , txt_acct_oper_instrs2
         , txt_acct_oper_instrs3
         , txt_acct_oper_instrs4 OperatingInstructions 
         , count(*)
      from ci_acct_oper_instrs
     where flg_mnt_status = 'A'
    group 
        by txt_acct_oper_instrs1
         , txt_acct_oper_instrs2
         , txt_acct_oper_instrs3
         , txt_acct_oper_instrs4
    having count(*) > 1 
    order 
        by count(*) desc
    rudy
    http://r937.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •