One important aspect of SAP BODS is that, it enables us to create template tables in the desired datastore with in the tool itself and enables us to convert the template table into permanent physical table in the same datastore. Template table plays a vital role in development and testing phases.
We often use template table as a staging table. Template table is used to store the data in an optimized way. There are number of use cases for template table however one such use case is when we want to lookup template table in BODS. By default, Data Services doesn’t directly allow us to use the template table inside a lookup function, but this can be achieved through a workaround which we will see in detail below. Let’s jump into it –
- Click FUNCTION button inside the query transform mapping area for the field which needs to be looked up.
- In the popup window, choose lookup functions and select lookup_ext in the right window.
- Click Next button and new popup window will appear.
- Select the datastore which contains the table which needs to be looked up. In our case we selected the DS_STAGING datastore which contains our template table (to be looked up).
- Once datastore chosen, all the physical tables in the datastore will be listed. Select any one table as of now. NOTE: Template tables will not be listed here.
- Choose any one field in the “column in lookup table (select box) under condition” and choose the field that you want to put in “expression” from the input schema or variables.
- Choose any one field from the selected physical table into the “column in lookup table(select box) under output”.
- Once you have completed the above steps, click Next. You will see code like the one below in the mapping area of the field.
lookup_ext([DS_ STAGING.DS_STAGING.PHYSICAL_TABLE_NAME,'PRE_LOAD_CACHE','MAX'], [FIELD3],[NULL],
[FIELD1,'=', QRY_STG.LENGTH ,
FIELD2,'=', QRY_STG.WIDTH']) SET ("run_as_separate_process"='no', "output_cols_info"='
- Replace PHYSICAL_TABLE_NAME with your template table name.
- Replace the FIELD3 with the field which you want to retrieve from template table.
- Replace FIELD1 and FIELD2 with the template table field on which you want to apply the condition. You can have one or any number of conditions based on your requirement. In our case, you will see two conditions in the above example.
Got questions? Click here to get in touch.