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.
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/
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
Example RPG program to populate the Infor System21 *LDA
RPG example – How to retrieve System21 Background Job status
Toby Schofield 3PSC – Chicago IBMi (AS400) JBA Consultant
Start Stop System21 Background Jobs (WHSE, GL, IN, OE – BACK3)
Can I upgrade JBA SYSTEM21 from IBM-i v5r4 to V6 or V7?
another as400 jba date conversion routine
User guide to the JBA System21 application on the AS400
The rise and fall of JBA software – AS400 ERP System21