Why does the if or case test not work in this subquery?

Question

Why does the if or case test not work in this subquery?

Navigation

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

2

I'm trying to run the following subquery inside a view:

    (SELECT 

        if(idnotas > 0, "não", "sim")

    FROM notas WHERE vendas_idvendas = p.vendas_idvendas
    ORDER BY idnotas DESC
    LIMIT 1

    )  as primeiraFatura,

However, if is only set when the condition is true. At the time it is fake, I get a null, instead of the sim.

The funny thing is that if I execute the same as a normal query (without being a subquery) the return is exactly what I want, and the value "yes" is assigned (when it corresponds to the condition).

Follow the complete query:

         SELECT 

    idpatrimonios, numeroPatrimonio, n.dataVencimento,

    (SELECT nomeTipo FROM tipos WHERE idtipos = p.tipos_idtipos) as tipo,

    cl.idclientes as idclientes, p.vendas_idvendas, dataEntrega, dataFatura,

    (
        SELECT 

            if(idnotas > 0, "não", "sim")

        FROM notas WHERE vendas_idvendas = p.vendas_idvendas
        ORDER BY idnotas DESC
        LIMIT 1

    )  as primeiraFatura,

    (
        SELECT 

            case 
                WHEN MONTH(dataVencimento) >= MONTH(NOW()) THEN "não"
                WHEN NOW() >= dataFatura THEN "sim"
            end

        FROM notas WHERE vendas_idvendas = p.vendas_idvendas
        ORDER BY idnotas DESC
        LIMIT 1

    )  as exibirNaLista

FROM patrimonios as p

INNER JOIN vendas as v ON v.idvendas = p.vendas_idvendas
INNER JOIN clientes as cl ON cl.idclientes = v.clientes_idclientes
LEFT JOIN logpatrimonios as log ON log.patrimonios_idpatrimonios = p.idpatrimonios
LEFT JOIN notas as n ON n.vendas_idvendas = p.vendas_idvendas 

WHERE situacao_idsituacao = 2 

group by idpatrimonios;

I'm using Mysql 8.

mysql if query case

asked by anonymous 27.12.2018 / 12:49

2 answers

Server can not attach a header after the HTTP headers have been sent Sort by name in alphabetical order

score 0 · Answer 1

Vitor,

Please use ELSE as follows: EXAMPLE (correct as you like):

SELECT 
CASE
    WHEN MONTH(dataVencimento) >= MONTH(NOW()) THEN "não"
    WHEN NOW() >= dataFatura THEN "sim"
    ELSE "sim"
END
FROM ...

score 0 · Answer 2

Vitor,

Change the position of the "displayNaList" to after the "end", like this:

(
SELECT 
case 
WHEN MONTH(dataVencimento) >= MONTH(NOW()) THEN "não"
WHEN NOW() >= dataFatura THEN "sim"
ELSE "sim"
end as exibirNaLista
FROM notas WHERE vendas_idvendas = p.vendas_idvendas
ORDER BY idnotas DESC
LIMIT 1
)
FROM patrimonios as p
...