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.

From version 4.7, the optional fAI module includes an option to build SQL queries using AI.

An extra tab will appear call “AI Query”.

In the dialog that opens, enter (or click the microphone button and speak) your requirement for the query and it will be generated. You can then use it or reject it and try again.

The syntax of the query will have been checked but check the query carefully as AI generated queries may be incorrect