Return predefined values depending on logical condition

Question

I got the following script mysql:

SELECT con.id_crecpa,
    con.id_crecpa,
    con.descricao,
    con.diabase,
    con.dt_emissao,
    con.dt_quitacao 
FROM crecpa con
INNER JOIN pessoa pes ON con.id_pessoa = pes.id_pessoa
INNER JOIN plano_conta pla ON con.id_plano_conta = pla.id_plano_conta        
WHERE con.id_empresa = 6
AND con.dt_vencto < CURDATE()

If the registration date is less than the current date, I want to return one more column with the "expired" value, how can I do this?

Edit: actually, I will need more than that, I will need to return 'defeated', 'wins today' and 'to win', I already know I will have to use the CASE WHEN, and tried that way:

SELECT con.id_crecpa,
    con.id_crecpa,
    con.descricao,
    con.diabase,
    con.dt_emissao,
    con.dt_vencto,  
    CASE con.diabase
       WHEN con.dt_vencto < CURDATE() THEN 'vencido'
       WHEN con.dt_vencto > CURDATE() THEN 'a vencer'
       WHEN con.dt_vencto = CURDATE() THEN 'vence hoje'      
    END AS venc 
FROM crecpa con
INNER JOIN pessoa pes ON con.id_pessoa = pes.id_pessoa
INNER JOIN plano_conta pla ON con.id_plano_conta = pla.id_plano_conta        
WHERE con.id_empresa = 6
AND con.dt_vencto < CURDATE()
AND con.tipo = "Credito"
AND con.status = "Ativo"

but came NULL.

Upshot

id_crecpa  id_crecpa  descricao    diabase  dt_emissao  dt_vencto   venc    
---------  ---------  -----------  -------  ----------  ----------  --------
       87         87  Mensalidade       10  2018-01-12  2018-01-10  (NULL)

Answer 1

Flow Control with Mysql

Mysql has four flow operators (five note that the CASE has two forms, one similar to the if programming languages, and another similar to switch:

CASE        Forma 1: CASE WHEN condição THEN valor ... ELSE ... END;
CASE        Forma 2: CASE expressao WHEN v1 THEN e1 WHEN v2 THEN e2 ... END;
IF()        IF( expressão lógica, valor se verdadeiro, valor se falso );
IFNULL()    IFNULL( e1, e2 ) - Se e1 for nulo, retorna e2, senão retorna e1;
NULLIF()    NULLIF( e1, e2 ) - Se e1==e2, retorna NULL, senão retorna e1;

More details in the manual:

https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

Using the function IF in your specific case:

Mysql has the function IF, which greatly facilitates the reading of query (in T-SQL has the IIF, which is equivalent). Applying to the first part of the question:

SELECT con.id_crecpa,
    con.descricao,
    con.diabase,
    con.dt_emissao,
    con.dt_quitacao,
    IF( con.dt_vencto < CURRENT_DATE, 'vencido', '-' ) AS status
FROM crecpa con
INNER JOIN pessoa pes ON con.id_pessoa = pes.id_pessoa
INNER JOIN plano_conta pla ON con.id_plano_conta = pla.id_plano_conta        
WHERE con.id_empresa = 6;

Note that I took the last AND otherwise you won’t notice the effect of IF. What if the query is really just that there, it seems to me that has JOINs unnecessary.

Nestling IFs

Even after editing the question the IF is still simple, just two of them to solve the 3 conditions:

    IF( con.dt_vencto < CURRENT_DATE, 'vencido',
       IF( con.dt_vencto > CURRENT_DATE, 'a vencer', 'vence hoje' )
    ) AS status

In this case, the second IF enters as second argument of the first, being used only in case the date is not less than the current.

Can expand as needed:

    IF( condicao1, 'valor pra condicao 1',
       IF( condicao2, 'valor pra condicao 2',
          IF( condicao3, 'valor pra condicao 3',
             'valor caso nenhuma das 3 satisfaça'
          )
       )
    ) AS status

Equivalent with CASE:

If you still prefer to use the CASE, two is enough WHEN, because the third condition is the only one remaining if the first two comparisons are false. In this case the ELSE:

       CASE
          WHEN con.dt_vencto < CURRENT_DATE THEN 'vencido'
          WHEN con.dt_vencto > CURRENT_DATE THEN 'a vencer'
          ELSE 'vence hoje'      
       END
       AS status

Points of interest:

In Mysql the function IF has three parameters:

 IF( expressão lógica, valor se verdadeiro, valor se falso )

And every SELECT column can receive a nickname with the keyword AS. Example:

 SELECT nome AS meunome

Putting the two together, we come to this for the "loser column":

IF( con.dt_vencto < CURRENT_DATE, 'vencido', '-' ) AS status
            se preferir pode deixar em branco ^       ^ e aqui vc escolhe o nome

Just out of curiosity, I used the default CURRENT_DATE instead of the CURDATE() function to show another syntax alternative, both result in the same thing.

Answer 2

I managed to solve it, it stayed that way(I removed the name of the field that was right after the word CASE):

 SELECT con.id_crecpa,
        con.id_crecpa,
        con.descricao,
        con.diabase,
        con.dt_emissao,
        con.dt_vencto,  
           CASE
           WHEN con.dt_vencto < CURDATE() THEN 'vencido'
           WHEN con.dt_vencto > CURDATE() THEN 'a vencer'
           WHEN con.dt_vencto = CURDATE() THEN 'vence hoje'      
           END AS venc  
    FROM crecpa con
    INNER JOIN pessoa pes ON con.id_pessoa = pes.id_pessoa
    INNER JOIN plano_conta pla ON con.id_plano_conta = pla.id_plano_conta        
    WHERE con.id_empresa = 6
    AND con.dt_vencto < CURDATE()
    AND con.tipo = "Credito"
    AND con.status = "Ativo