Stuck on trying to set a condition based on a temporary table i created within the following query:
I get an error: Invalid column name ‘amount_usd’.
Any thoughts on why my “>10” condition isn’t referencing the temporary ‘amount_usd’ and any suggestions to correct this?
Many thanks in advance.
SELECT USER_ID, AMOUNT, round((amount * fx.rate),2) as AMOUNT_USD, CREATED_DATE FROM ( SELECT * , ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY user_id, created_date) AS RN FROM transactions) a join fx_rates fx on (fx.ccy = currency and base_ccy = 'usd') WHERE RN=1 and state = 'completed' and type = 'card_payment' and amount_usd > 10 order by created_date