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?

• LoztInSpace: There is, but you will have to redesign your database to have an extra table for the data/codes and (re)move the columns from your current table.

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.

• Rellek: Your updated solution worked out great. Since I have no control over the design of this table, this extra bit of code is a great alternative. Thank you!

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.

• Rellek: I appreciate everyone's help with this. @Littlefoot, your solution made the most sense since I do not have control over how this table is designed. I created the view, but when I tried to query it, I got an error that the table or view does not exist. Any thoughts on why I may be getting this error?
• Littlefoot: Either source table (the one that contains all code information - cdhisttbl ) doesn't exist, or view wasn't created (for some reason) or was created with errors so - when you tried to select from it - it failed. If what I've just said doesn't help, consider editing the original question and post steps you took - as I did, copy/paste - so that we'd see what's going on.

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.