Creating SELECT Statements

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > Databases and MapForce > 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.mdb file available in the <Documents>\Altova\MapForce2019\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).

selcomp1

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.mdb file available in the <Documents>\Altova\MapForce2019\MapForceExamples\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

add_select_01

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.

add_select_02

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.

add_select_03

 

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.

© 2019 Altova GmbH