Why do if or case tests not work in this subquery?

Asked

Viewed 55 times

2

I am trying to run the following subquery within 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 defined when the condition is true. The moment she’s fake, I get a null, instead of yes.

The funny thing is that if I run 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 matches the condition ).

Follow the full 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 am using Mysql 8.

mysql query if case

  • have tried using single quotes :

  • Yeah, it’s not the problem.

  • Perhaps it would be better to validate if the field is NULL and assign a value: if(ifnull(idnotas. 0) > 0, "não", "sim")

  • It doesn’t help me much, but I tested only for consciousness disengagement and the return remains null: if(ifnull(idnotas, 0) > 0, "no", "yes")

  • SELECT IF(COUNT(idnotes) > 0, 'no', 'yes') FROM notes...

  • 1

    Fabius thus solved the problem. But, I still continue with the question at other points of the query that return null when the first condition is not satisfied, for example in the return of 'displayNaLista'. I need to understand what to do with these null returns so that the tests continue in case the condition is not satisfied.

  • Vitor, the first condition?!? Sorry, question: What is the first condition? It would be "no"??

  • 1

    I’ll take it you’re asking me about the 'show-off' thing. I have two cases: WHEN MONTH(due date) >= MONTH(NOW()) THEN 'no' WHEN NOW() >= dataFatura THEN 'yes' If the due date month is greater than or equal to the current date month, I want to display no. Up to this point it works perfectly. The problem happens if the due month is less than the current month, where I never enter the second case, for some reason

  • Victor, it may be the following: The two conditions are NOT met.

  • This is not the case, Fabio. As I answered your comment, even putting a ELSE, I still get null. And, as described in the question, if I run the subquery individually, I get the data I normally want.

  • Vitor, I’ve looked at some examples at w3schools.com and I’m guessing that the "displaying them" is in the wrong position... please check.

Show 6 more comments

2 answers

0

Victor,

Please use the ELSE as follows: EXAMPLE (correct to your liking):

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

  • It still returns null. The second condition is being satisfied, the question is that the query only interprets the first condition and, if true, returns its value. Otherwise, returns null, regardless of the other conditions to be checked.

0

Victor,

Change the position of the "show" to after the "end", this way:

(
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
...

  • Same problem as above. But, I did the query like this: if(idnotes > 0, if(MONTH(NOW() > MONTH(n.expiration date)), "yes", "no"), if(NOW() >= v.dataFatura, "yes", "no") ) as displayNaList, And I get the results I want. The problem only happens when I use the if or case within a new select, just where I need it most.

  • In Stackoverflow, you have a similar question: "Can I use the subquery Inside a case statement?" ... " I think you have another question, which is that you are performing an aggregation in an expression that contains a sub-query, which gives an error."

Browser other questions tagged mysql query if case

You are not signed in. Login or sign up in order to post.