A Sql data group uses a Sql query to obtain data from a data source.

Select the data source and enter the Sql query.

While entering the query, click on the placeholders available to the right of the page, or right click in the query area and choose from the options offered. The placeholders are inserted into the query and will be substituted when the data group is executed with the appropriate values. Placeholders are each of the following:

• External inputs to the DSD
• Inputs added to this data group or its parent and antecedents
• Security details (User Id, AD Login or Caller Id)
• A function entered using the Function Builder

Also to the top right of the page is a list of tables that were identified in the source set up as ones that you may wish to use for this source. Click on a table name to insert the name into the query. Click the icon to the right of the table name to open a list of fields for the table. Click a field name to insert in the query. Check the option at the top if you want to include the table name as a prefix (e.g. thistable.thatfield). Note that the tables and fields offered here are those suggested for the source; you can use any tables in the query itself as this is only an aid.

There are three useful tools to the top right of the entry box:

• The first switches between the full editor (shown above) and a simple code editor. The code editor is a straightforward entry box and is useful to copy/paste queries into the data group.
• In the example shown, the query is colour coded to highlight sections and is also tided up and indented. Use the centre icon to tidy and indent at any time.
• The third option lets you make the entry area full width to provide more space for editing.

At the top of the page is a “Check” button. Click this to establish whether your source and query are valid. The test is done by substituting dummy and sample values for your placeholders and executing the query. Any actions taken by the query are rolled back on completion of the test, to prevent any updates being executed.