Oracle check if any of multiple string exists in another table

Question

I am newbie to Oracle. I have a requirement in which I need to fetch all the error codes from the comment field and then check it in another table to see the type of code. Depending on the type of code I have to give preference to particular type and then display that error code and type into a csv along with other columns. Below how the data is present in a column

TABLE 1 : COMMENTS_TABLE

  id   | comments
  1    | Manually added (BPM001). Currency code does not exists(TECH23).  
  2    | Invalid counterparty (EXC001). Manually added (BPM002)

TABLE 2 : ERROR_CODES

  id    | error_code  | error_type  
  1     | BPM001      | MAN  
  2     | EXC001      | EXC         
  3     | EXC002      | EXC
  4     | BPM002      | MAN

I am able to get all error codes using REGEX_SUBSTR but not sure how to check it with other table and depending on type display only one. For eg. if the type is MAN only that error code should be returned in select clause.

Please clearly show the expected output and also the query that you have tried. When you say other table, does it mean error_codes table? — Kaushik Nayak
– Kaushik Nayak, Commented Mar 14, 2018 at 5:19
@Fact : First preference to MAN error_type , if not that then EXC error type codes. — mithz
– mithz, Commented Mar 14, 2018 at 6:02
@KaushikNayak : Yes other table means error_codes table which has all the error codes and types available in it. I will update the query sometime as it is in vpn I cannot take it from there. — mithz
– mithz, Commented Mar 14, 2018 at 6:03

Kaushik Nayak · Accepted Answer · 2018-03-14 16:01:38Z

2

I propose you to define a hierarchy of error_codes within the FIRST function to search for the best fit.

SQL Fiddle

Query 1:

 SELECT c.id,
         MAX (
            ERROR_CODE)
         KEEP (DENSE_RANK FIRST
               ORDER BY CASE ERROR_TYPE WHEN 'MAN' THEN 1 WHEN 'EXC' THEN 2 END)
            AS ERROR_CODE,
         MAX (
            ERROR_TYPE)
         KEEP (DENSE_RANK FIRST
               ORDER BY CASE ERROR_TYPE WHEN 'MAN' THEN 1 WHEN 'EXC' THEN 2 END)
            AS ERROR_TYPE
    FROM ERROR_CODES e
         JOIN COMMENTS_TABLE c ON c.COMMENTS LIKE '%' || e.ERROR_CODE || '%'
GROUP BY c.id

Results:

| ID | ERROR_CODE | ERROR_TYPE |
|----|------------|------------|
|  1 |     BPM001 |        MAN |
|  2 |     BPM002 |        MAN |

EDIT : You said in your comments

This is helpul, but I have multiple fields in select clause and adding that in group by could be a problem

One option could be to use a WITH clause to define this result set and then join with other columns.

with res as
(
  select ...
         --query1
)
select t.other_columns, r.id, r.error_code ... 
   from other_table join res on ...

You may also use row_number() alternatively ( Which was actually my original answer. But I changed it to KEEP .. DENSE_RANK as it is efficient.

SELECT * FROM
( SELECT c.id
    ,ERROR_CODE
    ,ERROR_TYPE
    --Other columns,
    ,row_number() OVER (
        PARTITION BY c.id ORDER BY CASE error_type
                WHEN 'MAN'
                    THEN 1
                WHEN 'EXC'
                    THEN 2
                ELSE 3
                END
        ) AS rn
FROM ERROR_CODES e
INNER JOIN COMMENTS_TABLE c 
ON c.COMMENTS LIKE '%' || e.ERROR_CODE || '%'
 ) WHERE rn = 1;

Fiddle

edited Mar 14, 2018 at 16:01

answered Mar 14, 2018 at 7:46

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mithz Over a year ago

This is helpul, but I have multiple fields in select clause and adding that in group by could be a problem.

Kaushik Nayak Over a year ago

@mithila : Please Check i've added an alternative solution. Actually my original answer had used row_number() instead of KEEP .. DENSE_RANK. Anyway, you may use any option that suits you. That's the reason we always insist you to post expected o/p bcoz we end up making assumptions otherwise.

mithz Over a year ago

Thanks @Kaushik this helped.

Ronnis · Accepted Answer · 2018-03-14 15:21:36Z

You can sort, prioritize and filter records with analytic functions.

with comments as(
    select 1 as id
          ,'Manually added (BPM001). Currency code does not exists(TECH23).' as comments 
      from dual union all
    select 2 as id
          ,'Invalid counterparty (EXC001). Manually added (BPM002)'          as comments 
      from dual
)
,error_codes as(
    select 1 as id, 'BPM001' as error_code, 'MAN' as error_type from dual union all 
    select 2 as id, 'EXC001' as error_code, 'EXC' as error_type from dual union all        
    select 3 as id, 'EXC002' as error_code, 'EXC' as error_type from dual union all
    select 4 as id, 'BPM002' as error_code, 'MAN' as error_type from dual
)
-- Everything above this line is not part of the query. Just for generating test data
select * 
  from (select c.id as comment_id
              ,c.comments
              ,e.error_code
              ,row_number() over(
                partition by c.id                              -- For each comment
                    order by case error_type when 'MAN' then 1 -- First prio
                                             when 'EXC' then 2 -- Second prio
                                                        else 3 -- Everything else
                              end) as rn
           from comments    c
           join error_codes e on(
                e.error_code = regexp_substr(c.comments, e.error_code)
           )
        )
  where rn = 1 -- Pick the highest priority code
 /

If you could add a priority column to your error code (or even error_type) you could skip the case/when logic in the order by and simply replacing it with the priority column.

Collectives™ on Stack Overflow

Oracle check if any of multiple string exists in another table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related