Case & When in Select

Asked

Viewed 31 times

0

SELECT
    c2.quantidade AS qtde_solicitada,

IF (
    (
        SUM(c1.quantidade) - c2.quantidade
    ) < 0,
    '0',
    c2.quantidade
) AS estoque,
 (
    SELECT
        SUM(quantidade_autorizada)
    FROM
        ferramental_requisicao
    WHERE
        devolvido = 1
) AS devolvido,

IF (
    c2.quantidade <= devolvido,
    'ferramental_estoque',
    'ferramental_requisicao'
) AS tabela,
 CASE
WHEN tabela = 'ferramental_requisicao' THEN
    (
        SELECT
            *
        FROM
            ferramental_requisicao
        WHERE
            id_ativo_externo = c1.id_ativo_externo
        AND devolvido = 1
    )
ELSE
    'pare aqui'
END AS retorno
FROM
    ferramental_estoque AS c1
JOIN ferramental_requisicao AS c2 ON c2.id_ativo_externo = c1.id_ativo_externo

Error: column tabela there is no

How can I suit?

mysql

  • cannot make a case with an alias of the same select, in this case "table", need to select separately to use it, and use WITH

  • @Ricardopunctual you can post a suggestion?

1 answer

1


A suggestion would be to adapt the data according to the need, generating these attributes within the FROM to be consumed on another level, an example would be like this,

SELECT
c3.qtde_solicitada,
c3.estoque,
c3.devolvido,
c3.tabela,
CASE
WHEN c3.tabela = 'ferramental_requisicao' THEN
    (
        SELECT
            *
        FROM
            ferramental_requisicao
        WHERE
            id_ativo_externo = c3.id_ativo_externo
        AND devolvido = 1
    )
ELSE
    'pare aqui'
END AS retorno
FROM (
    SELECT
        c2.quantidade AS qtde_solicitada,
        c1.id_ativo_externo
    IF (
        (
            SUM(c1.quantidade) - c2.quantidade
        ) < 0,
        '0',
        c2.quantidade
    ) AS estoque,
    (
        SELECT
            SUM(quantidade_autorizada)
        FROM
            ferramental_requisicao
        WHERE
            devolvido = 1
    ) AS devolvido,

    IF (
        c2.quantidade <= devolvido,
        'ferramental_estoque',
        'ferramental_requisicao'
    ) AS tabela,

    FROM
        ferramental_estoque AS c1
    JOIN ferramental_requisicao AS c2 ON c2.id_ativo_externo = c1.id_ativo_externo
) as c3;

Since I don’t have the database, I can’t guarantee it’ll work, but that would be the idea

Browser other questions tagged mysql

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