Condition unable to reference temporary table

Get Help SQL
#1

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
#2

Where does that stray a come into play?

Will that raise a syntax error (missing EOS)?

#3

Hi mtf,

The a is a table alias for

(
	SELECT * , ROW_NUMBER() 
	OVER(
		PARTITION BY user_id 
		ORDER BY user_id, created_date) 
	AS RN FROM transactions)

its basically

FROM a [a basically an expression to remove duplicates, it gives me the first instance of user_id in chronological order, removing all subsequent instances]

the entire query works fine if I use the condition

amount > 10

but I do not want the condition to apply to the original amount, I want it to apply to the converted USD amount, as such:

amount_usd > 10

but I get invalid column name ‘amount_usd’ even though I defined it on line 4, as so:

round((amount * fx.rate),2) as AMOUNT_USD,

1 Like
#4

I may be wrong, but doesn’t AMOUNT_USD need to be in quotes?
Edit: Nevermind. It doesn’t since there’s no spaces.

#5

Where is it referenced? One would expect to see, a.column somewhere, or am I off my rocker?