How to Query a Database and Write the Data to an EDIFACT Document

In this tutorial, you will learn how to query a database table and write the purchase order data to an EDIFACT document using version D18B of the ORDERS transaction set from the Add-on Marketplace.

Create a New Project

To begin, create a new Project following the Getting Started with Projects tutorial.

SQL Select

From within the Project Designer, expand the Database folder in the Component Library, and then drag the SQL task to the Project Outline.

SQL Task

On the SQL task, specify the Database Server from the drop-down list.

SQL Task

Click the Add button to Add a Query to the SQL task. In this example, the table being queried is titled “purchase_order”. We will specifically query the Beginning of Message Segment (BGM) and Date/Time/Period Segment of an EDIFACT purchase order. The following is an image of the BGM and DTM data contained in the database table.

Purchase Order Database Entry

Type in your SQL select statement in the SQL Statement field. Alternatively, you can use the SQL Wizard for quickly building the Select statement. With the SQL Wizard, you can choose schemas (libraries), tables (physical files), columns (fields), column headings, “where” clauses, and “order by” criteria. To access the SQL Wizard for building a Select statement, click the browse icon on the right side of the SQL Statement field. See the SQL Wizard tutorial for more information.

Name the output variable that will contact the table data. For this example, we will name the variable “RowSet” because it contains both the BGM Segment data and DTM Segment data.

SQL Query Statement

Write EDIFACT

Expand the Data Translation folder in the Component Library and drag the Write EDIFACT task to the Project Outline.

  • On the Write EDIFACT task, specify values for the following attributes:
  • Output file – the path and file name of the EDIFACT document to write.
  • Sender ID – the identifier of the sender.
  • Receiver ID – the identifier of the receiver.
  • Interchange Control Reference – the unique reference assigned by the sender to an interchange.
  • EDIFACT Transaction Set – Select an EDIFACT Transaction Set from the list of those installed. In this example, select the ORDERS Purchase order message – D18B Transaction Set. Transaction sets can be downloaded from the Add-on Marketplace.

Write EDIFACT Task

Click the Launch Data Mapping Wizard button on the Write EDIFACT task main panel to open the EDIFACT Data Mapper. When you first launch the EDIFACT Data Mapper, you will see an input RowSet container and an EDIFACT Transaction Set container. The input RowSet container should be named after the Output Variable defined in the SQL task. The order of the columns contained in the RowSet variable should mirror the order of those same columns in the database purchase_order table. When finished mapping Transaction Set elements to the input RowSet container, click Apply.

Write EDIFACT Data Mapper

The following is an image of the Project Outline for the example Write EDIFACT Purchase Order Project.

Write EDIFACT Project Outline

Execute Project

Press the Execute button in the toolbar of the main panel. The Project will save, validate, and then execute. Once the Project has finished executing, a View Job Log link is displayed. You can review the log or download it as a text file that can then be emailed to tech support if requested.

Below is an image of the EDIFACT file generated by this Project. The file contains the attributes defined in the Write EDIFACT task as well as the BGM and DTM data mapped using the data mapper.

EDIFACT File

Still have questions? Browse our forum, post a question, or live chat with a technician now!