Database Lookups

Workshop - Database Lookup

The Database lookup step allows you to look for values in a database table.

In this guided demonstration, you will:

Configure the following steps:
- Database Lookup

Create a new Transformation

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

By clicking File > New > Transformation
By using the CTRL-N hot key

Data grid

The Data Grid step allows you to enter a static list of rows in a grid. This is usually done for testing, reference or demo purposes.

Drag the Data Grid step onto the canvas.
Open the Data grid properties dialog box.
Ensure the following details are configured, as outlined below:

Database lookup

The Database lookup step has 3 options

Simple Lookup

The Database lookup step allows you to look up values in a database table. Lookup values are added as new fields onto the stream.

Drag the Data Grid step onto the canvas.
Open the Data grid properties dialog box.
Ensure the following details are configured, as outlined below:

The ‘key’ fields are where you specify the conditions. Each row in the grid represents a comparison between a column in the table, and a field in your stream, by using one of the provided comparators.

In this example:

WHERE PRODUCTNAME LIKE '%Aston Martin%' AND BUYPRICE < 90 WHERE PRODUCTNAME LIKE '%'Ford Falcon%' AND BUYPRICE < 70 WHERE PRODUCTNAME LIKE '%Corvette'%' AND BUYPRICE < 70

The Database lookup step allow us to retrieve any number of columns based on the search criteria. Each database column you enter in the lower grid will become a new field in your dataset.

You can rename them (this is particularly useful if you already have a field with the same name) and supply a default value if no record is found in the search. In the workflow, you added three fields: PRODUCTNAME, PRODUCTSCALE, and BUYPRICE.

For values where there’s no match for PRODUCTNAME, ‘not available’ is returned. In the Preview, notice there are no PRODUCTNAMES that match %Ford Falcon% where the max price is less than the max price of 70.

PreviousLookups NextScripting

Last updated 1 month ago

Was this helpful?

Good night