I have the above table named htd. I am trying to fetch the result where tran_particular is unique before the ":"

Final output looking for:

But below query returns no rows:

select tran_id||'|'||tran_date||'|'|| TRAN_PARTICULAR||'|'|| tran_amt
||'|'|| part_tran_type from htd a where a.acid ='ab500'  and 
Substr(TRAN_PARTICULAR,1,instr(TRAN_PARTICULAR,':')-1) not in (select Substr(TRAN_PARTICULAR,1,instr(TRAN_PARTICULAR,':')-1) from htd b where b.acid ='ab500'
  and b.tran_id not in( a.tran_id)) order by tran_date;

CodePudding user response：

koen >CREATE TABLE htd (tran_id, tran_particular)AS
  2  (
  3  SELECT 'm1', '123:qwe' FROM DUAL UNION ALL
  4  SELECT 'm5', '124:qse' FROM DUAL UNION ALL
  5  SELECT 'm16', '123:pyh' FROM DUAL
  6* );

Table HTD created.

koen >WITH tp_unique_vals (tran_particular, cnt) AS
  2  (
  3  SELECT SUBSTR(tran_particular,1,INSTR(tran_particular,':')-1), COUNT(*) 
  4    FROM htd 
  5    GROUP BY SUBSTR(tran_particular,1,INSTR(tran_particular,':')-1)
  6  HAVING COUNT(*) = 1
  7  )
  8  SELECT h.* 
  9    FROM htd h
 10*        JOIN tp_unique_vals u ON SUBSTR(h.tran_particular,1,instr(h.tran_particular,':')-1) = u.tran_particular;

TRAN_ID    TRAN_PARTICULAR    
__________ __________________ 
m5         124:qse            

koen >

CodePudding user response：

Try to use a regular expression and an aggregation:

        with a as (select '123:qwe' b from dual union all 
             select '124:qse' from dual union all 
             select '123:pyh' from dual)
select b
  from a
 where regexp_substr(b, '[^:] ') in ( select c 
                                        from (select b, regexp_substr(b, '[^:] ') c 
                                                from a)
                                       group by c
                                       having count(*) = 1);

Returns 124:qse.