AnalysisException: Could not resolve column/field reference: 'transaction_nominal_value' in SQL Impala

Question

I have the following SQL query where I have two datasets CSDB and MMSR. I merge these two datasets to th Final dataset. So far everything works fine. Now I want to aggregate the data on trade_date but I get the error message: AnalysisException: Could not resolve column/field reference: 'transaction_nominal_value'. I am working in Impala. How can I resolve the problem?

--CSDB
WITH CSDB AS (
    SELECT isin, nominal_currency, amount_outstanding, issue_price, amount_issued, yield, original_maturity, residual_maturity
    FROM csdb_pq
    where nominal_currency = "EUR" 
    ),
    
--MMSR
MMSR AS (
    SELECT transaction_nominal_amount, maturity_days, deal_rate, collateral_haircut, collateral_isin, collateral_nominal_amount, trade_date
    FROM datashop_store_business_mmsr.secured_vl_pq
    WHERE collateral_isin IS NOT NULL
    ),

---Join
Final AS (
    SELECT *
    FROM MMSR
    LEFT JOIN CSDB
    ON MMSR.collateral_isin = CSDB.isin
    )

--Aggregate Data 
SELECT trade_date, AVG(transaction_nominal_amount)
FROM Final 
GROUP BY trade_date;

Answer 1

Should be transaction_nominal_amount, not transaction_nominal_value per your table elements within your CTE. Just a simple column name error and it should work.