Updating Values in a Table

Example: Creating a Synonym for a CSV (Comma Separated Values) File

The source table for this tutorial can be found in the application directory where you created the tutorial files. By default, this location is the ibisamp directory.

From the DMC:

1. Right-click ibisamp, point to New, and click Synonym (Create or Update).

   The Select adapter to configure dialog box opens.

2. Under the Delimited Flat File folder, click <local> and click OK.

   The Select Data File dialog box opens.

3. In the Data Files Location box, enter ibisamp.
4. In the Extension box, enter csv.
5. Click Next.

   The Select Synonym candidates dialog box opens.

6. Select the checkbox next to the sales_orders synonym.

   To view the CSV file, right-click sales_orders and click Show File. Click the X in the upper right hand corner to close the view.

7. Set the following parameters to create the synonym:
   1. In the Field Delimiter, leave comma selected.
   2. In the Field Enclosure box, select " (double quote) from the drop-down menu.
   3. Select the Header row checkbox.
   4. Expand the Customize Data type mappings section. In the Decompose Data fields into components box, select OFF - Turn Off from the drop-down menu.
   5. In the Prefix box, enter dm.
8. Click Next.

   The Status dialog box opens.

9. Ensure that the status shows "Created successfully" and no other messages. Click Close.
10. To view the data through the synonym, expand the ibisamp directory, right-click dmsales_orders, and click Sample Data.

Example: Creating an Initial Load Data Flow

The next step is to create a Data Flow that creates a new target table and loads the first month of data. In this example, we will be using mysamp as the application directory.

From the DMC:

1. Right-click mysamp, point to New, and click Flow.

   The Data Flow workspace opens.

2. Right-click the left hand side of the workspace and click Add Source.

   The Select Synonym dialog box opens.

3. Click the ibisamp directory and then click dmsales_orders. Click Select.

   The source synonym is added to the workspace.

4. Double-click the SQL object to open it.
5. Hold the Ctrl key to select and select the PRODUCT_NUMBER and PRODUCT_NAME columns. Click the >> button to add them to the Selected Columns section.
6. With both columns selected, select Group By from the Aggregates drop-down menu.
7. From the Available Columns section, hold the Ctrl key and select the REVENUE and QUANTITY columns. Click the >> button to add them to the Selected Columns section.
8. With both columns selected, select Sum from the Aggregates drop-down menu, as shown in the following image.
   
9. Click OK to close the Column Selection dialog box.
10. Right-click the SQL object and click WHERE Filter.

    The WHERE Filter calculator opens.

11. Click the Conditions tab.
12. Right-click the ORDER_DATE column, point to Insert Function, and click Return part month of date from the list of functions.

    The expression MONTH(T1.ORDER_DATE) is added.

13. Enter =1 at the end of the expression, as shown in the following image.
    
14. Click OK to close the WHERE Filter calculator.
15. Right-click the right side of the workspace, point to Add Target, and click New.
16. Right-click the target object and click Properties.

    The Properties panel for the target opens.

17. Enter the following values:
    1. For Adapter, select any adapter for a relational database.
    2. For Synonym, enter the name of your application directory and a name for the target table For example, mysamp/dmsales_counts.
    3. For Load Type, leave it as Insert/Update.
    4. In the Key box, under the Adapter Specific section, enter or select PRODUCT_NUMBER.
18. On the Flow tab, from the Run group, click Run and then click Submit.
19. Click OK in the Save changes dialog box.

    The Save Procedure As dialog box opens.

20. In the Name box, enter create_dmsales_counts.
21. Click Save.

    You should see a message in the Console Log at the bottom of the DMC that says Request mysamp/create_dmsales_counts complete.

    Note: If the Console Log is not visible, on the Home tab, from the View group, click Console Log.

22. On the Flow tab, from the Reports group, click View Last Log to view the number of rows that were inserted. Click the X on the tab to close the log view and return to the flow.
23. To view the data that was loaded, right-click the target object, point to Operations, and click Sample Data. The report shows the product information that was loaded, specifically, the quantity of each. For example, Product Number 1005 shows a Quantity of 2, as shown in the following image.
    

Example: Creating an Update Data Flow

In this step, we will start with the initial load flow and edit it so that it updates values in the target table by adding new to existing values.

1. In the Data Flow that was just created, right-click the SQL object and click WHERE Filter.

   The WHERE Filter calculator opens.

2. Click the Conditions tab.
3. In the expression, change the number 1 to a 2.
4. Click OK.
5. Right-click the target object and click Delete.
6. Click Yes to confirm that you want to delete the target object.
7. Right-click the right hand side of the workspace, point to Add Target, and click Existing.

   The Select Synonym dialog box opens.

8. Select the application directory you used to create the target table. In this example, it is mysamp. Then, select the target table in the available list, for example, mysamp/dmsales_counts.
9. Click Select.
10. Right-click the new target object and click Properties.

    The Properties pane opens.

11. Select Update the existing record from the If the record exists drop-down menu.
12. Double-click the target object.

    The Transformations dialog box opens.

13. Click the AutoMap button to automatically map the source to the target columns. This defines the assignments that are done from source to target when inserting a new row, as shown in the following image.
    
14. Click the On Match Expressions tab.
15. In the Target Columns section, double-click PRODUCT_NUMBER.

    The On Match Transformation calculator opens.

16. Double-click REVENUE to add it to the expression. This is the value from the source table.
17. Click the plus sign.
18. Expand dmsales_counts and double-click REVENUE. This will add D.REVENUE to the expression, which is the value from the target table.
19. Click OK to close the On Match Transformation calculator.
20. In the Target Columns section of the Transformations dialog box, double-click QUANTITY .

    The On Match Transformation calculator opens.

21. Double click QUANTITY to add it to the expression.
22. Click the plus sign.
23. Expand dmsales_counts and double click QUANTITY. This will add D.QUANTITY to the expression, which is the value from the target table.
24. Click OK to close the On Match Transformation calculator.

    The Transformations dialog box should look like the following image.

    
25. Click OK to close the Transformations dialog box.
26. On the Quick Access Toolbar, click the Save As button.

    The Save Procedure As dialog box opens.

27. Enter a name for the procedure, for example, update_dmsales_counts.
28. Click Save.
29. On the Flow tab, from the Run group, click Run and then click Submit.

    The message "(ICM18763) Request ibisamp/update_dmsales_counts complete" should appear in the console log, confirming that the flow was submitted successfully.

30. On the Flow tab, from the Reports group, click View Last Log to view the log. The log shows that 1 row was inserted, and 74 were updated, as shown in the following image.
    
31. To view the data that was loaded, return to the flow, right-click the target object, point to Operations, and click Sample Data. The report shows that product 1005 now has a Quantity of 445, as shown in the following image.