Results 1 to 5 of 5

Thread: Need help

  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Unanswered: Need help

    I have a huge table record counts with 10 columns,

    SQL needed for below scenarios::

    I)Need to find out the nulls for each column

    II) duplicate checks for specfic columns


    Thanks
    Kumara swamy

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I)Need to find out the nulls for each column

    II) duplicate checks for specfic columns
    Do you want to find the existence of such records(answer is 'Yes' or 'No') or to count such records(answer is one count)?
    OR
    Do you want to list out such records?

  3. #3
    Join Date
    Feb 2012
    Posts
    4

    database

    Thanks for your reply,

    I need to know such column values having the nulls & duplicates

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all,
    If there are some unique columns or combination of columns,
    it would be better to add UNIQUE(or PRIMARY KEY) constraints for those columns or combination of columns,
    to prevent insertion of duplicated rows and to eliminate duplication check by users.

    A query to check I) and II) might be something like...
    Code:
    SELECT <columns to be checked duplication>
         , MAX( <col-1> ) AS <col-1>
         , MAX( <col-2> ) AS <col-2>
         ...
         , MAX( <col-n> ) AS <col-n>
         ,    CASE WHEN COUNT(*) > 1 THEN 'Dup ' ELSE '    ' END
           || CASE WHEN COUNT( <col-1> ) < COUNT(*) THEN ' 1' ELSE '  ' END
           || CASE WHEN COUNT( <col-2> ) < COUNT(*) THEN ' 2' ELSE '  ' END
           ...
           || CASE WHEN COUNT( <col-n> ) < COUNT(*) THEN ' n' ELSE '  ' END
           AS flags
     FROM  <your-table>
     GROUP BY
           <columns to be checked duplication>
     HAVING
           COUNT(*) > 0
       OR  COUNT( <col-1> ) < COUNT(*)
       OR  COUNT( <col-2> ) < COUNT(*)
       ...
       OR  COUNT( <col-n> ) < COUNT(*)
    ;
    Note: If duplication and nulls are both exist for a set of rows, values of columns might be inaccurate.

  5. #5
    Join Date
    Feb 2012
    Posts
    4

    need help - DB2

    I need to find out the combination of 1,2,3,4,5,6 fields cannot have duplicates, its huge table. What is the best way to find these?

Tags for this Thread

Posting Permissions

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