Wrap your query that uses alias as a sub query and use main query to reference the alias. For example:
select * from (
select t.tenantname as organization
, u.username as user
, r.rolename as role
from jiuser u
inner join jiuserrole j on j.userid = u.id
inner join jirole r on r.id = j.roleid
inner join jitenant t on t.id = u.tenantid
) t
where organization = 'root'
tchen is correct: the column aliases are established in the SELECT clause, but the WHERE clause is run after the FROM (and can therefore use table aliases) but before the SELECT. So you either user the actual value like t.tenantname in our WHERE (or even repeat a calculation or function if necessary), or else if the manipulation you did in the SELECT (calculations/functions/CASE statements, etc.) was too taxing or wordy to repeat in your WHERE clause, then you can instead wrap an outer query around the inner one, and the entire outer query (including a WHERE clause) has all of the column aliases from the inner query in scope, e.g.:
select
date_category,
amount
from
(
select
case
when tran.posting_date is null then 'Null'
when tran.posting_date<CURRENT DATE - 1 year then 'Long ago'
when tran.posting_date<CURRENT DATE then 'Recent past'
when tran.posting_date=CURRENT DATE THEN 'Today'
else 'Future'
end as date_category,
tran.amount as amount
from
tran
)
where
date_category in ('Long ago', 'Future')
Wrap your query that uses alias as a sub query and use main query to reference the alias. For example:
select * from (
select t.tenantname as organization
, u.username as user
, r.rolename as role
from jiuser u
inner join jiuserrole j on j.userid = u.id
inner join jirole r on r.id = j.roleid
inner join jitenant t on t.id = u.tenantid
) t
where organization = 'root'
tchen is correct: the column aliases are established in the SELECT clause, but the WHERE clause is run after the FROM (and can therefore use table aliases) but before the SELECT. So you either user the actual value like t.tenantname in our WHERE (or even repeat a calculation or function if necessary), or else if the manipulation you did in the SELECT (calculations/functions/CASE statements, etc.) was too taxing or wordy to repeat in your WHERE clause, then you can instead wrap an outer query around the inner one, and the entire outer query (including a WHERE clause) has all of the column aliases from the inner query in scope, e.g.:
select
date_category,
amount
from
(
select
case
when tran.posting_date is null then 'Null'
when tran.posting_date<CURRENT DATE - 1 year then 'Long ago'
when tran.posting_date<CURRENT DATE then 'Recent past'
when tran.posting_date=CURRENT DATE THEN 'Today'
else 'Future'
end as date_category,
tran.amount as amount
from
tran
)
where
date_category in ('Long ago', 'Future')
it works fine, thanks for the tips