Creating a map of an ODA data source query result set
When you create a map of an ODA data source query result set, the IO Design perspective creates a subfolder in the folder that contains the data connection definition (.dcd) file for the ODA data source. The subfolder contains a .epr file as well as the .sma file. The .epr file specifies:
The ODA data source query
The query’s input and input/output parameter values and data types
The query’s output parameters
The result set map represents the first result set returned by the ODA data source query. The query’s input and input/output parameters are associated with the result set map. In other words, when you build an information object from the result set map, these parameters are source parameters. The parameter values provided by a result set map user must yield the same result set metadata as the parameter values you provide when you create the map. In other words, the result set map must have the same columns and data types at run time as it does at design time.
The names of the subfolder and the .epr file are derived from the name you provide for the query. For example, you are working with an ODA data source called MyODADataSource. As shown in Figure 2‑45, if you create a map of a query result set called MyResultSet, the IO Design perspective places the files MyResultSet.sma and _MyODADataSourceQuery.epr in a subfolder of MyODADataSource called MyODADataSourceQuery.
Figure 2‑45 Location of the .epr file and map file for an ODA data source
How to create a map of an ODA data source query result set
1 In Data source connection properties, choose Map tables.
2 In New Maps—Maps:
In Name, type a name for the ODA data source query.
In Type, select an ODA data source type from the drop-down list, as shown in Figure 2‑46.
Choose Next. The ODA data source query builder appears. For example, for a flat file data source, Select Table—Select Columns appears.
Figure 2‑46 Selecting an ODA data source type
3 In the ODA data source query builder, build a query. The procedure for building a query varies by data source type. For a flat file data source, Select Table appears, enabling you to select columns as shown in Figure 2‑47. Choose the Right Arrow. Then, choose Finish.
Figure 2‑47 Selecting columns from a flat file data source
The .epr and .sma file names appear in Navigator.
4 In Output Columns:
To rename a column, type the new name in Name.
Decide on column names before you build an information object from this map. Changing a column name after you build an information object results in a compiler error in the information object.
To create a filter on a column, set the column’s Filter property to Predefined and choose Prompt editor to specify the filter’s prompt properties. Figure 2‑48 shows the Output Columns pane for a map using an ODA data source.
Figure 2‑48 Viewing the output columns for a map using an ODA data source
To define other column properties, select the column and define the properties in Properties.
How to modify a .epr file for an ODA data source query
1 In Navigator, double-click the .epr file you need to modify.
2 In General:
To modify the query in Query text, choose Custom Data Source Properties.
In Description, type a description for the query, as shown in Figure 2‑49.
Figure 2‑49 Choosing Custom Data Source Properties to modify the query
3 Choose Parameters.
The query’s parameters appear.
4 In Parameters:
To rename a parameter, type the new name in Parameter.
The IO Design perspective assigns a default name to a parameter based on its position in the query, for example, param_1.
If necessary, choose the correct data type from the Data type drop-down list.
In Default value, type the parameter’s default value.
Do not create an expression. You cannot type a default value for an output parameter.
To specify the parameter’s prompt properties, choose Prompt editor. Figure 2‑50 shows the Parameters pane for a map using an ODA data source.
Figure 2‑50 Specifying prompt properties for an ODA data source query’s parameters
To define other parameter properties, select the parameter and define the properties in Properties.
5 Choose Data Set Properties.
The data set properties are displayed, if the data set has any.
6 In Value, type values for the data set properties.
The ODA data source query
