Jump to content

Recommended Posts

Posted

Hello: 

I tried to use alias name for the field in the where clause, and it will not work, anyone knows how to work around it?

 

 

thanks 

Eric

 

 

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Posted

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'

 

Posted

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')

 

 

 

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...