Creating an SQL Statement (Magic xpi 4.14)
When you click 
in a Database Source or Destination's SQL Statement parameter, the SQL Statement window opens. You use basic rules for database scripts to enter an SQL statement in the SQL Statement field.
The Data Mapper Source supports only Select statements. The Data Mapper Destination supports only Update, Insert, and Delete statements. You can use environment variables to provide a dynamic translation of the statement.
You can edit the statement and add any legal SQL text, provided the full statement is a legal SQL statement. You cannot use a variable to hold the entire or parts of the WHERE statement, but you can use an environment variable instead.
The template used by the wizard to create the SQL statements is defined in the external XML file, SQLTemplates.xml, which is located in the <Magic xpi installation>\Studio\Templates\Database folder. This file contains generic templates and DBMS-specific templates and can be modified for specific DBMSs. (Since version: 4.5)
A variable can be used only as the value of a field.
When a flow variable is entered, it will be surrounded by angle brackets and question marks as follows: <?flow variable name?>. You can also add other legal SQL statement text, such as Example or Order By.
When you edit an SQL statement in the Data Mapper, you need to click Save to ensure that the changes take effect.
You can use multiple tables in the statement, with a JOIN between them. Or, if multiple tables were selected through the wizard, a JOIN will be automatically created according to the selections made in the wizard.
|
|
-
To include an SQL function, such as Count(*), in a statement you must use an alias.
-
You can use environment variables in the SQL statements to connect to DB2/400 databases.
-
Isolation levels, except NC, are not supported as part of the SQL query statement for DB2/400 databases.
|
-
Use ANSI symbols in the statement for support of all Database types.
-
Special characters, such as #,*,&,%,@, are not supported in field names by the Data Mapper.
|
|
If any special characters are used in a field name in the SQL statement, they may not be visible in the Data Mapper. This will not have any impact on runtime.
|
The encapsulation of the square brackets can alternatively be done in the SQLTemplates.xml file. By changing the SQLTemplates.xml, the SQL statements generated by the Data Mapper wizard will automatically have the square brackets and user will not be required to change the statement manually.
This behaviour is applicable for Magic xpi 4.13.1 and 4.13.2 versions.
Since version xpi 4.13.3, this manual change is not required as all the column names will be encapsulated in square brackets by default.
-
The Data Mapper does not support field names with spaces.
-
You must enclose all Alpha strings with single quotes (').
-
To connect to other databases, you must indicate the database owner. You do not need to indicate the owner if the database you want to connect to is the Magic xpi internal database that you are using for your project.
-
You cannot use wildcard characters for fields or functions. You must specify each one or create an alias for them.
-
The Data Mapper SQL Window preserves format changes that you make to the SQL statement.
-
When you build an SQL statement, the value is stored in an expression. The value in the expression can contain up to 32,000 characters. If the value is larger than 32,000 characters, a Missing End of Expression error will appear. However, you can combine several expressions to create a statement that is only limited by the database itself and not by Magic xpi.
|
|
If your database contains two tables with an identical column name, Magic xpi cannot differentiate between the two columns. In this case, Magic xpi ignores the second column. To avoid this situation, you should add an alias to the column names in the SELECT statement.
For example, instead of writing:
SELECT t1.customer,t2.customer from t1,t2
You should write:
SELECT t1.customer AS customer1,t2.customer AS customer2 from t1,t2
|
(Since version: 4.13.1)
The SQL Statement in the Data Mapper supports variables in the Query (SELECT Statement) and Procedure (EXEC Statement). The variable tag can be placed anywhere in the SQL Statement like the SELECT clause, WHERE clause, and so on. The Studio will take the default value of the variable and at runtime, the current value in the variable will be used.
Sample Use Cases:
· You can define the ORDER BY segment of the SQL Statement based on a variable. This will enable the Mapper step to have a flexible sorting based on the different sets of data at the time of execution.
· You can define the table name as a default value of a variable. With this, you can enable referring to different owners for similar table. In runtime, it gives you the flexibility to point to two different tables that have common columns.
|
|
If the variable does not have a default value, or is assigned an expression with a dynamic value, then the Mapper parsing will result in an 'Invalid SQL statement' error.
For example, if the "Date()" function is used in the default value, it will not work.
|
|
|
-
Any variable which is used to define the query structure (not applicable for WHERE clause) should not be changed since the same value will be used for building the flow and creating the generated code.
-
To use variables as a part of the SQL statement, the statement must begin with SELECT or EXEC.
|
Here is an example showing how the variable can be used in the SQL Statement.
SELECT [employee].first_name FROM <?F.Name1?> WHERE dbo.[employee].last_name = <?F.Name1?>
|
|
The table names in the SQL statements will be wrapped in the backtick (`) characters. This may result in backward compatibility issues if the SQL statement in the existing projects is updated using the database wizard. To maintain the backward compatibility, remove the backtick characters used for wrapping the table names in the SQLTemplates.xml file before re-opening the studio.
(Since version: 4.13.2)
|
