SQL Statement Lookup

The SQL Statement Lookup object in Astera Centerprise is used to look up certain values that are mapped to it from a source object. It uses an SQL statement to access a table that contains the lookup values and their corresponding output values. Once the lookup is performed, the SQL Statement Lookup object returns either a single or multiple output fields, depending on the nature of the lookup table. Similarly, the lookup can be performed based on one lookup field or multiple lookup fields. When the incoming values match the lookup values, the output field or fields for those particular records are returned by the SQL Statement Lookup object.

Use Case

In this use case, we will read data from the Customers table in the Northwind database using a Database Table Source object. This table contains customer information from a fictitious organization and will serve as the source table. Our purpose is to use an SQL Statement Lookup object to find some information about the orders placed by customers. This data is stored in a separate table called Orders, which will serve as the lookup table.

How to Work with SQL Statement Lookup in Centerprise

1. Drag-and-drop the Database Table Source object from Toolbox > Sources > Database Table Source onto the dataflow designer. Configure the object so that it reads data from the Customers table.

image-20200916105517539

To learn how you can configure a Database Table Source object, click here.

2. Now, drag-and-drop the SQL Statement Lookup Transformation object from Toolbox > Transformations > SQL Statement Lookup onto the dataflow designer, next to the source object.

image-20200916105708832

3. Right-click on the header of the SQL Statement Lookup object and select Properties from the context menu.

image-20200916105751549

This will open a new window.

image-20200916110909938

Here, we need to configure the properties of the SQL Statement Lookup object.

4. In the Database Connection window, enter details for the database you wish to connect to.

  • Use the Data Provider drop-down list to specify which database provider you wish to connect to. The required credentials will vary according to your chosen provider.
  • Alternatively, use the Recently Used drop-down list to connect to a recently connected database.

image-20200916110218649

  • Test Connection to ensure that you have successfully connected to the database. A separate window will appear, showing whether your test is successful. When the connection has been successfully established, close it by clicking OK, and then click Next.

image-20200916110954350

image-20200916111038962

5. The next window will present a blank space for you to write an SQL statement. Here, you can enter any valid SELECT statement or stored procedure to read any table from the database that was specified earlier. This table will serve as the lookup table.

image-20200916111118728

In this case, we will be reading data from the Orders table.

image-20200916111207856

Enter the SQL statement and click OK. This will take you back to the dataflow designer.

image-20200916111236652

As you can see, the SQL Statement Lookup object has been populated with all the fields present in the Orders table.

6. The next step is to choose an incoming field or multiple incoming fields from the source object, based on which the lookup action will be performed. This field needs to be mapped to the transformation object.

In this case, we can clearly see that CustomerID is a common element between the two tables. Hence, this field will be used to perform the lookup. It will be mapped from the Database Table Source object to the SQL Statement Lookup object as a new member.

image-20200916111318520

7. Right-click on the transformation object’s header and select Properties to open the Properties window. Keep clicking Next until you reach the Layout Builder window. Here, you can customize the layout by modifying the existing fields or creating new fields.

image-20200916111427105

Once you are done, click Next.

8. On the next window, you can define one or more lookup conditions. These conditions will determine what values are returned when the lookup is complete.

image-20200916112451786

You will have to make appropriate selections from three drop-down lists:

Database Element Name: This list contains all the elements present in the SQL Lookup object. Select the element that you wish to use as a lookup field. In this case, it is CustomerID.

image-20200916112534105

Operator: This list contains a set of operators that are used to define the condition. In this case, we will be using the ‘equals to’ operator because the lookup value is supposed to match the incoming value.

image-20200916112610853

Input Element: This list contains the elements that have been mapped to the lookup object. In this case, the only input element available is CustomerID from the Customers table.

image-20200916112740880

Once you are done defining the condition, click Next.

9. The next window will allow you to choose a Lookup Caching Type. The following options are available:

No Caching: No data will be stored in cache. This option is selected by default.

image-20200916112927681

Static: The lookup values are stored in a cache. Once the cache is created, the lookup object will always query the cache instead of the lookup table. When you select this option, the following sub-options are enabled:

  • Fill Cache With All Lookup Values at Start: Fills the cache with all of the lookup values at the start and continues to use this cache for every lookup.

  • Cache After First Use: Uses the database table for the first lookup and fills the cache right after it is done. This cache is then used for every subsequent lookup. Checking this option enables another sub-option:

    o Cache Commit Count: Defines the number of records collected per cache chunk before they are committed to the cache.

image-20200916113538109

Persistent: Saves the lookup values in a cache file that can be reused for future lookups. When you choose this option, the following sub-options are enabled:

  • Rebuild Persistent Cache on Next Run: Checking this option will allow the contents of the cache file to be modified after every run.
  • Cache File Name: Here, you can enter a name for your cache file.

image-20200916114230796

In this case, we will select the No Caching option. Once you are done, click Next.

10. On the next window, you will see multiple lookup options.

image-20200916114320131

The page provides a set of options for different scenarios that could be faced during a lookup.

If Multiple Values Are Found

Multiple Matches Found Option: This option provides the flexibility to choose the output value if more than one matches are found for a single value in the lookup table. You can select one out of three options that appear in the drop-down list:

  • Return First: Returns the first matched value.
  • Return Last: Returns the last value among all matched values.
  • Return All: Returns all the matched values.

image-20200916114438447

If Value Is Not Found In the Lookup List

If no lookup values are found for a source value, you can choose from the following options to be appended with the output:

  • No Message: The output value will be the same as the input value and no message will appear with it.
  • Add Error: An error message will appear with the output.
  • Add Warning: A warning message will appear with the output.

image-20200916114615235

If Value Is Not Found in the Lookup List, Assign Value

If no lookup value is found for a source value, you can assign an output value of your choice.

  • Assign Source Value: Returns the source value in the output.
  • Assign Null: Returns null in the output.
  • This Value: Allows you to enter any value that will be returned in the output.

image-20200916114728047

In this case, we want to look up the details for all of the orders placed by every customer. Hence, we will select Return All from the drop-down list in the Multiple Matches Found Option. This will automatically disable the rest of the options available on the screen.

image-20200916114824550

Once you are done choosing the option, click Next.

11. On the next window, you can define certain parameters for the SQL Statement Lookup object.

These parameters facilitate an easier deployment of flows by excluding hardcoded values and providing a more convenient method of configuration. If left blank, they will assume the default values that were initially assigned to them.

image-20200916114908685

In this case, we will be leaving them blank. Click Next.

12. On the last window, you will be provided with a text box to add Comments. The General Options in this window have been disabled.

image-20200916114951264

You are now done configuring the SQL Statement Lookup object. Click OK.

13. Right-click on the SQL Lookup object’s header and select Preview Output.

image-20200916115038664

You will able to see the following results:

image-20200916115312317

Scroll down the Data Preview window to see the rest of the results.

The SQL Statement Lookup object has successfully returned the details for the orders placed by every customer in the Customers table (Source table) by comparing the CustomerID to its counterpart in the Orders table (lookup table).

This concludes using the SQL Statement Lookup Transformation object in Astera Centerprise.