How to make a CASE WHEN for named expressions?

Asked

Viewed 83 times

-1

Good morning!

I’m having trouble making one CASE WHEN of a named expression. I have a SELECT in which one of the parameters has a named expression.

Example:

ROUND(NFI.NOTAFISCALITEM_VALORMARGEMGERENCIAL, 2))MARGEM

I want to make a check so that this expression does not return values NULL. I tried to do so:

CASE WHEN (ROUND(NFI.NOTAFISCALITEM_VALORMARGEMGERENCIAL, 2)MARGEM) <> NULL THEN MARGEM ELSE 0 END

But that way I get a mistake saying the following:

SQL Error [102] [S0001]: Incorrect syntax near 'MARGEM'.

Can someone help me?

mysql sql
  • 3

    Assuming that what you posted is on the SELECT List or in the WHERE clause see what the manual says: "It is not permissible to refer to a column alias in a WHERE clause, because the column value Might not yet be determined when the WHERE clause is executed.". However if you just want 0 to appear when the field is NULL use the COALESCE function: COALESCE(ROUND(NFI.NOTAFISCALITEM_VALORMARGEMGERENCIAL, 2), 0) AS MARGEM

  • It worked here, thank you very much! puts what you said as an answer for me to give the Check

2 answers

1

Put a AS (AS "means alias, we can call another name a given table or column using an alias") before MARGEM

ROUND(NFI.NOTAFISCALITEM_VALORMARGEMGERENCIAL, 2) AS MARGEM

  • I have tried this but it returns the following error: "SQL Error [156] [S0001]: Incorrect syntax near the keyword 'AS'."

-2

CASE WHEN (ROUND(NFI.NOTAFISCALITEM_VALORMARGEMGERENCIAL, 2)MARGEM) 
     <> NULL THEN MARGEM,
     ELSE
        0
     END AS MARGEM
FROM tabela
WHERE id > 0;

Browser other questions tagged mysql sql

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