Creating an SQL Statement (Magic xpi 4.5)
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.
|
Note:
|
-
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.
|
-
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 will not be visible in the Data Mapper.
-
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.
|
Note:
|
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
|
