SQL Error (207): Invalid column name 'BTC'

January 17, 2022

Any idea why this query returns the error "SQL Error (207): Invalid column name ‘BTC’"?

I’m just trying to use the WHERE clause after the JOIN staement

WITH balances AS (
    SELECT 
        UserId,
        Balance,
        CASE
            WHEN CurrencyId = '123' THEN 'BTC'
            WHEN CurrencyId = '456' THEN 'USD'
            WHEN CurrencyId = '789' THEN 'BSV'
            WHEN CurrencyId = '987' THEN 'CAD'
            WHEN CurrencyId = '765' THEN 'ECD'
            WHEN CurrencyId = '543' THEN 'XLM'
            WHEN CurrencyId = '321' THEN 'ETH'
            WHEN CurrencyId = '0123' THEN 'GBP'
            WHEN CurrencyId = '0345' THEN 'BCH'
            WHEN CurrencyId = '0567' THEN 'EUR'
            WHEN CurrencyId = '0789' THEN 'USD as WSD'
        END AS Currency
    FROM "mydb"."dbo"."Balances" balance
    WHERE balance.Balance > 0.01
)
SELECT TOP 500
    curUser.Id,
    curUser.Email,
    balance_btc.Balance AS BTC,
    balance_btc.Currency,
    curUser.LastBalanceSnapshot
FROM "mydb"."dbo"."Users" AS curUser
INNER JOIN balances balance_btc ON balance_btc.UserId = curUser.Id
WHERE balance_BTC.Currency = "BTC";

>Solution :

You appear to be using the incorrect text qualifier in your WHERE clause – the double-quotes indicate an identifier, not a value. In other words, your WHERE clause is written in a way that SQL Server is trying to find an equality between two columns, rather than a column equal to a value.

Change your code so that your WHERE clause reads
WHERE balance_BTC.Currency = 'BTC'; and you should find that the error is resolved.