Pular para o conteúdo

Marcado: 

  • Este tópico contém 11 respostas, 3 vozes e foi atualizado pela última vez 2 anos, 3 meses atrás por Avatar photoJosé Laurindo Chiappa.
Visualizando 12 posts - 1 até 12 (de 12 do total)
  • Autor
    Posts
  • #156972
    tiagoss
    Participante

      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?

      #156982
      Avatar photoJosé Laurindo Chiappa
      Moderador

        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

        #156984
        Avatar photoJosé Laurindo Chiappa
        Moderador

          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

          #156995
          Motta
          Participante

            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;`

            #157010
            Avatar photoJosé Laurindo Chiappa
            Moderador

              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

              #157030
              Motta
              Participante

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

                #157031
                tiagoss
                Participante

                  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)

                  #157033
                  Avatar photoJosé Laurindo Chiappa
                  Moderador

                    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

                    #157045
                    Motta
                    Participante

                      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.

                      #157046
                      Motta
                      Participante

                        *mas

                        #157047
                        Avatar photoJosé Laurindo Chiappa
                        Moderador

                          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

                          #157048
                          Avatar photoJosé Laurindo Chiappa
                          Moderador

                            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

                          Visualizando 12 posts - 1 até 12 (de 12 do total)
                          • Você deve fazer login para responder a este tópico.
                          plugins premium WordPress