.st0{fill:#FFFFFF;}

System21 – Phone numbers for any customers on sales orders 

 May 21, 2016

By  NickLitten

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/

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad 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 remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

Iā€™m always looking for awesome input, feedback and critique!

>