Native Query Block
To begin designing a Map, your first step is to select a Query Block or a Native Query Block for the Connection linked to your source data. Drag the Block from the Connections panel to the workspace.
For Connectors that support native languages, such as Microsoft SQL Server or Microsoft Dynamics 365, use the Native Query Block to write your own free form query that can join tables, restrict the source field list, or do a WHERE clause to filter data in a more flexible way. Data can be normalized without foreign keys, or de-normalized by ignoring existing relationships.
A number of caveats apply to the use of the Native Query Block. See Creating Native Queries For Microsoft SQL Server.
Note: The Native Query Block only displays if the Connection supports using a query language within TIBCO Scribe® Online.
Block Properties — General Tab
After you have moved the Native Query Block to the workspace, you can write a query in the language supported by the Connection.
Edit Native Query Properties
- Select the Native Query Block to display settings in the Properties panel on the right.
- Select the General link to open the Properties dialog to the General tab.
- Optionally change the label and add a description as described in Block Properties General Tab.
Specify And Test A Native Query
- In the Native Query Text pane, enter your query.
The query can be as simple or complex as you need it to be; however, it should return a single result set. For example:
SELECT * FROM Accounts a
JOIN Customers c ON a.Id = c.AccountId
- Select Test to validate your query.
When testing a Native Query in a Map, if the source datastore does not return any data, TIBCO Scribe® Online cannot build the schema for the underlying metadata and the Map cannot be saved. To allow TIBCO Scribe® Online to build the schema, do the following:
- Create a single temporary record in the source datastore that matches the Native Query.
- Test the Native Query and ensure that it is successful.
- Save the Map.
- Remove the temporary record from the source datastore.
Block Properties — Preview Tab
Before building your Map, you can preview the first 25 records of your source data. When you open the Preview tab, TIBCO Scribe® Online generates, and then displays the first record in your source data to be processed when you run this Map. See Block Properties Preview Tab.
- Displaying the first record may take a few moments.
- Records on the Preview tab are filtered using the criteria on the Filters tab, but are not filtered by the date entered on the Net Change tab.
Block Properties — Errors And Warnings Tab
If there are any errors or warnings for this Block, the Errors and Warnings tab displays the number of errors or warnings in the tab name. See Block And Map Errors And Warnings.
Once you have corrected the errors, select the Validate button to update the Errors and Warnings tab.