SQL stands for Structured Query Language. It is the basic programming language used by the Firebird database that stores the data in ACCOUNTS, and by almost all major database systems today, such as Microsoft or Sybase SQL Server or Oracle. Some very technically experienced users of the program may be familiar with SQL, and may wish to explore the database using it. That is what the Database ⇒ SQL Select feature allows you to do. We may also give you some instructions to use this feature either to help diagnose problems you are having, or to create simple ad-hoc reports that are not included in the Reports menu.
When you select Database ⇒ SQL Select, the following window comes up on which you can enter a SQL Select statement:
You can save a SQL statement that you enter here to a file with the Save to SQL File button, or retrieve an existing SQL statement from a file with the Open SQL File button, to re-use it. However, more commonly you would instead use the Memorize Report button - more details on that are below.
On this window you can also select a Report Style – either Normal or Grid.
The Normal style looks a lot like the built-in reports. The advantages to using the default Normal Report Style are that it has a standard header section, like the built-in reports, and it starts out in Print Preview mode, so that you can have a better idea of exactly what the printed report will look like.
The Grid style looks more like a spreadsheet, with boxes around each row and column, but it has no header section (except for the column headings). It has some interesting features. You can change the displayed width of columns by clicking on the border between two columns in the row of headings, and dragging it left or right. You can change the order of columns by clicking a heading, and dragging it left or right. When you print the report, the grid lines are not printed.
You can also select between the Portrait (normal) and Landscape (sideways) Orientations for the report.
In addition, you can specify the Report Title on this window, though it will only be shown on the report if you use the Normal Report Style, not the Grid style.
Once you have your desired statement entered, click the Run button. If there are errors in the SQL, a message box will come up explaining the error. If the SQL is valid, the results will be displayed as a very simply formatted report on the Reports window, as if it was a normal report. From there, you can do anything you could normally do on the Reports window, including Sorting or Filtering the data, Printing the result, and using Save As to save the results to a text file or other file format.
Note: any columns that hold dollar figures (or other currency figures, if you are not in a country that uses dollars) will automatically have a total created for them at the bottom of the report.
Memorizing your Report
You can memorize a report in the SQL Select window with the Memorize Report button. That will memorize the SQL, Report Style, Orientation and Report Title, and allow you to replay it later with the Reports ⇒ Memorized Reports menu option. Or, you can wait until you are on the report-viewing window after clicking Run in this window, and use the Memorize button there, possibly after using the Sort or Filter buttons, since any sort or filter will also be memorized with the report. See Memorized Reports for full details.
Arguments for Transaction Date Selection
There are five arguments that can be used in different combinations for selecting only a single transaction date, a range of transaction dates, or the current fiscal year, to include in the data selected by the report. You would include these in a WHERE clause in the report (which restricts the data to data satisfying a given condition). The following are the options:
•:TRANSACTION_DATE - if this is present, when you Run the report, a window will come up prompting for a single transaction date to include in the report. This argument would generally be used in a SQL phrase such as:
WHERE transaction.trandate = :TRANSACTION_DATE
•:TRANSACTION_FROM_DATE and :TRANSACTION_TO_DATE - if these are both present, when you Run the report, a window will come up prompting for starting and ending transaction dates to include in the report. Example SQL:
WHERE transaction.trandate between :TRANSACTION_FROM_DATE and :TRANSACTION_TO_DATE
•:FISCAL_START_DATE and :FISCAL_END_DATE - if these are both present, and you have set up a fiscal year different from the calendar year in the Maintenance ⇒Organization Info window, then when you Run the report, it will be run for the current fiscal year, with no prompting. Example SQL:
WHERE transaction.trandate between :FISCAL_START_DATE and :FISCAL_END_DATE
Details and Examples
The ACCOUNTS database is composed of tables, one table for each type of data. Major tables in the database are named constants (for your Organization Info), accounts, transactions, and splits (for the split lines of transactions). There are a number of other tables, but you are less unlikely to want to select from them.
To see what columns are available in each table, use "select * from tablename" for the different tables. That will give you a report listing every column. You can then change the "*" to one or more column names, separated by commas, to get the columns you are really interested in. All SQL entry is case-insensitive, i.e. it doesn't matter whether you enter upper case or lower case for things like column names and keywords like "Select". (However, the special arguments listed above must be upper case.) For those who already understand SQL, the table structures are all defined in the file TABLES.SQL in the main program directory, usually C:\Program Files\Accounts or C:\Program Files (x86)\Accounts.
Here is one example of how you might use this SQL Select routine directly. As of October 2012, there is no report that merely lists your Vendors (that you set up with the Maintenance ⇒ Vendor List menu option). So, after running "Select * from Vendors" to see what columns there are, you could use a SQL statement such as the following to make a list of their names and business phone numbers, sorted by their names:
select name, workphone from vendors
order by name
One problem you can have if you use this utility to create reports, is that the report may be too wide to print. There are a couple of things you can do to resolve this problem. If it's only a bit too wide for the standard print format (which is in Portrait mode, where the page is vertical), select the Landscape radio button in the SQL Select window to switch to Landscape mode, where the page is horizontal. Another obvious option is to reduce the number of columns you are displaying. If you use the Grid Report Type, you can click and drag to make the columns narrower.
As with any other report that is displayed, you can export the results of a SQL Select to various output formats, including Excel, using the Save As button once the report is displayed. This is one good way to "pretty up" a report created with SQL Select, if you are not quite satisfied with the results. However, please see the warning about problems with that here.
Making Changes with SQL
It is also possible to use this routine to make changes to the data in your database, using the SQL INSERT, DELETE and UPDATE statements. In normal usage there would be no reason to do this, because all normally required tasks can be done using the other features of ACCOUNTS. However, in rare cases, particularly if we are helping you with a technical support issue, this may be appropriate. You will need us to approve the change you want to make, and give you a special password in order to use this feature.
If you need further help with this option, feel free to contact support for assistance.
This rarely used facility is the one exception to the statement in the help topic on the Audit Trail that all activity on transactions is tracked by it. For technical reasons, if agreement is ever granted for using SQL statements to modify existing transactions, it would be almost impossible to have them tracked properly in the audit trail.
SQL Tutorials
For more information on how to write SQL, there is a web page with a list of several SQL tutorials, at http://www.thefreecountry.com/developercity/sqltutorials.shtml.
This topic was last edited on Mar 4, 2022