SQL Duplicate Records - Distinct keyword

Every run into a situation where records in your tables have no unique field and for some reason there are duplicate records? Well, this happened to me in our EDI database in our invoice tables and was cause some errors during the import process in Gentran Server. Here is what I did:

First I created temporary tables that mirrored my production tables. (You can get the script for this through SQL Enterprise Manager by selecting the tables and under Action->All Tasks->Generate SQL Script).

Now pull only one of each of the duplicate records into the temp database using the the 'distinct' keyword:

insert into invoiceheadertemp
select distinct * from invoiceheader where tranno = 217589 or tranno = 217590 or tranno = 217591

insert into invoicedetailtemp
select distinct * from invoicedetail where tranno = 217589 or tranno = 217590 or tranno = 217591

Now delete the records from the production database:

delete from invoiceheader where tranno = 217589 or tranno = 217590 or tranno = 217591

delete from invoicedetail where tranno = 217589 or tranno = 217590 or tranno = 217591

Now insert the unique records from the temp tables:

insert into invoiceheader select distinct * from invoiceheadertemp where tranno = 217589 or tranno = 217590 or tranno = 217591

insert into invoicedetail select distinct * from invoicedetailtemp where tranno = 217589 or tranno = 217590 or tranno = 217591

Someone might ask: "Why not use real temp tables with the # prefix?" Well, these temp tables are also my testing tables that I use to test modified stored procedures as well as testing maps in Gentran Server, so it made sense for me to go ahead and duplicate the tables for this dual purpose.

I hope this helps someone who finds themselves in a similar situation and let me know if it does!

EDI System Design

Intro

My company has been doing EDI now for many years. Initially our IT Director was manually processing the 850s (Purchase Orders) into our system for larger companies that mandated we do EDI with them. Gradually these big dogs mandated that we start to send them 856 (advance ship notice) and 870s (order status report). These documents were designed to make their life and business seamless while pushing our IT staff and resources to the max. This continues to happen on a regular basis. We have within the last year begun to move our EDI service to a more scalable product from Sterling called Gentran Server. Although I did not design the current setup, I thought it might be helpful for those out there that are looking at EDI solutions for your company.

The Basics

Since I assume most reading this will know the basics about EDI, I will not go into the details of sending and receiving documents (maybe in another post). I will instead deal with what happens with a document once we received it as well as how an EDI document is exported out of our enterprise system. The two diagrams to the left give you a general idea of how we handle EDI.

The Interim EDI Database was something that was designed and implemented before my time as the EDI guy. This step in the document process is perhaps the most important and was only made possible with the use of Gentran Server. While it is possible to map from Gentran Server directly into our Enterprise System, the thought was that our company is still growing and who knows what the future holds as far as our Enterprise System. Lets talk a little more about this database and its structure.

Interim EDI Database

Our Interim EDI Database has a set of tables for each set of document transactions we send or receive with our trading partners. For instance, we have an InvoiceHeader, InvoiceDetail and a PartnerControlInvoice table that are used to process only Invoices. We have a few other tables that are shared between multiple processes such as ItemTranslation, ItemCustSkuTranslation, and CarrierCode tables that have global data that is the same regardless of the transaction type.

When designing these tables, it is important to make sure all of the data you will ever need to process that document is contained in these tables. One might not think that the tracking number is important on an invoice, but some of our trading partners require us to send that to them on the invoice. Gather all of your trading partner EDI specifications and make sure you know what data you will need in your table design. It also doesn't hurt to gather a little more information from your Enterprise System or EDI documents even if you don't need it now. You may find a future trading partner may need this data.

Database Interfaces

The most time consuming part of the process was putting together the stored procedures to pull/push the data out of and into our Enterprise System. Getting the data out of the system required digging into the its tables and finding the data you need to create the outgoing document. Again, it helps to know what data you absolutely need before you begin this dig. A helpful tip here is to get as much data from one set of tables as you can. For example, if you are looking for invoice data, look for it in the invoicing table(s) before you go and look in the sales order or shipment table(s). This will save you many headaches later...trust me.

Getting the data into the Enterprise System is a completely different process. You need to know what data your system needs in order to process the document. In our case, Sales Orders are the only documents we automate into our Enterprise System. The best way to figure out what you need and where it needs to go is to run a SQL trace while someone manually processes an order. It is very important that you follow each step of the process exactly like the system does. Failure to do so will result in either errors in the Enterprise System (helpful but frustrating) or no errors at all with the Sales Order not getting in the system (just plain frustrating). It helps if someone who designed or maintains the Enterprise System can help you with this process. A SQL guru would be nice as well, if not, Google is your friend.

Why not an out the box product for the database and stored procedures?

For some the option of creating your own database and stored procedures is not feasible. For those I would say it makes sense to look at a product that interfaces with your Enterprise System. Our old system used this type of interface, and while it worked, it did not fit into our long term goals and needs. The product only works with our current Enterprise System. If we made an Enterprise System change, we have had to purchase a new product as well as rewrite all of the maps and go through the testing with our trading partners all over again. With our current set up, we need only redo the stored procedures to move the data into and out of a new Enterprise System. Nothing else changes.

In addition to learning a new Enterprise System, we would have had to learn a new mapping process as well. Learning another proprietary mapping language was not something we were fond of doing either. There are other reasons we didn't like the old out of the box solution, but I would rather focus on what is good about our current set up.

Gentran Server

Gentran Server is an all in one product that allows for mapping and document transmit/receive management. I was not involved with selection of this product, but I was told that the mapping process was more intuitive than any of the other products that were looked at. I can't speak for the other examples, but Gentran Server is quite intuitive. I am sure it also helped that we have an outstanding relationship with Sterling Commerce with us using some of their other product lines.

I think I will reserve some additional thoughts about Gentran Server for later posts. I have learned a few things about the product that may benefit a some of you out there including a few undocumented issues that can be worked around, but can be quite frustrating trying to figure out. On a plus note, their technical services team is reachable and generally responsive to the questions I have.

Summary

It is important to evaluate what your long term EDI goals are before designing your EDI system. We were not entirely convinced that our current Enterprise System will be our last. This thought necessitated us designing a loosely couple EDI system. So far it has served us well and will likely serve us well in the future.

Future Topics

Here are some future EDI topics I have been thinking about. Leave me a comment and let me know what you would like to see...even if it isn't on this list.

EDI Web Interface - Getting EDI troubleshooting in the hand of the less technical

Gentran Server - common issues and the corresponding solutions

EDI Basics - Frequently used EDI terminology

EDI Data Files - Displaying them in web interfaces (812's, 820's, 852's)

EDI Error handling - Dealing with missing mandatory data