Arrays in the 'NOT IN' WHERE clause

Question

Arrays in the 'NOT IN' WHERE clause

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

DECLARE 
   string_valor  VARCHAR2(100);
   lista_array dbms_utility.lname_array;
   contador binary_integer; 
BEGIN 
   SELECT t.campo_a INTO string_valor
   FROM tabela t
   WHERE t.campo_cod = 1;
   dbms_utility.comma_to_table
   (
       list          => regexp_replace(string_valor,'(^|,)','x')
       , tablen  => contador
       , tab       => lista_array
   );       
   FOR i in 1 .. l_count LOOP
      dbms_output.put_line( substr(lista_array(i),2) );
   END LOOP;
END;

This code works perfectly, what it does is to get a VARCHAR variable with values '2,3,5,9' for example and separate them into a vector of numbers, so that this need? So that I can compare, using NOT IN, each element of ARRAY with a field of NUMBER inside the WHERE clause.

The problem is how I can call it in the Where clause, using NOT IN:

SELECT ... FROM tabela t
WHERE campo_exemplo_1 > 0 
      AND campo_exemplo_2 <> campo_exemplo_4
      AND campo_exemplo_3 NOT IN ( ARRAY AQUI??? )

oracle pl-sql

asked by anonymous 05.02.2015 / 14:13

2 answers

2

The NOT IN clause should be populated with the values you want to search in the field.

For example, if the field is a foreign key, make a subquery by selecting only the id s from the table.

Post table

id titulo
 1 Titulo do post 1
 2 Titulo do post 2

Category table

id titulo
 1 Curiosidades
 2 Bobagens
 3 Variedades

Post_category table

post_id category_id
      1           1
      1           3
      2           2
      2           3

Your query would then look like this:

SELECT p.*
FROM post p
INNER JOIN post_category pc
    ON pc.post_id = p.id
    AND pc.category_id IN (
        SELECT c.id
        FROM category c
        WHERE c.titulo = 'Variedades'
    )

PS: I based my answer on the ANSI SQL standard. Maybe Oracle has some differences in the implementation of IN but I think it's unlikely.

05.02.2015 / 14:36

Audio Tag does not work on Phonegap Receive id of an autocomplete option

score 0 · Accepted Answer

I'm trying to solve the problem, I'll post the result here if anyone needs it.

DECLARE 
    V_varchar2  VARCHAR2(100);
    l_List_char dbms_utility.lname_array;
    l_count binary_integer;   

    TYPE TesteVarchar2 IS TABLE OF NUMBER;
    l_Aux_ListNumber TesteVarchar2  := TesteVarchar2();
    resultado NUMBER;

BEGIN      
    V_varchar2 := '2,9,8,10,5';
    dbms_utility.comma_to_table
    (
         list      => regexp_replace(V_varchar2,'(^|,)','x')
         , tablen  => l_count
         , tab     => l_List_char
    ); 

    l_Aux_ListNumber.EXTEND(l_count);

    FOR i in 1 .. l_count LOOP       
         l_Aux_ListNumber(i) := substr(l_List_char(i),2);
    END LOOP;

    -- Aqui uma pequena operação matematica para provar que virou number
    FOR i in 1 .. l_count LOOP
         dbms_output.put_line( l_Aux_ListNumber(i));
         resultado := l_Aux_ListNumber(i) * i;
         dbms_output.put_line( 'Result: ' || resultado );   
    END LOOP;

END;

Just call inside clause:

Where na clausula NOT IN ( SELECT column_value FROM TABLE( TesteVarchar2() );