How to do column operations where you give nicknames?

Question

I have the following hypothetical consultation with the bank:

SELECT dinheiro_na_carteira, 
(SELECT sum(despesas_pagas_carteira) FROM DESPESAS WHERE id = X ) as despesas 
FROM RECEITAS
WHERE dinheiro_na_carteira - despesas > 0

When trying to make this query it returns the following error:

[Err] 42S22 - [SQL Server]Invalid column name

Can anyone give me a suggestion how I can resolve this situation? I don’t even know what to look for to resolve.

Answer 1

Use a subconsulta correlated.

One subconsulta correlated is a subconsulta which cannot be executed independently of external consultation. The order of operations in a subconsulta correlated works like this:

1. A line is processed in the external query.
2. So for this specific line in external consultation the subconsulta is executed.

Example:

CREATE TABLE DESPESAS
(
  id INT,
  dinheiro_na_carteira INT,
  despesas_pagas_carteira INT
);

INSERT INTO DESPESAS(id,dinheiro_na_carteira,despesas_pagas_carteira)
VALUES (1,10,5),(1,15,26);

SELECT id
    ,dinheiro_na_carteira
FROM (
    SELECT id
        ,dinheiro_na_carteira
        ,sum(despesas_pagas_carteira) total
    FROM DESPESAS
    WHERE id = 1
    GROUP BY id,dinheiro_na_carteira
    ) AS despesas
WHERE dinheiro_na_carteira - total > 0

Sqlfiddle