SQL queries give you ability to:
- View only needed table fields / columns.
- Use expressions as fields.
- Combine data from several tables using joins, unions etc.
- View only records that meet user specified criteria.
- Use parameters and macros. SQL query parameters and macros allow reusing the same SQL text to produce different criteria.
The SQL editor is a part of SQL Window. To open such a window, click New SQL Window button or right-click the needed table and choose Query Data... from the context menu. You can open multiple SQL windows and switch between them as tabs. The editor supports SQL syntax highlighting, some token hints and basic code completion.
There are a lot of functions helping you to write and execute the queries. They are available in Query menu and on the tool bar above the editor.
There is an ability to load SQL text from file and save it to file.
To open / execute an SQL query, type or load the query text in the SQL editor and then click Execute Query button or select the corresponding item from Query menu. If your query is correct, its result will be shown in the grid below.
Please note if the SQL editor contains selected text, only selected query will be executed.
You can work with the query result in the same way as you do with tables, including exporting or copying data etc.
Basic SQL Query
If you are new to SQL, start from this:
SELECT * FROM <table_name> WHERE <column_name1> = ... AND <column_name2> = ...
Here, <table_name> is the name of the table to export data from, and <column_name1> and <column_name2> are the names of columns in the table.
Queries with Parameters and Macros
Parameters and macros are substitution variables in queries. They improve flexibility of query execution by allowing to change the query dynamically (without changing of SQL text) before the execution. These variables get their values immediately before query execution either by user input or command line parameters.
Macros are always text variables; their values just replaces macros text in the SQL text before query execution.
Unlike macros, parameters have type; their values and types are passed to database server or database engine and not inserted in SQL text.
Macros and parameters inside comments or string literals are ignored by the application SQL engine, but is not recommended to use parameters there. Although macros are often more convenient to use, using parameters is preferable as they help in performance optimization and protecting against SQL injection.
If parameter or macro is not predefined one and its value was not defined in command line, the user will be prompted to enter it in a separate window.
Macros begin with << and end with >>. Macros are defined mainly for substitution when executing queries via command line. In the following SQL example, there is CUST_TYPE_LIST macro:
SELECT * FROM payments WHERE customer_type in (<<CUST_TYPE_LIST>>)
The value for this macro can be, for example, the following string:
3, 8, 12, 5
Note that such result cannot be produced using parameters.
Parameters begin with colon. Parameters, which contain spaces, must be enclosed in single quotes. Parameterized queries are convenient for using the same SQL statement for many data values. In the following SQL example, there is a DATE_OF_PAYMENT parameter:
SELECT * FROM payments WHERE pay_date = :DATE_OF_PAYMENT
When the application executes such a query, it suggests user to enter parameter value and data type, and then continues execution.
There are several predefined parameters, which are calculated automatically by the application and does not require user or command line input:
|<<SYSTEM_DATE>>||Replaced by current date (with Date type)|
|<<SYSTEM_DATETIME>>||Replaced by current date and time (with DateTime type)|
|<<SYSTEM_TIME>>||Replaced by current time (with DateTime type)|
|<<SYSTEM_YEAR>>||Replaced by current year (with Integer type)|
|<<SYSTEM_MONTH>>||Replaced by current month (with Integer type)|
|<<SYSTEM_DAY>>||Replaced by current day (with Integer Type)|
If the predefined parameters do not begin with colon, they are interpreted as predefined macros. In case of integer data, the result will be the same, but in case of other data types there may be problems, therefore please don't confuse parameters and macros.