How to avoid using UNION?

Rob8111

Hi, I have a table Employees with

employee_name

Age

In apex page I have a page item(p123_page_selector), which is a radio button. Its values are ALL and AGED.

So, when user selects 'ALL', I want to return all data from table employees.

When user selects 'AGED', I want to return all data from employees where age greater than 35.

I wrote query like below.

How to write this query without using 'UNION'?

cormaco

Like this (untested):

select *
from employees
where
  :p123_page_selector = 'ALL'
  or
  (age > 35 and :p123_page_selector = 'AGED')

Two comments on your select:

UNION forces the removal of duplicates which is not necessary here, UNION ALL is sufficient.

You should not store the age of a person in the database, store the birthdate instead.

Frank Kulash

Hi, @Rob8111

In apex page I have a page item(p123_page_selector), which is a radio button. Its values are ALL and AGED.

If :p123_page_selector will always be either 'ALL' or 'AGED', then you can simplify Cormaco's solution like this:

select *
from employees
where
  :p123_page_selector = 'ALL'
  or
  age > 35

cormaco

Like this (untested):

select *
from employees
where
  :p123_page_selector = 'ALL'
  or
  (age > 35 and :p123_page_selector = 'AGED')

Two comments on your select:

UNION forces the removal of duplicates which is not necessary here, UNION ALL is sufficient.

You should not store the age of a person in the database, store the birthdate instead.

Frank Kulash

Hi, @Rob8111

In apex page I have a page item(p123_page_selector), which is a radio button. Its values are ALL and AGED.

If :p123_page_selector will always be either 'ALL' or 'AGED', then you can simplify Cormaco's solution like this:

select *
from employees
where
  :p123_page_selector = 'ALL'
  or
  age > 35

Rob8111

Hi @cormaco, thanks for your time. It is working as expected.

Rob8111

Hi @Frank Kulash, thanks for your time. Your approach is also working.