During a meeting this morning, I was asked to provide a list of all Customer phone numbers for any System21 Customers that have ever been used on a Sales Order.

Perhaps this sounds like a complicated request but… have no fear SQL is here!!! In reality, its a very simple thing to find and takes just a few seconds using a SQL INNER JOIN.

How can we use SQL to find this data?

In this example, I’m working on a data cleanse to make sure that the phone numbers are properly formatted before sending them to FEDEX as part of the shipment information. We know that some of our data is badly formatted so lets quickly see how many are good, bad or ugly. Using SQL we can see all CUSTOMER entries from the Customer Master file (SLP05) which have been referenced on the Sales Order Header file (OEP40).

The SQL command goes like this:

SELECT cusn05, dseq05, cnam05, phon05 
 FROM slp05 
 WHERE EXISTS 
 (SELECT * 
 FROM oep40 
 WHERE slp05.cusn05 = oep40.cusn40)

This is saying show me the Customer Number (cusn05), Delivery Sequence code (DSEQ05), Customer Name (CNAM05) and Phone Number (PHON05) from the Customer Master file (SLP05) for all customers that exist in the Sales Order Master file (OEP40). The results will looks something like this:

....+....1....+....2....+....3....+....4....+....5....+....6....+....7..
 Customer Delivery Customer Telephone 
 address Name number 
 cod 
 12345 000 BOB SMITH (123) 456 1000 
 555444 000 CLANGER METALS 455-1234 X400

etc etc

Simple as Pie… not the 3.14159 one 😉

NOTE: If you want to know more about Joins I recommend this excellent article http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/