This guide will help you get an idea on how to perform your custom SQL queries.

Configuration

You can set your custom SQL queries in the bottom field of the Record Source window. This can be done during configuring database connectors. Commonly, it's the second property window that appears. By default, SELECT * is performed.

The highlighted area can be edited, and you can replace defaults with your own SQL queries.

Sample SQL queries

Below are the examples of common SQL queries for MS SQL database.

To get top N records in the table:

select top N * from [TABLENAME]

To get just records in which a FIELD starts with a specific value:

select * from [TABLENAME] where [FIELDNAME] like 'value%'

To get just records in which a FIELD finishes with a specific value:

select * from [TABLENAME] where [FIELDNAME] like '%value'

To get just records in which a FIELD contains a specific value:

select * from [TABLENAME] where [FIELDNAME] like '%value%'

To get just records in which a FIELD equals to a specific value:

Case sensitive:

select * from [TABLENAME] where [FIELDNAME] ='value'

Case sensitivity depends on server settings:

select * from [TABLENAME] where [FIELDNAME]  like 'value'

To get records in which a FIELD is empty:

select * from [TABLENAME] where ([FIELDNAME]='' OR [FIELDNANE] is null)

To get records in which a FIELD is NOT empty:

select * from [TABLENAME] where ([FIELDNAME]!='' OR [FIELDNANE] is not null)

If you want to use a number of queries, you can join them by AND. Example:

select * from [TABLENAME] where [FIELDNAME] ='value' AND ([FIELDNAME]='' OR [FIELDNANE] is null)