Altova MapForce 2023 Professional Edition

Navigation: Structural Components > Databases > SQL SELECT Statements as Virtual Tables

Creating SELECT Statements

You can create SELECT statements on any mapping which contains a database component. If your mapping does not contain a database yet, add a database first (see Connecting to a Database ). For the scope of this example, select the menu command Insert | Insert Database and follow the wizard steps to connect to the Altova-Products.sqlite file available in the <Documents>\Altova\MapForce2023\MapForceExamples\Tutorial\ folder.

To create a SELECT statement:

1.Right-click the title of the database component and select Add/Remove/Edit Database Objects. As an alternative, select the database component, and then select the menu command Component | Add/Remove/Edit Database Objects.

2.Do one of the following:

oTo generate the SELECT statement from an existing table, right-click any table and select Generate and add an SQL statement from the context menu. You will be able to edit the generated statement afterwards.

oTo write a custom SELECT statement, click the Add/Edit SELECT Statement button.

3.Edit or create the statement as required. For example, the SELECT statement below is valid for the Altova-Products.sqlite file available in the Tutorial folder. The Price field is the product of the two fields, Quantity and UnitPrice, and is declared as a correlation name ( AS Price ).

SELECT *, (Quantity*UnitPrice) AS Price
From Orders
INNER JOIN Products
ON Orders.ProductID = Products.ProductID
Where Orders.Quantity > 2

4.Click Add SELECT Statement. Notice that the SELECT statement is now visible as a database object, similar to how tables, views, and procedures are visible.

5.Click OK. The SELECT statement is also displayed on the database component, and you can map data from any of the fields returned by the SELECT query.

Important notes:

•All calculated expressions in the SELECT statement must have a unique correlation name (like "AS Price" in this example) to be available as a mappable item.

•If you connect to an Oracle or IBM DB2 database using JDBC, the SELECT statement must have no final semicolon.

To remove a previously added SELECT statement:

1.Right-click the title of 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.