# Database Lookups

{% hint style="warning" %}
**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
    {% endhint %}

<figure><img src="/files/B6caO2FHf9CdGYoIys1d" alt="" width="563"><figcaption><p>Database lookup</p></figcaption></figure>

***

{% tabs %}
{% tab title="First Tab" %}

<figure><img src="/files/22tlX0Fee58hC8wtZlBC" alt=""><figcaption></figcaption></figure>
{% endtab %}

{% tab title="Second Tab" %}

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**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
  {% endhint %}

{% tabs %}
{% tab title="1. Data Grid" %}
{% hint style="info" %}
**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.
{% endhint %}

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

<div><figure><img src="/files/Nfb4zIz3eUc77ukvCx4u" alt=""><figcaption><p>Data grid - Meta</p></figcaption></figure> <figure><img src="/files/YuRRhTSKMEDiNZ4StKkw" alt=""><figcaption><p>Data grid - Data</p></figcaption></figure></div>
{% endtab %}

{% tab title="2. UDJE" %}
{% hint style="info" %}
**User defined Java expression**

The User Defined Java Expression step in Pentaho Data Integration allows you to write custom Java code that executes on each row of your data transformation. This step is useful when you need to perform complex calculations or data manipulations that aren't possible with PDI's standard steps.

You can access field values using the `get("fieldname")` method and create multiple expressions within a single step. Each expression produces a new output field in your data stream. The step handles type conversion automatically, making it flexible for various data operations.

Common uses include mathematical calculations, string manipulations, conditional logic, and date transformations. It's particularly valuable when you need Java-specific functionality or want to simplify your transformation by replacing multiple basic steps with a single, powerful Java expression.
{% endhint %}

1. Drag the User Defined Java expression step onto the canvas.
2. Open the User defined Java expression properties dialog box.
3. Ensure the following details are configured, as outlined below:

<figure><img src="/files/l1fKrBcvuIsUMCTWDIV4" alt="" width="563"><figcaption><p>UDJE - like statement</p></figcaption></figure>

{% hint style="info" %}
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
{% endhint %}
{% endtab %}

{% tab title="3. Database Lookup" %}
{% hint style="info" %}
**Database lookup**

The Database lookup step has 3 options
{% endhint %}

{% tabs %}
{% tab title="3.1 Simple" %}
{% hint style="info" %}
**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.
{% endhint %}

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

<figure><img src="/files/tC27igd9K9GVJJjrVEYY" alt=""><figcaption><p>Dtabase lookup - simple</p></figcaption></figure>

{% hint style="info" %}
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
{% endhint %}

{% hint style="info" %}
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.
{% endhint %}
{% endtab %}

{% tab title="3.2 Error Handling" %}
{% hint style="info" %}
In this workflow, error handling has been enabled, with a write to log step.
{% endhint %}

1. To see this in action, disable the Hops to Database Lookup (simple) and Database Lookup (do not pass).
2. The error message is written out in the Logging output.

<figure><img src="/files/qcEAQQ7vZM1er9pQGPWk" alt=""><figcaption><p>Logging Results</p></figcaption></figure>

3. Preview the Database Lookup (with error handling) step.

<figure><img src="/files/hTMi9Ac7s9OgH0kI3QYc" alt=""><figcaption><p>Database lookup - erorr handling</p></figcaption></figure>

{% hint style="info" %}
The rows for which the lookup fails, go directly to the stream that captures the error, in this case, the ‘Write to log’ step.
{% endhint %}
{% endtab %}

{% tab title="3.3 Do not pass" %}
{% hint style="info" %}
Taking some action when there are too many results The Database lookup step is meant to retrieve just one row of the table for each row in your dataset. If the search finds more than one row, the following two things may happen:

1. If you check the Fail on multiple results? option, the rows for which the lookup retrieves more than one row will cause the step to fail. In that case, in the Logging tab window, you will see an error similar to the following: ...

\- Database lookup (fail on multiple res.).0 – ERROR... Because of an error, this step can't continue:

\- Database lookup (fail on multiple res.).0 – ERROR: Only 1 row was expected as a result of a lookup, and at least 2 were found! Then you can decide whether you want to leave the transformation or capture the error.

2. If you don't check the Fail on multiple results? option, the step will return the first row it encounters. You can decide which one to return by specifying the order. You do that by typing an order clause in the Order by textbox. In the Sampledata database, there are three products that meet the conditions for the Corvette row. If, for Order by, you type PRODUCTSCALE DESC, PRODUCTNAME, then you will get 1958 Chevy Corvette Limited Edition, which is the first product after ordering the three found products by the specified criterion.

If, instead of taking some of those actions, you realize that you need all the resulting rows, you should take another approach—replace the Database lookup step with a Database join or a Dynamic SQL row step.

Compare this with the Database Join

As the database join is a full outer, all the records are returned from the database table, rather than just return a single lookup reference value.
{% endhint %}
{% endtab %}
{% endtabs %}
{% endtab %}
{% endtabs %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://academy.pentaho.com/pentaho-data-integration/data-integration/enrich-data/lookups/database-lookups.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.