sql union same parameters for two where = how to change it to one parameter -

i have query

select last_name, job_id, department_name, country_name, hire_date      , sysdate, salary employees e    , departments d    , locations l    , countries c  e.department_id = d.department_id   , d.location_id = l.location_id    , l.country_id = c.country_id    , upper(last_name) upper('%&name%')  union   select last_name, j.job_id, department_name, country_name, start_date      , end_date, to_number(null)  employees e   , job_history j   , departments d   , locations l   , countries c  j.department_id = d.department_id    , d.location_id = l.location_id    , l.country_id = c.country_id    , j.employee_id = e.employee_id    , upper(last_name) upper('%&name%'); 

as see on both select in union 'like upper('%&name%')' way don't ask both time pass first select second in union?

thank answers.

in databases, can use cte:

with t (       select last_name, job_id, department_name, country_name, hire_date,              sysdate thedate, salary, employee_id       employees e,            departments d,            locations l,            countries c        e.department_id = d.department_id             , d.location_id = l.location_id              , l.country_id = c.country_id              , upper(last_name) upper('%&name%')     ) select last_name, job_id, department_name, country_name, hire_date, thedate, salary t union  select last_name, job_id, department_name, country_name, hire_date, thedate, null t join      job_history jh      on jh.employee_id = t.employee_id; 

the use of sysdate makes me think of oracle, support with statement.

by way, should learn use proper join syntax, uses join keyword in from clause. there example in query.