Some and's and some or's

select urn
from mailable_donors
where last_donation_date > '01-Sep-96'

select urn
from mailable_donors
where last_donation_date < '01-Sep-96'
and last_donation_amount > 100

select urn
from mailable_donors
where last_donation_date < '01-Sep-96'
or last_donation_amount > 100

select urn
from mailable_entity
where goneaway != 'Y'
and (high_donor = 'Y' or orig_source_code = 'EV93113332')
and deleted != 'Y'
and dead != 'Y'

Trap!! Sometimes people say :-

where name = "Smith" and name = "Jones"

because they want Smith and Jones. This will give you an empty result, because you are asking for people who have a name which is both Smith and Jones. This is not possible, at least not in this universe.

What they really mean is:-

where name = "Smith" or name = "Jones"

---

Top of page
Some and's and some or's | A few like's
Some between's | Matching lists using some in's
Selecting from more than one table or view - joins
Some selections which won't do the right thing...

---

A few like's

select urn
from mailable_donors
where postcode like 'N19%'

Trap!! Sometimes people say :-

where postcode like 'N1%'

This will give you all the postcodes that start with N1: ie N1, N11, N12 etc

Tip! You can however extract N1 only by including a blank space:-

where postcode like 'N1 %'

select urn
from mailable_donors
where title like 'Mr%'

Trap!! Sometimes people say :-

where title like 'Mr *'

This is an understandable mistake, since * is used as a wildcard in the system in some places. Here, it means you are literally searching for an asterisk. This is one of InFoE's little jokes.

---

Top of page
Some and's and some or's | A few like's
Some between's | Matching lists using some in's
Selecting from more than one table or view - joins
Some selections which won't do the right thing...

---

Some between's

select urn
from mailable_entity
where creation_date between '01-jan-97' and '31-may-97'

select urn
from people
where orig_source_code between 'EM97066693' and 'EM97066699'
or orig_source_code between 'RC92062023' and 'RC92062035'

select urn from mailable_entity
where postcode between 'CH5' and 'CH99'
or postcode like 'LL%'

---

Top of page
Some and's and some or's | A few like's
Some between's | Matching lists using some in's
Selecting from more than one table or view - joins
Some selections which won't do the right thing...

---

Matching lists using some in's

select urn from
all_members
where goneaway != 'Y'
and suppress_all != 'Y'
and country_code not in ('GB','EN','SO','WA', '','UN')
and postcode not like 'BT%'

Tip! Use:-

where goneaway != 'Y'

rather than

where goneaway = 'N'

as some records do have goneaway as a blank field, and this statement will not find them.

Tip! Exclude blank rows by using 2 single-quotes together, ie ''. For example:-

where country_code not in ('GB','EN','SO','WA', '','UN')

Records without country_codes will not be included by your selection.

select urn from mailable_members
where sex = 'F'
or title in ('MISS','MRS','MS', 'Ms', 'Miss','Mrs')
or title like '%Mrs%'
or title like '%MRS%'

select urn
from mailable_entity
where country_code in ('UN', 'CI')

---

Top of page
Some and's and some or's | A few like's
Some between's | Matching lists using some in's
Selecting from more than one table or view - joins
Some selections which won't do the right thing...

---

Selecting from more than one table or view - joins

select p.urn from people p, communications c
where c.urn = p.urn
and p.high_donor = 'N'
and c.source_code = 'HD93052964'

An explanation of joins.

See also selection 66 on the training system, which is a good example of many of the above.

Trap!! Do NOT join a view to another view under any circumstances: your selection will run extremely slowly.

Try to avoid joining a view to a table whenever possible, especially if the table is one of the really big ones: i.e. Gifts or Communications. Ask the FDM whether there is any way to avoid making this sort of join.

---

Top of page
Some and's and some or's | A few like's
Some between's | Matching lists using some in's
Selecting from more than one table or view - joins
Some selections which won't do the right thing...

---

Some selections which won't do the right thing...

Test yourself. The following will either be rejected when you try to save them, or will not do what you expect. Can you see what's wrong with them? ( Answers here )

Question

1. select urn
   from mailable_donors
   where postcode = 'KA%'

2. select urn
   from mailable_donors
   where postcode like 'KA*'

3. select urn
   from mailable_donors
   where last_donation_date like '01-Sep-%'

4. select urn
   from mailable_members
   where title = ('MISS','MRS','MS', 'Ms', 'Miss','Mrs')

5. select urn
   from mailable_entity
   where postcode between 'N1%' and 'N19%'

6. select urn
   from pledges
   where update_date > date('today') -'6 months'

7. select urn
   from mailable_members
   where raffles = 'N'
   or country_code = 'SO'
   and payments_per_year = 12