Salesforce

Creating an SQL Statement (Magic xpi 3.x)

« Go Back

Information

 
Created ByKnowledge Migration User
Approval Process StatusPublished
Objective
Description

Creating an SQL Statement (Magic xpi 3.x)

When you click the SQL button in the Database Schema Properties dialog box, you open the SQL Statement window. You use basic rules for database scripts to enter an SQL statement in the Enter SQL Statement field.

The Mapper Source supports only Select statements. The 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.

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 OK in the Data Mapper window 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:

You can use environment variables in the SQL statements to connect to DB2/400 databases.

Data Mapper SQL Statement Considerations

  • Use ANSI symbols in the statement for support of all Database types.

  • To include a function, such as BlobToBase64, in a statement you must use an alias.

  • 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.

  • You cannot use comments in Data Mapper Source or Destination SQL statements.

  • 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

Reference
Attachment 
Attachment