# Lookup transformation in BODS: lookup_ext explained with examples

> Source: https://netray.co/erp-help/lookup-transformation-in-bods-lookup-ext-examples
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

In SAP Data Services (BODS) there is no separate Lookup transformation box. Lookups are done with the lookup_ext function (or lookup and lookup_seq) inside a Query transformation, typically in a mapping expression or the function wizard. You give it a lookup table, the result column, a default and the compare condition.

## Kind

how-to

## System

sap

## Erp

SAP BusinessObjects Data Services

## Module

Data Services / Data Migration

## Query

lookup transformation in bods

## Alternate Queries

- bods lookup_ext example
- how to load data into bods
- lookup function in sap data services

## Applies To

SAP BusinessObjects Data Services 4.x.

## Steps

### Adding a lookup to a data flow

**Items:**

- Open the data flow in Designer and add a Query transformation after your source.
- In the target schema, select the column to populate and open its Mapping tab.
- Click the function wizard, choose Lookup Functions, then lookup_ext.
- Select the lookup table, the column to return, the default value if not found, and the cache option.
- Define the condition, comparing the lookup table key to the input column.
- Validate the data flow, run it in a test job, and check unmatched rows go to a reject path or take the default.

## Sections

### lookup_ext in one view

**Body:**

The call has this shape: lookup_ext([table, cache_spec, return_policy], [result columns], [default values], [compare column, operator, expression]). The cache spec decides how the table is read, for example NO_CACHE, PRE_LOAD_CACHE or DEMAND_LOAD_CACHE. The return policy such as MAX or MIN decides which row wins when several match.

Use PRE_LOAD_CACHE for reasonably small reference tables used for many rows. Use NO_CACHE or demand load for very large tables or when the lookup data changes during the job.

**Code:**

- lookup_ext([DS_REF.DBO.MATERIAL_MAP, 'PRE_LOAD_CACHE', 'MAX'],
  [NEW_MATNR], ['UNMAPPED'],
  [OLD_MATNR, '=', Query.MATNR])

### Alternatives and when to use them

**Body:**

lookup is simpler and returns one value, lookup_seq picks by sequence column. For many columns or complex joins, a Query join in the data flow is often clearer and can be pushed down to the database. Use lookups for reference translation, joins for combining datasets.

### Loading data into the target

**Body:**

To load data, define source and target datastores, build the data flow with Query transformations (including lookups and validation), and execute through a job. For SAP targets, load through the supported interfaces such as IDocs, BAPIs or the SAP-supplied load methods rather than inserting into tables.

## Pitfalls

- Caching a huge lookup table in memory.
- No sensible default, so unmatched rows silently load blanks.
- Using a lookup where a join would push down and run faster.
- Ignoring multiple matches and relying on arbitrary return order.
- Loading directly into SAP tables.

## Ai Angle

In migration projects an assistant can profile source and mapping tables, list unmatched keys and draft the lookup rules for review. Netray runs this privately on-prem, so migration data stays within your network.

## Faq

### Is there a Lookup transformation in BODS?

**Answer:** Not as a separate transform. The functionality is the lookup_ext function used in Query transformation mappings.

### What is the difference between lookup and lookup_ext?

**Answer:** lookup_ext is more flexible: multiple return columns, multiple conditions, cache control and return policy. lookup is a simpler single-value version.

### How do I load data into BODS?

**Answer:** Create datastores for source and target, build a data flow with a Query transform, put it in a job and execute it from Designer or the Management Console.

### Why is my lookup slow?

**Answer:** Likely no cache or a very large table. Preload where the table fits in memory, or switch to a join pushed down to the database.

## Search terms

lookup transformation in bods, lookup_ext in bods, lookup_ext example, sap data services lookup, how to load data into bods, bods query transform, bods data migration

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json