Viewing and Exporting Data with SQL Selects

SQL stands for Structure Query Language. It is the basic programming language used by the Firebird database that stores the data in DONATION, 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. I may also give you some instructions to use this feature either to help debug problems you are having, or to create simple ad-hoc reports that are not included in the Reports menu.

While this SQL Select utility can be used to create custom reports, most users will prefer to use the much easier (though less powerful) Reports à Custom Reports option, which does not require any knowledge of SQL. With that option, you just select columns from lists, specify a range of years, and click OK. It generates the SQL for the report, and puts you into this SQL Select window.

When you select Database à SQL Select, a window comes up on which you can enter a SQL Select statement. You can save that statement to a file with the Save SQL File button, or retrieve an existing SQL statement from a file with the Open SQL File button.

On this window you can also select a Report Type – either Normal or Grid.

The Normal type looks a lot like the built-in reports. The advantages to using the default Normal Report Type 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 type 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 is 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 used if you use the Normal Report Type, not the Grid type.

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.

The DONATION database is composed of tables, one table for each type of data. The tables in the database are named constants (for your Organization Info), donor, donation, category (for donation categories), donor_category1 and donor_category2 (for the donor categories) and receipt.

Here is one example of how you might use this routine. Suppose you wanted a list of your donation categories, for the donation year 2007. There is no report built into DONATION to create such a list. (Well, OK, there is. But this is still a good example!) Enter the following SQL statement:

Select * from category

Where year_number = 2007

Note that "*" means "all columns in that table". When you Run this SQL, you will see that it generates a report listing three columns – Categorynum, Year Number and Description. You may decide that all you really want to see is the Description column. Close the Reports window, and change the SQL to say:

       Select Description from category

       Where year_number = 2007

Print off the result, and there is your list of donation categories!

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. For instance, "select Year_Number, Description from category". All SQL entry is case-insensitive, i.e. it doesn't matter whether you enter upper case or lower case. For those who already understand SQL, the table structures are all defined in the file TABLES.SQL in the main program directory.

To create more sophisticated SQL statements, the best way is to copy from good existing ones. To do that, run one of the normal Reports that is somewhat similar to what you want. On the Reports window, you will see a Save SQL button. You can use that to save the SQL Select used by that report to a file. Then come back to this routine, and use the Open SQL File button to retrieve that saved SQL from the report. You can then usually immediately Run the SQL to see its results, or modify it somehow (perhaps you want to add a column, or remove one?) to get the result you are looking for. (Another option is to modify the saved SQL, then load it back into the same report with the Load SQL button.)

The one case in which you cannot immediately run a SQL Select saved from a Report is when it is a report that prompts you for information before running. For instance, Reports à Donation à One Date Details prompts you for the date to report on. If you save its SQL and then open that in Database à SQL Select and try to Run it, you will get an error message that includes "Arguments for select are invalid or incomplete". Look at the SQL and you will see that it includes ":selected_date" in it. Anything preceded by a colon is an argument that you must replace by an actual value to run the SQL. In this case, you could replace ":selected_date" with a real date such as '2007/12/31' (include the quotes, which must be single quotes, and use this format for dates). Then when you Run it, it will work.

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. Finally, 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. This is one good way to "pretty up" a report created with SQL Select or Custom Reports, if you are not quite satisfied with the results.

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 DONATION. However, in rare cases, particularly if I am helping you with a technical support issue, this may be appropriate. You will need me to give you a special password in order to use this feature.

As usual, if you have any comments about this routine or need help with it, feel free to contact support for advice.

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.

Note to Users of Version 2.70 or Earlier of DONATION

If you previously used version 2.70 or earlier, which used the Sybase Adaptive Server Anywhere database, and have upgraded to version 3.00 or higher, which uses the Firebird database, some of the database field names have been changed, and also there are some fairly minor "dialect" differences in the SQL used by the two databases. As a result, if you had any saved SQL files that you used with SQL Select with the older version, they will probably need some adjustment in order to work with the new version. You can use the techniques in this help page (e.g. using "select * from tablename", or consulting the file TABLES.SQL) to determine the new field names. If you get stuck, contact technical support.