CODEWITHSUNDEEP
sqloracle-database
mithz
mithz

Reputation: 24

Oracle check if any of multiple string exists in another table

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.

Upvotes: 0

Views: 287

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31736

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

Upvotes: 2

Ronnis
Ronnis

Reputation: 12843

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.

Upvotes: 0

Related Questions