DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
bhoompallipraveen,
This is one way if eliminating dups from a table, while maintaining one row only using SQL. It is so much easier to use cursors in COBOL to perform this function.
The base table was created
| Code: |
CREATE TABLE XXXXX.EMPLOYEE
(LAST_NAME CHAR(20),
FIRST_NAME CHAR(20),
MI CHAR(1),
DEPT_NBR DEC(4)
)
IN DATABASE XXXXX;
|
The following, though not a single SQL, will eliminate the duplicate rows. And, if you must do it in SQL only, and without anything that can make the duplicate rows unique, this does work, though not pretty.
| Code: |
PAGE 1
***INPUT STATEMENT:
SELECT *
FROM EMPLOYEE
;
+-------------------------------------------------------------+
| LAST_NAME | FIRST_NAME | MI | DEPT_NBR |
+-------------------------------------------------------------+
1_| Smith | John | T | 1000 |
2_| Jones | John | L | 1200 |
3_| Smith | John | T | 1000 |
4_| Jones | David | S | 1000 |
5_| Smith | John | T | 1210 |
6_| Johnson | Roy | S | 2000 |
7_| Johnson | Roy | S | 2000 |
8_| Johnson | Roy | S | 2000 |
+-------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 8 ROW(S)
DECLARE GLOBAL TEMPORARY TABLE EMPLOYEE
(
LAST_NAME CHAR(20),
FIRST_NAME CHAR(20),
MI CHAR(1),
DEPT_NBR DEC(4)
)
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DECLARE SUCCESSFUL
PAGE 1
***INPUT STATEMENT:
INSERT INTO SESSION.EMPLOYEE
SELECT DISTINCT *
FROM EMPLOYEE A
WHERE EXISTS
(SELECT COUNT(*)
FROM EMPLOYEE B
WHERE B.LAST_NAME = A.LAST_NAME
AND B.FIRST_NAME = A.FIRST_NAME
AND B.MI = A.MI
AND B.DEPT_NBR = A.DEPT_NBR
HAVING COUNT(*) > 1)
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT416I SQLERRD = 0 0 2 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000002' X'FFFFFFF
INFORMATION
SUCCESSFUL INSERT OF 2 ROW(S)
PAGE 1
***INPUT STATEMENT:
SELECT *
FROM SESSION.EMPLOYEE
;
+-------------------------------------------------------------+
| LAST_NAME | FIRST_NAME | MI | DEPT_NBR |
+-------------------------------------------------------------+
1_| Johnson | Roy | S | 2000 |
2_| Smith | John | T | 1000 |
+-------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 2 ROW(S)
PAGE 1
***INPUT STATEMENT:
DELETE FROM EMPLOYEE E
WHERE EXISTS
(SELECT *
FROM SESSION.EMPLOYEE SE
WHERE SE.LAST_NAME = E.LAST_NAME
AND SE.FIRST_NAME = E.FIRST_NAME
AND SE.MI = E.MI
AND SE.DEPT_NBR = E.DEPT_NBR
)
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT416I SQLERRD = 0 0 5 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000005' X'FFFFFF
INFORMATION
SUCCESSFUL DELETE OF 5 ROW(S)
PAGE 1
***INPUT STATEMENT:
SELECT *
FROM EMPLOYEE
;
+-------------------------------------------------------------+
| LAST_NAME | FIRST_NAME | MI | DEPT_NBR |
+-------------------------------------------------------------+
1_| Jones | John | L | 1200 |
2_| Jones | David | S | 1000 |
3_| Smith | John | T | 1210 |
+-------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 3 ROW(S)
PAGE 1
***INPUT STATEMENT:
SELECT *
FROM SESSION.EMPLOYEE
;
+-------------------------------------------------------------+
| LAST_NAME | FIRST_NAME | MI | DEPT_NBR |
+-------------------------------------------------------------+
1_| Johnson | Roy | S | 2000 |
2_| Smith | John | T | 1000 |
+-------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 2 ROW(S)
PAGE 1
***INPUT STATEMENT:
INSERT INTO EMPLOYEE
SELECT DISTINCT *
FROM SESSION.EMPLOYEE
;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT416I SQLERRD = 0 0 2 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000002' X'FFFFFFF
INFORMATION
SUCCESSFUL INSERT OF 2 ROW(S)
PAGE 1
***INPUT STATEMENT:
SELECT *
FROM EMPLOYEE
;
+-------------------------------------------------------------+
| LAST_NAME | FIRST_NAME | MI | DEPT_NBR |
+-------------------------------------------------------------+
1_| Jones | John | L | 1200 |
2_| Jones | David | S | 1000 |
3_| Smith | John | T | 1210 |
4_| Smith | John | T | 1000 |
5_| Johnson | Roy | S | 2000 |
+-------------------------------------------------------------+
|
|
|
