oracle - Generate a variable in a SQL statement -

i declare variable in sql oracle statement work in next lines. write simple statement example:

select customer.surname, length(customer.name) long, customer.age  customer long > 4; 

i didn't found "clear" info on web, possible?

the order of operations select statement not same order in written.

1. from (including joins , subqueries in order of operation starts on subquery; order of operations in algebra; inside out )
2. where
3. group by
4. select
5. having
6. order by

there exceptions above not engines process quite way. appears may able use alias in group if you're using mysql. i'm not familiar enough know if changes processing or if mysql looking ahead.

in order can see executes before 'long' alias generated, db engine doesn't know long @ time it's being executed. put way, long not in scope @ time clause being evaluated.

this can solved repeating calculation in clause or nesting queries; latter less efficient.

in below i:

• aliased customer c save typing , improve readability.
• re-wrote clause use formula instead of alias
• renamed long alias due reserved/keyword use.

.

select c.surname, length(customer.name) name_len, c.age  customer c length(c.name)> 4; 

in next example use with key word generate set of data called cte (common table expression) length of name calculated. in effect changes the order in clause processed.

in case processed in cte select including our calculated value no clause applied. second query run selecting cte data set clause. since first dataset calculated name_len, can use in clause.

with cte (select c.surname, length(customer.name) name_len, c.age      customer c) select *  cte     name_len > 4; 

this done subquery; after nest few of those, can see using may make easier read/maintain.

select cte.* (select c.surname, length(customer.name) name_len, c.age        customer c) cte     cte.name_len > 4;