Altova MapForce 2024 Professional Edition

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.

MF_SELECT01

SELECT statement

For the database component, we have added the following SQL statement (see instructions in Create/Edit/Remove a SELECT Statement):

MF_SELECT02

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

MF_SELECT03

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

MF_SELECT04

SELECT statement

For the BookCatalog component, we have entered the following SQL statement:

MF_SELECT05

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 selectSprocButton 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

 

© 2017-2023 Altova GmbH