Menu de navegação

[tiagossParticipante]

Tenho um sql que estou comparando valores dentro de uma cláusula “in” e só devo retornar se todos existirem. Exemplo:

select *
from teste
where campo1 in (‘AAA’, ‘BBB’, ”CCC’, ‘DDD’);

Na tabela “teste” campo “campo1″ não tenho o valor ”CCC’. Então não devo trazer nenhum resultado.

select *
from teste
where campo1 in (‘AAA’, ‘BBB’, ‘DDD’);

Na tabela “teste” campo “campo1” tenho ‘AAA’, ‘BBB’, ‘DDD’. Então devo trazer os 3 resultados.

Possui algum função no oracle se todos os valores da cláusula “in” existem?

[José Laurindo ChiappaModerador]

Xô entender : vc não diz explicitamente, mas já que uma coluna numa determinada linha só pode ter UM único valor, quando vc diz que tem ‘AAA’ e ‘BBB’ e ‘DDD’ nessa coluna CAMPO1, Claro que são em linhas diferentes, né ?
Imagino então que vc quer realmente trazer as linhas aonde CAMPO1 contenha ou ‘AAA’ ou ‘BBB’ ou ‘DDD’ mas isso DESQUE QUE na tabela como um todo vc tenha pelo menos uma linha com CAMPO1 contendo ‘AAA’ E pelo menos uma linha contendo ‘BBB’ e pelo menos uma linha contendo ‘DDD’ ???
SE FOR ISSO MESMO, não sr, não há nada NATIVO pra isso não : acredito que a melhor maneira seria vc CONTAR (com a função analítica SUM, imagino) a quantidade de linhas com CAMPO1 contendo AAA, com campo1 contendo BBB e com campo1 contendo CCC, aí vc usa essa contagem no WHERE , o que provavelmente vai implicar em algum tipo de SUB-QUERY ou INLINE VIEW…. Blz ?

Abraços,

Chiappa

[José Laurindo ChiappaModerador]

Sendo isso mesmo, eis um exemplinho “desmontado” , mostrando o passo-a-passo percorrido pra chegar no resultado – primeiro, vou ter a tabela com alguns registros com CAMPO1 = AAA e alguns com CAMPO1=BBB mas nenhum contendo DDD :

SYSTEM@db_app::CNTNR=DB_APP> CREATE TABLE TESTE (ID number, DESCR varchar2(80 char), CAMPO1 varchar2(3 char));

Tabela criada.

SYSTEM@db_app::CNTNR=DB_APP> INSERT INTO TESTE values(1, 'Linha 1', 'AAA');
SYSTEM@db_app::CNTNR=DB_APP> INSERT INTO TESTE values(2, 'Linha 2', 'AAA');
SYSTEM@db_app::CNTNR=DB_APP> INSERT INTO TESTE values(3, 'Linha 3', 'BBB');
SYSTEM@db_app::CNTNR=DB_APP> INSERT INTO TESTE values(4, 'Linha 4', 'XYZ');

==> ok, vamos ao filtro de cada linha recuperada, veja que a linha cujo valor NÃO ESTÁ NO IN vai ser filtrada, não aparece :

SYSTEM@db_app::CNTNR=DB_APP> select * from teste where CAMPO1 in ('AAA', 'BBB', 'DDD');

        ID DESCR           CAMPO1
---------- --------------- ------------
         1 Linha 1         AAA
         2 Linha 2         AAA
         3 Linha 3         BBB

3 linhas selecionadas.

SYSTEM@db_app::CNTNR=DB_APP> 

==> Muito bem, agora precisamos validar a nível de tabela , pra fazer isso uma opção é CONTAGEM, vamos a isso :

SYSTEM@db_app::CNTNR=DB_APP> ed
Gravou file afiedt.buf

  1  select t.*,
  2        (select count(*) from teste T1 where T1.CAMPO1='AAA') as CONTAGEM_AAA,
  3        (select count(*) from teste T2 where T2.CAMPO1='BBB') as CONTAGEM_BBB,
  4        (select count(*) from teste T3 where T3.CAMPO1='DDD') as CONTAGEM_DDD
  5*   from teste t where t.CAMPO1 in ('AAA', 'BBB', 'DDD')
SYSTEM@db_app::CNTNR=DB_APP> /

        ID DESCR           CAMPO1       CONTAGEM_AAA CONTAGEM_BBB CONTAGEM_DDD
---------- --------------- ------------ ------------ ------------ ------------
         1 Linha 1         AAA                     2            1            0
         2 Linha 2         AAA                     2            1            0
         3 Linha 3         BBB                     2            1            0

3 linhas selecionadas.

SYSTEM@db_app::CNTNR=DB_APP> 

==> vamos encapsular a consulta num in-line view para que as colunas Calculadas de CONTAGEM possam ser referenciadas no WHERE :

SYSTEM@db_app::CNTNR=DB_APP> SELECT A.ID, A.DESCR, A.CAMPO1
  FROM (select t.*,
              (select count(*) from teste T1 where T1.CAMPO1='AAA') as CONTAGEM_AAA,
              (select count(*) from teste T2 where T2.CAMPO1='BBB') as CONTAGEM_BBB,
              (select count(*) from teste T3 where T3.CAMPO1='DDD') as CONTAGEM_DDD
          from teste t where t.CAMPO1 in ('AAA', 'BBB', 'DDD')
       ) A
  WHERE A.CONTAGEM_AAA > 0 and CONTAGEM_BBB > 0 and CONTAGEM_DDD > 0; 

não há linhas selecionadas

==> ok, NÃO TINHA nenhuma linha na tabela inteira com DDD, não trouxe ninguém… Agora eu terei :

SYSTEM@db_app::CNTNR=DB_APP> insert into TESTE values(5, 'Linha 5', 'DDD');

1 linha criada.

==> pronto, a consulta VAI trazer as linhas :

SYSTEM@db_app::CNTNR=DB_APP> SELECT A.ID, A.DESCR, A.CAMPO1
  FROM (select t.*,
              (select count(*) from teste T1 where T1.CAMPO1='AAA') as CONTAGEM_AAA,
              (select count(*) from teste T2 where T2.CAMPO1='BBB') as CONTAGEM_BBB,
              (select count(*) from teste T3 where T3.CAMPO1='DDD') as CONTAGEM_DDD
          from teste t where t.CAMPO1 in ('AAA', 'BBB', 'DDD')
       ) A
  WHERE A.CONTAGEM_AAA > 0 and CONTAGEM_BBB > 0 and CONTAGEM_DDD > 0;

        ID DESCR           CAMPO1
---------- --------------- ------------
         1 Linha 1         AAA
         2 Linha 2         AAA
         3 Linha 3         BBB
         5 Linha 5         DDD

4 linhas selecionadas.

SYSTEM@db_app::CNTNR=DB_APP> 

==> okdoc ?? Poderia ser algo nesse estilo, SE eu compreendi Corretamente a sua necessidade…. E no caso ao invés de funçao analítica preferi queries colunares….

Abraços,

Chiappa

[MottaParticipante]

Se entendi seria algo como

Select *
From teste
Where (select count(distinct campo1)
from teste
where campo1 in (‘AAA’, ‘BBB’, ”CCC’, ‘DDD’)
Having count(distinct campo1)) = 4;`

[José Laurindo ChiappaModerador]

Sim, pode ser uma variação interessante : ao invés de contar, um COUNT DISTINCT de uma query que só traz os valores desejados serviria, acho eu…

Abraços,

Chiappa

[MottaParticipante]

Complicaria numa lista dinâmica , tipo usuário marca numa ComboList as opções.

[tiagossParticipante]

Obrigado a todos pela ajuda.

Exatamente Motta. É numa lista dinâmica(usuário marca numa ComboList as opções).

Resolvi assim:

WITH quant_campos_lista — contar a quantidade de registros recebido na lista
AS (SELECT Count(1) quant
FROM (SELECT DISTINCT campo1
FROM campos
WHERE campo1 IN ( ‘AAA’, ‘BBB’, ‘CCC’, ‘DDD’ ))),
quant_campos_existem — verificar a quantidade de registros que são validos da lista correspondente
AS (SELECT Count(1) quant
FROM (SELECT DISTINCT campo1
FROM teste
WHERE campo1 IN ( ‘AAA’, ‘BBB’, ‘CCC’, ‘DDD’ )))
SELECT *
FROM teste
WHERE campo1 IN ( ‘AAA’, ‘BBB’, ‘CCC’, ‘DDD’ )
— verificar se a quantidade de registros da lista é igual a quantidade de registros existentes
AND (SELECT quant
FROM quant_campos_lista) = (SELECT quant
FROM quant_campos_existem)

[José Laurindo ChiappaModerador]

Na verdade, Thiago, vc tem no seu código HARD-CODED a condição campo1 IN ( ‘AAA’, ‘BBB’, ‘CCC’, ‘DDD’ ) : ora, se esses valores são Dinâmicos, o usuário PODE escolher qquer combinação de conteúdo para o CAMPO1 , então esse teu código que SEMPRe pergunta por campo1 IN ( ‘AAA’, ‘BBB’, ‘CCC’, ‘DDD’ ) Não Vai Funcionar, ao que entendi…
para poder manipular qquer combinação de valores no CAMPO1, imho OU vc faz a app gerar um SQL dinâmico com a porção do texto do IN contendo a lista de valores escolhida pelo usuário OU vc insere esses valores numa tabela “temporária” , de trabalho, aí teu cóodigo compara os valores de campo1 com a tabela temporária/de trabalho, via EXISTS ao invés de IN….

Abraços,

Chiappa

[MottaParticipante]

https://www.devmedia.com.br/forum/parametro-para-sql-com-mais-de-um-valor/322419

Esta foi uma solução que usei para a versão 9 ou 10 , a performance era ruim , mais permitia este tipo de coisa fazer uma Combo virar um IN , acho que a 12 tem uma solução melhor.

[MottaParticipante]

*mas

[José Laurindo ChiappaModerador]

Blz ? Sim, se REALMENTE o Thiago precisa MESMO que a lista de valores a ser passada pro IN seja dinâmica, UMA das soluções é ter um TYPE customizado e escrever um código PL/SQL que popule um array criado com esse TYPE, sim… Porém, muitas vezes, a performance é ruim devido (entre outras coisas) ao CONTEXT SWITCH que advém de vc ficar ‘mudando’ do engine de PL/SQL para o de SQL, e ALÈM DISSO, imho absolutamente NADA bate a simplicidade de algo tipo :

…. AND CAMPO1 IN (SELECT coluna FROM tabelaquecontemosvaloresdoIN) ….

pronto, cabou, não entrou NADA de PL/SQL (portanto NENHUM context switch acontecerá) , é SIMPLES de entender, SIMPLES de dar manutenção no futuro…E essa “tabelaquecontemosvaloresdoIN” pode INCLUSIVE ser uma Global Temporary Table, que automagicamente já permite que diversos usuários a usem simultaneamente, SEM termos que nos preocupar com LOCKs/concorrência….

Mas se (por qquer motivo – uma tool de desenvlvimento RUIM, que não aceite a solução simples, digamos) aí SIM, temos diversas opções de IN dinâmico : https://stackoverflow.com/questions/3462011/how-do-i-check-for-a-in-condition-against-a-dynamic-list-in-oracle , https://oracle-base.com/articles/misc/dynamic-in-lists#table_function_subselect , https://blog.jooq.org/when-using-bind-variables-is-not-enough-dynamic-in-lists/ , http://www.oracle-developer.net/display.php?id=301 , https://community.spiceworks.com/topic/2429314-how-to-pass-multi-values-into-an-in-clause-via-a-parameter-in-a-store-procedure e https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:110612348061 são algumas…

Abraços,

Chiappa

[José Laurindo ChiappaModerador]

Obviamente, só lembrando :

1) isso depende da tool de desenvolvimento usada, mas VIA DE REGRA, é quase sempre possível manipular o comando SQL que a tool de desenv vai mandar pro banco, dinamicamente incluindo os valores do IN nele : é algo a validar

2) nos releases mais recentes, vc pode ter a tal “tabela com os valores” sendo criada LOGICAMENTE, em memória : UMA das maneiras de vc fazer isso é, por exemplo, passar uma string no formato XML com os valores necessários e “criar a tabela com os valores do IN” via função built-in XMLTABLE, digamos…. Há também algumas built-ins para JSON, se este é o formato que a pessoa prefere/conhece…

3) Há funções que trabalham/podem trabalhar com dados da tabela inteira, como LISTAGG ou funções analíticas sem a cláusula de Partition : seria meio complicado de escrever mas Talvez se possa filtrar os valores dinamicamente do IN com elas

4) é possível (numa variação da opção 2 acima) se passar uma string, dinâmica, com os valores a restringir e validar no WHERE se a coluna CAMPO1 está contida nessa string, respeitando separadores : isso quem poderia fazer é a REGEXP_LIKE, é ainda outra opção…

5) o Óbvio ululante : como estamos falando aqui de POUCOS valores , pode ser que seja viável termos vários Parâmetros/variáveis que possam ser preenchidos com os diversos valores da lista de comparação, aí ao invés de :

CAMPO1 in (‘valor1’, ‘valor2’, ‘valor3’)

teríamos :

WHERE …
AND ( CAMPO1 = nvl(:V1, CAMPO1)
OR CAMPO1 = nvl(:V2, CAMPO1)
OR CAMPO1 = nvl(:V3, CAMPO1)
OR CAMPO1 = nvl(:V4, CAMPO1)
)
….

supondo que ao menos o primeiro valor seja sempre preenchido, aí seria só uma questão de Nulificar os demais que não forem necessários…

Abraços,

Chiappa

[Autor]

Posts