Loop through 30 columns in Oracle DB to check if a value exists

Question

I have a table which contains an ID in column one and that ID can have up to 30 different codes and dates associated with it.

ID | CODE_1 | CODE_1_DATE | CODE_2 | CODE_2_DATE | CODE_3 | CODE_3_DATE

Here is the only way that I can come up with to do this, which doesn't seem efficient and it also doesn't capture the date:

SELECT ID, CODE_1, CODE_2, CODE_3 
FROM CDHISTTBL
WHERE (ID = 'J12999' AND CODE_1 = 'AB99') or (ID = 'J12999' AND CODE_2 = 'AB99') or (ID = 'J12999' AND CODE_3 = 'AB99')

This is how I'd like the code to execute:

SELECT ID, CODE, CODE_DATE
FROM CDHISTTBL
WHERE ID = 'J12999' AND CODE = 'AB99'

Is there a way to execute this query to simply return the ID, Code and corresponding Date value without having to specifically check each ID+Code combination?

Answer 1

Update to previous answer: if you can't normalise the table structure you could pivot it with a vew or CTE:

WITH CodeSearch AS (
    SELECT ID, Code_1 AS Code, Code_1_Date AS CodeDate, 1 AS CodeFoundInSlot FROM CDHISTTBL WHERE Code_1 IS NOT NULL
    UNION ALL
    SELECT ID, Code_2 AS Code, Code_2_Date AS CodeDate, 2 AS CodeFoundInSlot FROM CDHISTTBL WHERE Code_2 IS NOT NULL
    UNION ALL
    …    …    …    …    …    …    …    
    UNION ALL
    SELECT ID, Code_30 AS Code, Code_30_Date AS CodeDate, 30 AS CodeFoundInSlot FROM CDHISTTBL WHERE Code_30 IS NOT NULL
)
SELECT ID
     , Code
     , CodeDate
  FROM CodeSearch
 WHERE ID = 'J12999' 
   AND Code = 'AB99'

To use the SELECT from the CTE in many places, create it as a view instead so you don't have to include it in every query that needs this lookup.

The CodeFoundInSlot column I've added is optional, remove it if where the code is found is never important to your queries.

The performance of this may be worse than some of the other answers (though no worse than 30 separate selects), so verify the solution performs acceptably on your data size/pattern.

---

---

Previous answer:

As LoztInSpace already said, ideally you would redesign to normalise your data making this sort of query much easier.

With the existing structure you could do something like:

SELECT CASE 
       WHEN code_1 = 'AB99' THEN code_1_date
       WHEN code_2 = 'AB99' THEN code_2_date
         …    …    …    …    …    …    …    
       WHEN code_30 = 'AB99' THEN code_30_date
       END AS DateIfFound
  FROM CDHISTTBL
 WHERE ID = 'J12999'

This will return a row with NULL in the date if nothing is found, you can remove this using a CTE or by repeating the large CASE clause:

WITH CodeSearch AS (
    SELECT ID
         , CASE 
           WHEN code_1 = 'AB99' THEN code_1_date
           WHEN code_2 = 'AB99' THEN code_2_date
             …    …    …    …    …    …    …    
           WHEN code_30 = 'AB99' THEN code_30_date
           END AS DateIfFound
      FROM CDHISTTBL
) 
SELECT ID
     , 'AB99' AS Code
     , DateIfFound AS CodeDate 
  FROM CodeSearch
 WHERE ID = 'J12999'

Far from ideal but it will work. Again, rearranging the data structure would be preferable if that is in your power.

Answer 2

You can try this. It's less typing but essentially the same:

SELECT ID, CODE_1, CODE_2, CODE_3 
FROM CDHISTTBL
WHERE ID = 'J12999' AND 'AB99' IN (CODE_1, Code_2, CODE_3)

It also won't tell you the date of the code that matched.

You really want a new table along the lines of the below (syntax may be a bit off)

create table CDHISTCODES 
(
 id INTEGER, 
 otherID INTEGER FOREIGN KEY (otherID) REFERENCES CDHISTTBL(ID), 
 thecode varchar(10), thedate date
);

From here you can find what you need.

Answer 3

With a little help of code that creates code for you, you might create a view and fetch data in a simple manner.

Sample data (up to "4"; didn't feel like doing it for "30" values):

SQL> select * from cdhisttbl;

ID     CODE_1 CODE_1_DAT CODE_2 CODE_2_DAT CODE_3 CODE_3_DAT CODE_4 CODE_4_DAT
------ ------ ---------- ------ ---------- ------ ---------- ------ ----------
J12999 AB99   15.01.2022 CD18   08.02.2023 ZM32   01.03.2023 DE15   18.08.2023
K25882 FP99   05.04.2022 GH18   02.03.2023 FP18   03.10.2023 AB99   08.07.2023
J12999 MM99   25.06.2022 NN18   28.04.2023 GX23   02.05.2023 AB99   28.01.2023
F87324 GL99   15.02.2022 ZZ18   18.03.2023 GH18   12.07.2023 AX19   18.04.2023

Values you're interested in (ID = J12999 and CODE_X = 'AB99') are in

• row 1 (CODE_1 with date equal to 15.01.2022)
• row 3 (CODE_4 with date equal to 28.01.2023)

Code that writes code for you (in the last line, you'd use 30 instead of 4 as your table has 30 codes):

SQL> select 'select id, ' || level || ' as code#, code_' || level ||' as code, code_' || level ||
  2           '_date as code_date from cdhisttbl union all ' as query
  3  from dual
  4  connect by level <= 4;

QUERY
------------------------------------------------------------------------------------------
select id, 1 as code#, code_1 as code, code_1_date as code_date from cdhisttbl union all
select id, 2 as code#, code_2 as code, code_2_date as code_date from cdhisttbl union all
select id, 3 as code#, code_3 as code, code_3_date as code_date from cdhisttbl union all
select id, 4 as code#, code_4 as code, code_4_date as code_date from cdhisttbl union all

Now, copy/paste that output and use it while creating a view (add the 1st line and remove trailing union all):

SQL> create or replace view v_cdhisttbl as
  2  select id, 1 as code#, code_1 as code, code_1_date as code_date from cdhisttbl union all
  3  select id, 2 as code#, code_2 as code, code_2_date as code_date from cdhisttbl union all
  4  select id, 3 as code#, code_3 as code, code_3_date as code_date from cdhisttbl union all
  5  select id, 4 as code#, code_4 as code, code_4_date as code_date from cdhisttbl;

View created.
  

That's it; the rest is trivial:

SQL> select *
  2  from v_cdhisttbl
  3  where id = 'J12999'
  4    and code = 'AB99';

ID          CODE# CODE CODE_DATE
------ ---------- ---- ----------
J12999          1 AB99 15.01.2022   --> code_1 and code_1_date
J12999          4 AB99 28.01.2023   --> code_4 and code_4_date
                                        contain values you're looking for

---

Though, consider what @LoztInSpace said and redesign that table. That's not the way it should look like.

Answer 4

More compactly than UNION ALL and without having to dynamically generate SQL, transform a single row into three rows so each row can be dedicated to only one of those column pairs.

CREATE VIEW myview
AS
SELECT id,
       code,
       MAX(code_date) code_date
  FROM (SELECT ID, 
               DECODE(seq,1,code_1,
                          2,code_2,
                          3,code_3) code,
               DECODE(seq,1,code_date_1,
                          2,code_date_2,
                          3,code_date_3) code_date
          FROM CDHISTTBL,
               (SELECT LEVEL seq FROM dual CONNECT BY LEVEL <= 3))
 WHERE code IS NOT NULL               
 GROUP BY id,
          code               
/

SELECT *
  FROM myview
 WHERE id = 'J12999'
   AND code = 'AB99'

However, this will not perform well if each of your ID values has a very large number of distinct CODE values, as the latter cannot be indexed. You can still index ID and that may be good enough.

Several folks have provided several methods of doing this transformation. I hope that the complexity involved in each demonstrates how undesirable unnormalized data in an RDBMS is. Your absolute best solution is to redesign the table with proper normalization. As a start, ensure that you never use repeating groups of columns like this in any of your database designs.