Flow Control with MySQL

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:

  

link

Using the function IF in your specific case:

MySQL has the function IF , which makes it much easier to read the query (in T-SQL it has 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 else you will not notice the effect of IF . And if the query is really just there, it seems to me that it has JOIN s unnecessary.

Nesting IF s

Even after editing the question IF is still simple, with only 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 the second argument of the first, being used only if the date is not smaller than the current one.

Equivalent with CASE :

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

       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 IF function has three parameters:

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

And every SELECT column can be given 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 curious, I used the default value CURRENT_DATE instead of the CURDATE () function to show another syntax alternative, both result in the same thing.

I managed to solve it, it was this way (I removed the name of the field that was just 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