Custom SELECT Statements
MapForce allows you to create custom SQL SELECT statements with or without parameters. These statements are represented as table-like structures, from which you can map data to other components. For example, you can create a custom statement to join tables, filter your database data, and define parameters that can accept values from another component in the mapping.
SQL SELECT statements without parameters are supported in C++, C#, Java, and Built-In languages. SQL SELECT statements with input parameters are compatible only with the Built-In transformation language.
Create/Edit/Remove a SELECT statement
To add a SELECT statement to a database component, follow the instructions below:
1.Right-click the title of the database component and select Add/Remove/Edit Database Objects from the context menu. Alternatively, select the database component and select the menu command Component | Add/Remove/Edit Database Objects.
2.In the Add/Remove/Edit Database Objects dialog, do one of the following:
oTo enter a custom SELECT statement, click the Add/Edit SELECT Statement button.
oTo generate the SELECT statement for a particular table, right-click the relevant table and select Generate and add an SQL statement from the context menu. You will be able to edit the generated statement afterwards.
To edit an existing SELECT statement, do one of the following:
•Right-click the SELECT statement in the component and select Edit SELECT Statement.
•Right-click the database component and select Add/Remove/Edit Database Objects from the context menu. Then double-click the relevant SELECT statement in the Add/Remove/Edit Database Objects dialog.
•In the Add/Remove/Edit Database Objects dialog, select the relevant SELECT statement and click Add/Edit SELECT Statement.
•In the Add/Remove/Edit Database Objects dialog, right-click the relevant SELECT statement and select Edit a SELECT Statement.
To remove a SELECT statement, take the steps below:
1.Right-click the database component and select Add/Remove/Edit Database Objects.
2.Right-click the SELECT statement you want to delete and select Remove SELECT Statement from the context menu.
Important notes
Note the following points:
•All calculated expressions in the SELECT statement must have a unique correlation name (e.g., SELECT *, (Quantity*UnitPrice) AS Price) to become available as mappable items.
•If you connect to an Oracle or IBM DB2 database using JDBC, the SELECT statement must not have the final semicolon.
SQL SELECT statements without parameters
The example below shows how to work with custom SELECT statements without parameters. In the mapping shown below, we map database data to a text file. The BookCatalog.sqlite database has a parent table called Authors and a child table called Books. However, only the SELECT statement with a tree structure is displayed in the component. The structure of the tree depends on the SQL query you define in the Enter a SQL SELECT Statement dialog. Since nothing will be mapped from the Authors and Books tables, these tables are absent from the component.
SELECT statement
For the database component, we have added the following SQL statement (see instructions in Create/Edit/Remove a SELECT Statement):
The SQL statement selects all the tables from the Authors table and filters database data to include only authors from the UK. As soon as we add this statement to the Enter a SQL SELECT Statement dialog, the statement becomes available in the Add/Remove/Edit Database Objects dialog (screenshot below). The statement is also visible in the database component (see mapping above). The number of visible lines of the SELECT statement can be configured in the Options dialog box (the Limit annotation display option).
Output
The output displays a list of comma-separated values that include authors only from the UK (code listing below).
Author,Country,Website
Bram Stoker,UK,www.bramstoker.org
Charles Dickens,UK,www.charlesdickensinfo.com
Emily Brontë,UK,n/a
James Herbert,UK,www.james-herbert.co.uk
Neil Gaiman,UK,www.neilgaiman.com
Terry Pratchett,UK,www.terrypratchettbooks.com
Agatha Christie,UK,www.agathachristie.com
Roald Dahl,UK,www.roalddahlfans.com
David Walliams,UK,www.worldofdavidwalliams.com
Kenneth Grahame,UK,n/a
Philip Pullman,UK,www.philip-pullman.com
J.K. Rowling,UK,www.jkrowling.com
Ann Cleeves,UK,www.anncleeves.com
SQL SELECT statements with parameters
Our second example illustrates a mapping in which the database component has a custom SELECT statement with a parameter (screenshot below).
SELECT statement
For the BookCatalog component, we have entered the following SQL statement:
The statement uses the Country parameter. This parameter will accept values from the constant (under the BookCatalog component). To be able to map data from the SELECT statement with the parameter, click the 
button next to the SELECT_Statement node in the database component (mapping above) and select Insert Call with Parameters from the context menu. This inserts a Call component with parameters (central component in mapping above). The Call component has two parts: The left part accepts an input parameter (in our case, Country), and the right part replicates the SELECT statement with the tree structure from the database component. The filtered data is then mapped to the Authors text file.
Output
The output now displays authors only from the USA (code listing below).
Author,Country,Website
Stephen King,US,www.stephenking.com
Frank Herbert,US,n/a
Isaac Asimov,US,www.asimovonline.com
Blake Crouch,US,www.blakecrouch.com
Ray Bradbury,US,www.raybradbury.com
Joe Hill,US,www.joehillfiction.com
Josh Malerman,US,www.joshmalerman.com
George R. R. Martin,US,www.georgerrmartin.com
A. J. Finn,US,n/a
Dan Brown,US,www.danbrown.com
Dean Koontz,US,www.deankoontz.com
Example files
For more information about mappings that use custom SQL SELECT statements as input, see the following examples in the MapForceExamples folder:
•DB_EmployeeListByTitle.mfd
•DB_MostExpensiveArticle.mfd
•DB_ManagerList_AllOffices.mfd
•DB_ManagerList_SelectedDepartment.mfd
•DB_ManagerList_SelectedOffice.mfd