oracle - john - ¿Cómo obtener detalles para las filas que no existen en la tabla? Atrapado con una situación extraña

apex oracle ejemplos

Question

Me atrapó una situación extraña. Aquí está,

Tenemos una tabla con 2 millones de registros donde MSISDN es de tipo cadena, única y no nula. Me pidieron que obtuviera detalles de 300 MSISDN conocidos y, por lo tanto, la consulta utilizada es

select * from table_name 
where msisdn in (''msisdn1'',''msisdn2'',......''msisdn300'')

pero desafortunadamente la consulta anterior devuelve solo 200 conteos. ¿Cómo encontrar esos 100 que no existen desde 300 dados en la consulta?

Solo puedo usar la consulta de selección debido a privilegios limitados.

Por favor aconséjame.

Answer 1

Creo que lo que estás pidiendo es posible, pero requiere un poco de esfuerzo.

En primer lugar, no creo que haya ninguna forma de encontrar qué elementos dentro de una cláusula IN no coinciden con nada en una tabla. Entonces, en cambio, me temo que tendrá que copiar esa parte de su consulta y modificarla.

Por el momento, su consulta se parece más a

SELECT * FROM my_table WHERE id IN (''id1'',''id2'',''id3'',''id4'',''id5'',''id6'')

Suponiendo que ninguno de sus MSISDN tenga comas y / o comillas simples en ellos, lo que puede hacer es:

• tomar el texto de la parte IN (...) de su consulta,
• eliminar todas las comillas simples,
• agregue una coma al comienzo y al final,
• rodea toda la cadena en comillas simples.

La lista de seis id en mi ejemplo anterior se convertiría en

'',id1,id2,id3,id4,id5,id6,''

Con todos los ID en una sola cadena, podemos ejecutar una consulta como la siguiente:

VARIABLE ids_string VARCHAR2(4000);
EXEC :ids_string := '',id1,id2,id3,id4,id5,id6,'';

WITH comma_posns AS (SELECT level AS comma_pos
                       FROM DUAL
                      WHERE SUBSTR(:ids_string, level, 1) = '',''
                       CONNECT BY LEVEL <= LENGTH(:ids_string)),
     starts_ends AS (SELECT comma_pos AS start_pos,
                            LEAD(comma_pos, 1, NULL) OVER (ORDER BY comma_pos)
                                      AS end_pos
                       FROM comma_posns),
     ids AS (SELECT SUBSTR(:ids_string, start_pos + 1, end_pos - start_pos - 1)
                    AS id
               FROM starts_ends
              WHERE end_pos IS NOT NULL)
SELECT id
  FROM ids
 WHERE id NOT IN (SELECT id FROM my_table);

Puse la lista de ID en una variable de :ids_string llamada :ids_string para poder consultarla varias veces a lo largo de la consulta.

La subconsulta de comma_posns enumera todas las posiciones dentro de la cadena en la que aparecen las comas. starts_ends luego reorganiza esto en pares de posiciones de coma, y ​​luego los ids usan estas posiciones de inicio y fin para seleccionar los IDs de la cadena. Finalmente, seleccionamos todos los ID que están en esta cadena pero no en la tabla.

Ejecuté la consulta anterior usando los siguientes datos de muestra:

SQL> select id from my_table; ID -------------------- id1 id1a id4 id6

Cuando ejecuté esta consulta, obtuve el siguiente resultado:

ID -------------------------------------------------------------------------------- id2 id3 id5

Si su cadena de identificaciones es particularmente larga (es decir, más de 4000 caracteres), puede que tenga que dividirla en partes de 4000 caracteres o menos.

Answer 2

En primer lugar, el MSISDN es una clave primaria (única + no nula), por lo que no hay posibilidad de que sea nula.

Llamas utiliza la siguiente consulta,

Seleccione * del nombre de la tabla donde MSISDN no tiene límite nulo 300;

Answer 3

Puede crear una tabla "virtual" con todos los ID que está buscando, y luego hacer una combinación externa con su tabla real.

with to_search as (
    select ''msisdn1'' as msisdn from dual 
    union all 
    select ''msisdn1'' from dual
    union all
    select ''msisdn2'' from dual
    union all
    select ''msisdn2'' from dual
    ...
    (you get the picture)
    ...
    select ''msisdn3000'' from dual
)
SELECT s.msisdn, 
       nvl(mt.msisdn, ''not found'')
FROM to_search s
  LEFT JOIN my_table mt ON mt.msisdn = s.msisdn 

Para todas las ID que no están presentes en my_table , verá un "no encontrado"