| Duplicates [message #226437] |
Fri, 23 March 2007 13:53  |
marks20101
Messages: 74
Registered: May 2005
|
Member |
|
|
How can I process this query to return only one duplicate?
select substr(a.LOCAL_ERR_MSG,1,56) from err_log a, err_log b
WHERE TRUNC(a.ERR_TS) = TO_DATE('20-FEB-07','DD-MON-YY')
AND substr(a.local_ERR_MSG,1,46) LIKE '%Case record containing case external number =%'
and a.ERR_LOG_PID <> b.ERR_LOG_PID
ERR_LOG_PID NOT NULL NUMBER(10)
PROCESS_RUN_PID NOT NULL NUMBER(10)
ERR_SVRTY_CODE_PID NUMBER(10)
ERR_SRC_SYS_CODE_PID NUMBER(10)
ERR_RPT_GRP_CODE_PID NUMBER(10)
ERR_TS NOT NULL DATE
SQL_ERR_MSG VARCHAR2(2000)
LOCAL_ERR_MSG VARCHAR2(2000)
retunn data looks like this:
Case record containing case external number = M034156398
Case record containing case external number = M034156398
Case record containing case external number = M034156398
....
Thanks
|
|
|
|
| Re: Duplicates [message #226439 is a reply to message #226437] |
Fri, 23 March 2007 14:40  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I think your query does not do what you want.
If you extract the core to simplify, here'a what you get a simple table containing 1, 2 and 3.
(Column from B is given just to show you what's happening.)
SQL> with data as (select 1 col from dual union all select 2 from dual union all select 3 from dual)
2 select a.col, b.col "(BCOL)"
3 from data a, data b
4 where a.col <> b.col
5 /
COL (BCOL)
---------- ----------
1 2
1 3
2 1
2 3
3 1
3 2
6 rows selected.
I think you can have what you want with just:
select substr(a.LOCAL_ERR_MSG,1,56)
from err_log a
WHERE TRUNC(a.ERR_TS) = TO_DATE('20-FEB-07','DD-MON-YY')
AND substr(a.local_ERR_MSG,1,46) LIKE '%Case record containing case external number =%';
or maybe with a "distinct":
select distinct substr(a.LOCAL_ERR_MSG,1,56)
from err_log a
WHERE TRUNC(a.ERR_TS) = TO_DATE('20-FEB-07','DD-MON-YY')
AND substr(a.local_ERR_MSG,1,46) LIKE '%Case record containing case external number =%';
Regards
Michel
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
