The company I work for is approaching its 10th anniversary this year. In that time, we’ve accumulated a variety of ways of getting data out of the system. For a ship-to report, I can run a couple variations from the N screen, or via Access through some of Carol Brown’s near-magic. And I have a web-based method based on a framework I haven’t introduced you to yet. However, what I use most often is a simple query in SQL Server Management Studio.
The reasoning behind this is the incredible flexibility it provides. I can run it wide open and get absolutely everything in my customer tables. I can run it on a particular state, city, or ZIP code. I can run it on drop-ship-flagged customers only. I can run it on customers that have ‘COB’ in their names. The point I’m getting at is, I can very quickly narrow down exactly the information I need without sorting through a spreadsheet with better than 120,000 records (and if you’re still stuck on older versions of MS Office, you can’t do that anyway…but it’s about time you upgraded Office if that is the case, considering it’s going End of Life pretty shortly.)
The basics are really pretty easy. Since the information we need lives in two tables, Customer and CustomerShipTo, we’ll use a left join to get what we need from both tables at the same time. Without getting deeply into the details, joins are one of the first things you should learn in using SQL. Joins allow us to pull data from multiple tables, matched on a key field to ensure that the data in each table corresponds to the correct match in the other table(s).
The left join is what I use most often. A left join takes all the data from the first (or left) table and matches to the sencond (or right) table, but returns all records from the left, even if there is no match on the right. There is correspondingly a right join that does the reverse, returning all records from the right even if nothing matches on the left. There are also full joins that return all records from both sides. This particular rabbit hole gets deeper, but for now, we’ll stick with a simple, single left join.
Rather than dallying around with broad examples, let’s jump right to a useful (but limited) ship-to report:
select Customer.Account, Customer.Department, Customer.DepartmentDescription,
CustomerShipTo.Name, CustomerShipTo.Address1, CustomerShipTo.Address2,
CustomerShipTo.City, CustomerShipTo.State, CustomerShipTo.Zip,
Customer.Phone
from Customer left join CustomerShipTo on Customer.Id = CustomerShipTo.CustomerId
where Customer.Name like '%bendustries%'
The select statement itself is fairly self-explanatory – I’m retrieving the bare-bones version of a ship-to – account number, department code and description, the address itself, and the phone number.
Where the magic happens is in the left join clause. I want all records from Customer, and the corresponding records from CustomerShipTo. The key is, unsurprisingly, the Customer.Id field. If you hadn’t already, open up the tree on the left side of SSMS and navigate to the Customer table and notice the key icon next to the Id field:
In parentheses after the name, notice the ‘PK’ as the first attribute. This means the Id column is this table’s primary key, a unique (meaning it contains no duplicates) column that serves as the table’s primary index. Also notice that this is a char field that is 14 characters long. (Note that it is 14 characters long, not that it has a maximum size of 14 characters as a varchar field would. I’ll come back to why the distinction is important a little later.) Now navigate to the CustomerShipTo table. What we’re looking for here is also near the top:
CustomerShipTo also has a primary key field named Id, but this isn’t what we’re looking for. (Also note that most of the DDMS SQL tables have a similar schema, their Primary Key field typically being named just ‘Id’.) What we’re interested in in this table is the CustomerId table, which corresponds to the Customer.Id table/column. Another way to tell, typically, is that it is also a 14-character char field. This stands in contrast to the CustomerShipTo.Id field, which is 24 characters long. If I try to use those in the join, I may not retrieve all the records I should, in the case the CustomerShipTo.Id field ends up with more characters than the corresponding Customer.Id field. As I recall, this is due to DDMS allowing multiple ship-to addresses on an account/department, for which that ship-to identifier becomes part of the Id field.
The next bit here is the where clause. This is where I limit my results to what I care about given the situation at hand. If I want to check what customers I have in Virginia, my where clause would look like where CustomerShipTo.State = 'VA'. Here, though, I want to check how many departments my favorite customer, Bendustries Inc, has. So I use where Customer.Name like '%bendustries%'. The percent symbols at the start and end are wildcard operators in MSSQL (and many other SQL flavors, for that matter.) I use the like operator as I’m not searching for an exact match; I want any customer records where the Name contains ‘Bendustries’, since my memory is bad and I can’t recall if they were set up as Bendustries LLC or Bendustries Inc…or just Bendustries. With this wildcard operator at the end, it doesn’t matter, as it will match 0 or more characters. The one at the beginning is a bit of laziness on my part (I mean, a valuable time-saving strategy) – the Name field is a 30-wide char field, and I don’t want to put leading spaces to pad the length of the string ‘bendustries’ until it is 30 characters long.
It is important to note the difference between the two where clauses I reference above. In the first, I use a like operator. In the second, I use the equals (=) operator. The latter will return an exact match, while the former, in conjunction with a wildcard operator, returns an inexact match. One of the challenges with DDMS’ implementation of MSSQL is that the SQL fields match the length and formatting of the current .dbf structures DDMS uses. This is mostly due to the .NET development DDMS has done over the past couple years. The new Delivery Manifest and Customer Price Plan functionality is driven by SQL, and are two of the few places where the SQL Server is writing back into DDMS itself. That being the case, the design decision was made to match the field lengths, leaving we poor developers and operators in the position of dealing with lots of leading whitespace.
Back to the report, though, if you plug my query into SSMS and Execute, you’ll get nothing, since you don’t have any companies named Bendustries in your database (right?) However, when I do it, I get this:
Notice the first line in my results. The Department and DepartmentDescription fields are blank, and the fields pulled from CustomerShipTo are all NULL. The results from Customer are simply because this is the shell (or parent, or master, depending on how you prefer to term it) and is simply the master record for the entire record. As standard practice in my company is that departmentalized accounts require everything to be on a department, the shell account doesn’t contain much. The NULL results are the result of the left join not finding a match in the CustomerShipTo table to the key provided by the record in the Customer table.
You can further your where clauses, as well, using and operators in your where clauses, along the lines of where Customer.Name like '%bendustries%' and CustomerShipTo.State = 'CA', resulting in this:
If I want to find customers in a particular city, note that I can use a where clause like where CustomerShipTo.City = 'Seattle' and it will work correctly. This is because the CustomerShipTo.City field is of the varchar type, which is a variable-length field. As I mentioned previously, the difference between the two is that a char field with a length of 30 will always be 30 characters long, whereas a varchar field with a length of 30 has a maximum length of 30, but will only be as long as the string contained within the field.
Play with this query – you can add or remove fields from the Customer or CustomerShipTo tables at will, and/or change or remove your where clause. At work, I leave SSMS open all the time, and typically have a variant of this query open, as well. I leave it open, and change my where clause depending on what I need to see. On that note, to comment out a line in MSSQL, use a double-minus (—) operator. My where clause usually looks something like this:
where
Customer.Name like '%bendustries%'
--and CustomerShipTo.State = 'VA'
--and CustomerShipTo.City = 'Houston'
--and CustomerShipTo.Address2 like '%108%'
I comment and uncomment and add or delete and operators as needed.
A good exercise might be to take this report as a baseline, and add to it results for your route codes. There are two fields in Customer, Route and ManifestRoute. Route is the Route box in Customer – Master, while ManifestRoute is in Customer – Buyers/Manifest. I’ve asked DDMS support, and they don’t have available a reference that lays out DDMS Field = SQL Field (or even better, DDMS Field = SQL Field = dbf Field), unfortunately. At some point, I’d love to create such a thing, but it’s a lot of work (I’ve got a very, very early start on it.)
I’ve shown you how to create a basic ship-to report and hopefully given you enough knowledge to expand and modify it to your own needs. In the next article, I’ll introduce another very powerful tool. As a bit of a hint, imagine being able to run this report and get the results in native Excel format, consistently laid out and formatted the same every time. In the meantime, feel free to email me at flux (at) bendustries (dot) co if you have questions or need support.
Recent Comments