Result Null Coming in query

Question

Result Null Coming in query

Navigation

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

2

Good morning,

I need a help from you, I have a query on my system that has a problem, it has 4 results, only 1 of those 4 is coming with null value.

I'm going to post my query below

SELECT Title, SUM(Count) AS Count
FROM (
    SELECT
    CASE WHEN EstadoId = EstadoIdAutor AND EstadoId = Meddoc1 AND EstadoId = Meddoc2 AND EstadoId = Meddoc3 THEN
            'Sem deslocamento'
            ELSE CASE WHEN EstadoId = EstadoIdAutor AND (EstadoId = Meddoc1 OR EstadoId = Meddoc2 OR EstadoId = Meddoc3) THEN
            'Fora de domicílio do autor'
            ELSE CASE WHEN EstadoId <> EstadoIdAutor OR (EstadoId = EstadoIdAutor AND (EstadoId <> Meddoc1 AND EstadoId <> Meddoc2 AND EstadoId <> Meddoc3)) THEN
            'Mais de um deslocamento'
            END
            END
    END AS Title,
    COUNT(*) AS Count
    FROM jud_Processos
    WHERE Status = 1 AND Trancado = 1
    GROUP BY EstadoId, EstadoIdAutor, Meddoc1, Meddoc2, Meddoc3
) Sub
GROUP BY Title

can anybody help me? Where NULL is Other

sql-server query

asked by anonymous 18.09.2017 / 15:35

2 answers

0

You can use coalesce

SELECT COALESCE(Title,'OUTROS'), SUM(Count) AS Count
FROM (
    SELECT
    CASE WHEN EstadoId = EstadoIdAutor AND EstadoId = Meddoc1 AND EstadoId = Meddoc2 AND EstadoId = Meddoc3 THEN
            'Sem deslocamento'
            ELSE CASE WHEN EstadoId = EstadoIdAutor AND (EstadoId = Meddoc1 OR EstadoId = Meddoc2 OR EstadoId = Meddoc3) THEN
            'Fora de domicílio do autor'
            ELSE CASE WHEN EstadoId <> EstadoIdAutor OR (EstadoId = EstadoIdAutor AND (EstadoId <> Meddoc1 AND EstadoId <> Meddoc2 AND EstadoId <> Meddoc3)) THEN
            'Mais de um deslocamento'
            END
            END
    END AS Title,
    COUNT(*) AS Count
    FROM jud_Processos
    WHERE Status = 1 AND Trancado = 1
    GROUP BY EstadoId, EstadoIdAutor, Meddoc1, Meddoc2, Meddoc3
) Sub
GROUP BY Title

18.09.2017 / 15:43

How to insert text into an existing PDF? [closed] jQuery - hide () and show () does not run correctly

score 2 · Accepted Answer

You only need to add ELSE to your CASE :

SELECT title,
       SUM(count) AS count
  FROM (SELECT CASE
                 WHEN estadoid = estadoidautor
                      AND estadoid = meddoc1
                      AND estadoid = meddoc2
                      AND estadoid = meddoc3 THEN 'Sem deslocamento'
                 WHEN estadoid = estadoidautor
                      AND (estadoid = meddoc1
                       OR estadoid = meddoc2
                       OR estadoid = meddoc3) THEN 'Fora de domicílio do autor'
                 WHEN estadoid <> estadoidautor
                      OR (estadoid = estadoidautor
                      AND (estadoid <> meddoc1
                      AND estadoid <> meddoc2
                      AND estadoid <> meddoc3)) THEN 'Mais de um deslocamento'
                 ELSE 'Outros'
               END AS title,
               COUNT(*) AS count
          FROM jud_processos
         WHERE status = 1
           AND trancado = 1
         GROUP BY estadoid,
                  estadoidautor,
                  meddoc1,
                  meddoc2,
                  meddoc3) sub
 GROUP BY title

Note: You only need a CASE in your query .