uso - tipos de case en sql
Obtener nulo== nulo en SQL (11)
@Andy Lester afirma que la forma original de la consulta es más eficiente que usar NVL. Decidí probar esa afirmación:
SQL> DECLARE
2 CURSOR B IS
3 SELECT batch_id, equipment_id
4 FROM batch;
5 v_t1 NUMBER;
6 v_t2 NUMBER;
7 v_c1 NUMBER;
8 v_c2 NUMBER;
9 v_b INTEGER;
10 BEGIN
11 -- Form 1 of the where clause
12 v_t1 := dbms_utility.get_time;
13 v_c1 := dbms_utility.get_cpu_time;
14 FOR R IN B LOOP
15 SELECT COUNT(*)
16 INTO v_b
17 FROM batch
18 WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL);
19 END LOOP;
20 v_t2 := dbms_utility.get_time;
21 v_c2 := dbms_utility.get_cpu_time;
22 dbms_output.put_line(''For clause: WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL)'');
23 dbms_output.put_line(''CPU seconds used: ''||(v_c2 - v_c1)/100);
24 dbms_output.put_line(''Elapsed time: ''||(v_t2 - v_t1)/100);
25
26 -- Form 2 of the where clause
27 v_t1 := dbms_utility.get_time;
28 v_c1 := dbms_utility.get_cpu_time;
29 FOR R IN B LOOP
30 SELECT COUNT(*)
31 INTO v_b
32 FROM batch
33 WHERE NVL(equipment_id,''xxxx'') = NVL(R.equipment_id,''xxxx'');
34 END LOOP;
35 v_t2 := dbms_utility.get_time;
36 v_c2 := dbms_utility.get_cpu_time;
37 dbms_output.put_line(''For clause: WHERE NVL(equipment_id,''''xxxx'''') = NVL(R.equipment_id,''''xxxx'''')'');
38 dbms_output.put_line(''CPU seconds used: ''||(v_c2 - v_c1)/100);
39 dbms_output.put_line(''Elapsed time: ''||(v_t2 - v_t1)/100);
40 END;
41 /
For clause: WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL)
CPU seconds used: 84.69
Elapsed time: 84.8
For clause: WHERE NVL(equipment_id,''xxxx'') = NVL(R.equipment_id,''xxxx'')
CPU seconds used: 124
Elapsed time: 124.01
PL/SQL procedure successfully completed
SQL> select count(*) from batch;
COUNT(*)
----------
20903
SQL>
Me sorprendió bastante descubrir cuán correcto es Andy. Cuesta casi un 50% más hacer la solución NVL. Por lo tanto, aunque una pieza de código no se vea tan ordenada o elegante como otra, podría ser significativamente más eficiente. Ejecuté este procedimiento varias veces y los resultados fueron casi los mismos cada vez. Felicitaciones a Andy ...
Deseo buscar una tabla de base de datos en una columna que se pueda anotar. Algunas veces, el valor que busco es NULL. Dado que Null es igual a nada, incluso NULL, diciendo
where MYCOLUMN=SEARCHVALUE
fallará. En este momento tengo que recurrir a
where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))
¿Hay alguna manera más simple de decir eso?
(Estoy usando Oracle si eso importa)
En Expert Oracle Database Architecture vi:
WHERE DECODE(MYCOLUMN, SEARCHVALUE, 1) = 1
Esta es una situación en la que nos encontramos mucho con nuestras funciones de Oracle que generan informes. Queremos permitir a los usuarios ingresar un valor para restringir los resultados o dejarlo en blanco para devolver todos los registros. Esto es lo que he usado y nos ha funcionado bien.
WHERE rte_pending.ltr_rte_id = prte_id
OR ((rte_pending.ltr_rte_id IS NULL OR rte_pending.ltr_rte_id IS NOT NULL)
AND prte_id IS NULL)
Esto también puede hacer el trabajo en Oracle.
WHERE MYCOLUMN || ''X'' = SEARCHVALUE || ''X''
Hay algunas situaciones en las que supera la prueba IS NULL con el quirófano.
También me sorprendió que DECODE te permita verificar NULL contra NULL.
WITH
TEST AS
(
SELECT NULL A FROM DUAL
)
SELECT DECODE (A, NULL, ''NULL IS EQUAL'', ''NULL IS NOT EQUAL'')
FROM TEST
No sé si es más simple, pero ocasionalmente he usado
WHERE ISNULL(MyColumn, -1) = ISNULL(SearchValue, -1)
Reemplazar "-1" con algún valor que sea válido para el tipo de columna, pero que probablemente no se encuentre realmente en los datos.
NOTA: uso MS SQL, no Oracle, por lo que no estoy seguro de si "ISNULL" es válido.
Otra alternativa, que probablemente sea óptima desde el punto de vista de la consulta ejecutada, y será útil solo si está realizando algún tipo de generación de consultas es generar la consulta exacta que necesita basándose en el valor de búsqueda.
El pseudocódigo sigue.
if (SEARCHVALUE IS NULL) {
condition = ''MYCOLUMN IS NULL''
} else {
condition = ''MYCOLUMN=SEARCHVALUE''
}
runQuery(query,condition)
Puedes hacer las cosas de IsNull o NVL, pero solo hará que el motor trabaje más. Llamarás a funciones para hacer conversiones de columna que luego tendrán que comparar los resultados.
Usa lo que tienes
where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))
Si un valor fuera de banda es posible:
where coalesce(mycolumn, ''out-of-band'')
= coalesce(searchvalue, ''out-of-band'')
Tratar
WHERE NVL(mycolumn,''NULL'') = NVL(searchvalue,''NULL'')
Use NVL para reemplazar null con algún valor ficticio en ambos lados, como en:
WHERE NVL(MYCOLUMN,0) = NVL(SEARCHVALUE,0)
Yo pensaría que lo que tienes está bien. Quizás puedas usar:
where NVL(MYCOLUMN, '''') = NVL(SEARCHVALUE, '''')