Troubleshooting
Problem
This document contains a method to determine which records exist more than once (or x times) in a file with SQL.
Resolving The Problem
When duplicate records exist, the following message are typically found in the joblog: MCH1811 and CPF5090 SQL7008. For a quick way to determine which records exist more than once (or x times) in a file, use the following IBM SQL/400 statement. The following can be used in Interactive SQL or in a Query Management Query:
SELECT ALL *
SELECT ALL *
FROM file T01
WHERE 1 < (SELECT count(*)
FROM file T02
WHERE T01.field = T02.field)
ORDER BY T01.field asc
This lists records where the specified field exists more than once. You can change the 1 in the first WHERE clause to another value if needed; for example, change 1 to 10 returns only records that occur more than 10 times. You can also test more than one field by adding an AND condition in the second WHERE clause and adding those fields to the ORDER BY, as in the following example.
SELECT ALL *
FROM file T01
WHERE 1 < (SELECT count(*)
FROM file T02
WHERE T01.field = T02.field
WHERE 1 < (SELECT count(*)
FROM file T02
WHERE T01.field = T02.field)
ORDER BY T01.field asc
This lists records where the specified field exists more than once. You can change the 1 in the first WHERE clause to another value if needed; for example, change 1 to 10 returns only records that occur more than 10 times. You can also test more than one field by adding an AND condition in the second WHERE clause and adding those fields to the ORDER BY, as in the following example.
SELECT ALL *
FROM file T01
WHERE 1 < (SELECT count(*)
FROM file T02
WHERE T01.field = T02.field
AND T01.field2 = T02.field2)
ORDER BY T01.field asc, T01.field2 asc
This example returns records where two fields are used to select duplicate records.
ORDER BY T01.field asc, T01.field2 asc
This example returns records where two fields are used to select duplicate records.
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i4eAAA","label":"IBM i Db2-\u003ESQL Examples \/ DB Examples \/ Misc how to"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
11084946
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
IBM i Db2->SQL Examples / DB Examples / Misc how to
Software version:
All Versions
Operating system(s):
IBM i
Document number:
642797
Modified date:
07 December 2024
UID
nas8N1018439
Manage My Notification Subscriptions