Faça um loop por 30 colunas no banco de dados Oracle para verificar se existe um valor

Você pode tentar isso. É menos digitação, mas essencialmente o mesmo:

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

Também não informará a data do código correspondente.

Você realmente quer uma nova tabela nos moldes abaixo (a sintaxe pode estar um pouco errada)

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

A partir daqui você pode encontrar o que precisa.

Com uma pequena ajuda do código que cria código para você, você pode criar uma visualização e buscar dados de maneira simples.

Dados de amostra (até "4"; não tive vontade de fazer isso para valores "30"):

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

Os valores de seu interesse (ID = J12999 e CODE_X= 'AB99') estão em

• linha 1 ( CODE_1com data igual a 15.01.2022)
• linha 3 ( CODE_4com data igual a 28.01.2023)

Código que escreve código para você (na última linha, você usaria 30em vez de, 4pois sua tabela tem 30 códigos ):

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

Agora, copie/cole essa saída e use-a ao criar uma visualização (adicione a primeira linha e remova o final 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.
  

É isso; o resto é 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

Porém, considere o que @LoztInSpace disse e redesenhe essa tabela. Não é assim que deveria ser.

Atualize para a resposta anterior: se você não conseguir normalizar a estrutura da tabela, poderá dinamizá-la com um vew ou 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'

Para usar o SELECTdo CTE em muitos lugares, crie-o como uma visualização para não precisar incluí-lo em todas as consultas que precisam dessa pesquisa.

A CodeFoundInSlotcoluna que adicionei é opcional, remova-a se o local onde o código foi encontrado nunca for importante para suas consultas.

O desempenho disso pode ser pior do que algumas das outras respostas (embora não seja pior do que 30 seleções separadas); portanto, verifique se a solução tem um desempenho aceitável no tamanho/padrão dos seus dados.

Resposta anterior:

Como LoztInSpace já disse, o ideal é redesenhar para normalizar seus dados, tornando esse tipo de consulta muito mais fácil.

Com a estrutura existente você poderia fazer algo como:

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'

Isso retornará uma linha com NULL na data se nada for encontrado, você pode remover isso usando um CTE ou repetindo a grande cláusula CASE:

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'

Longe do ideal, mas funcionará. Novamente, reorganizar a estrutura de dados seria preferível se isso estivesse ao seu alcance.

De forma mais compacta que UNION ALL e sem a necessidade de gerar SQL dinamicamente, transforme uma única linha em três linhas para que cada linha possa ser dedicada a apenas um desses pares de colunas.

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'

No entanto, isso não terá um bom desempenho se cada um dos seus valores de ID tiver um número muito grande de valores CODE distintos, pois estes últimos não podem ser indexados. Você ainda pode indexar o ID e isso pode ser bom o suficiente.

Várias pessoas forneceram vários métodos para fazer essa transformação. Espero que a complexidade envolvida em cada um demonstre quão indesejáveis ​​são os dados não normalizados em um RDBMS. Sua melhor solução é redesenhar a tabela com a normalização adequada. Para começar, certifique-se de nunca usar grupos repetidos de colunas como esse em nenhum dos designs de seu banco de dados.