I have the following hypothetical query to 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 do this query it returns the following error:
[Err] 42S22 - [SQL Server] Invalid column name
Can anyone give me a suggestion how can I resolve this situation? I do not even know what to look for.
Use a% correlated%.
A% correlated% is a subconsulta that can not be executed independently of the external query. The order of operations in a subconsulta correlated works like this:
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