You can map a query written in the database’s native SQL. When writing the query, observe the following rules:
Write the query using only the database’s native SQL; do not use Actuate SQL functions or syntax.
Do not include an ORDER BY clause in the query. Including an ORDER BY clause adversely affects the performance of information objects built from the map.
Use unnamed parameters. An unnamed parameter is represented by a question mark (?). Do not use named parameters, for example, BeginDate.
If the query uses a parameter, it may be necessary to cast the parameter to the appropriate data type. For example, if the query queries an Oracle database, you must use the CAST or RPAD functions to ensure that the data type and length for a string parameter match the data type and length for the corresponding column if the column is of type CHAR or NCHAR. For example, in the following queries the category column is of type CHAR(12):
SELECT orderID FROM items WHERE category = CAST ( ? AS CHAR(12) )
SELECT orderID FROM items WHERE category = RPAD ( ?, 12 )
How to create a map of a native SQL query
1 In Navigator, select the appropriate project.
2 Choose File➛New➛Map(s).
3 In New Maps—Data Source:
Select the appropriate data source.
Select Create a map by entering the native query string, as shown in Figure 2‑31.
Choose Next.
Figure 2‑31 Creating a map by entering a native query string
4 In New Maps—Map, in Map name, type a map name, as shown in Figure 2‑32.
Figure 2‑32 Specifying a map name
Choose Finish.
The map file name appears in Navigator.
5 In the textual query editor:
In the upper pane, type or paste the native SQL query.
In the lower pane, choose Describe Query.
The query’s output columns appear.
To rename a column, type the new name in Output column.
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.
If necessary, choose the correct data type from the Data type drop-down list.
The Actuate SQL data type must be compatible with the native SQL data type.
To create a filter on a column, set the column’s Filter property to Predefined and specify the filter’s prompt properties. Figure 2‑33 shows Columns and the location of the Prompt editor button.
To define other column properties, select the column and define the properties in Properties.
Figure 2‑33 A native SQL query and corresponding Columns page
Choose Parameters.
The query’s parameters appear.
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.
The Actuate SQL data type must be compatible with the native SQL data type.
In Default value, type the parameter’s default value.
Do not create an expression.
To specify the parameter’s prompt properties, choose Prompt editor. Figure 2‑34 shows Parameters and the location of the Prompt editor button.
To define other parameter properties, select the parameter and define the properties in Properties.
Figure 2‑34 The parameters page for a Native SQL query
Write the query using only the database’s native SQL; do not use Actuate SQL functions or syntax.
