valores valor validar vacios una resultados registro reemplazar por mostrar hay existe ejemplos devolver consulta campos campo cambiar buscar sql oracle11g toad

sql - validar - Devuelve valor cero cuando no hay datos de fila



reemplazar el valor null de un campo en una consulta mysql (1)

Primero, vuelve a escribir tu consulta. Utilice las vistas o la expresión de tabla común para evitar repetirse tres veces para sus cláusulas SELECT , GROUP BY , ORDER BY . Su consulta se convierte en:

WITH data AS ( SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day'' WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days'' WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days'' WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days'' WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed'' END) AS Age FROM table_1 WHERE id IN (1,2,3) ) SELECT Age, COUNT(*) FROM data GROUP BY Age ORDER BY Age

Luego, para asegurarse de que cualquiera de sus grupos deseados esté disponible en el resultado, tiene muchas opciones.

Podría usar UNION ALL :

WITH data AS ( SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day'' WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days'' WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days'' WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days'' WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed'' END) AS Age FROM table_1 WHERE id IN (1,2,3) -- The below will add one record for every desired Age group UNION ALL SELECT ''0-1 day'' FROM DUAL UNION ALL SELECT ''1-2 days'' FROM DUAL UNION ALL SELECT ''2-3 days'' FROM DUAL UNION ALL SELECT ''3-4 days'' FROM DUAL UNION ALL SELECT ''Closed'' FROM DUAL ) SELECT Age, COUNT(*) - 1 -- Subtract the extra record again FROM data GROUP BY Age ORDER BY Age

Una solución completamente diferente implicaría LEFT OUTER JOINs :

-- Groups is a dynamic table that contains the date ranges and their "Age" label WITH groups AS ( SELECT SYSDATE -1 lower, SYSDATE upper, ''0-1 day'' Age FROM DUAL UNION ALL SELECT SYSDATE -2 , SYSDATE -1 , ''1-2 days'' FROM DUAL UNION ALL SELECT SYSDATE -3 , SYSDATE -2 , ''2-3 days'' FROM DUAL UNION ALL SELECT SYSDATE -4 , SYSDATE -3 , ''3-4 days'' FROM DUAL UNION ALL SELECT SYSDATE -5 , SYSDATE -4 , ''Closed'' FROM DUAL ) SELECT g.Age, NVL(SUM(t.counter), 0) FROM groups g -- LEFT OUTER JOINing "table_1" to "groups" will ensure that every group -- appears at least once in the result LEFT OUTER JOIN ( SELECT 1 counter, t.* FROM table_1 t WHERE t.id IN (1,2,3) ) t ON t.time_dtm >= g.lower AND t.time_dtm < g.upper GROUP BY g.Age ORDER BY g.Age

En el segundo ejemplo, también podría hacerlo sin un CTE y usar un SELECT anidado para la tabla de groups . Es fácil ver cómo el segundo ejemplo es más simple de evolucionar en el futuro, en caso de que cambien sus requisitos.

Mi pregunta se ha abordado anteriormente, pero parece que no puedo aplicar ninguna solución a mi consulta para que funcione. Apreciaría mucho alguna orientación.

Mi consulta actual a continuación devuelve este conjunto de datos:

| Age | Count | 0-1 day 300 2-3 days 6000 3-4 days 100

SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day'' WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days'' WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days'' WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days'' WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed'' END) AS Age, COUNT( * ) AS "Count" FROM table_1 WHERE id IN (1,2,3) GROUP BY (CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day'' WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days'' WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days'' WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days'' WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed'' END) ORDER BY (CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day'' WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days'' WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days'' WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days'' WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed'' END)

Sin embargo, me gustaría que muestre las filas cero / nulas como ceros como este:

| Age | Count | 0-1 day 300 1-2 days 0 2-3 days 6000 3-4 days 100 Closed 0

He leído todo tipo de los últimos días re: NVL, COALESCE, FULL / LEFT / RIGHT OUTER JOIN, IZQUIERDA / DERECHA SE UNE, UNIÓN TODO etc. ninguno de los cuales tenía declaraciones CASE e intenté solucionarlo, PERO! Debes saber cuándo parar y pedir indicaciones.