Home » SQL & PL/SQL » SQL & PL/SQL » Duplicates
Duplicates [message #226437] Fri, 23 March 2007 13:53 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Duplicates [message #226444 is a reply to message #226437] Fri, 23 March 2007 14:57 Go to previous message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
select unique ....

[Updated on: Fri, 23 March 2007 14:57]

Report message to a moderator

Previous Topic: search varchar2 feild and get numbers from it???
Next Topic: How to view a table in 10g Express?
Goto Forum:
  


Current Time: Fri Dec 02 14:33:22 CST 2016

Total time taken to generate the page: 0.25875 seconds