Viewing and Exporting Data with SQL Selects

<< <%SKIN-STRTRANS-SYNTOC%> >>

Navigation:  DATABASE MAINTENANCE >

    Viewing and Exporting Data with SQL Selects

SQL stands for Structured 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. We 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 and that cannot be created with Reports Custom Report.

 

Note: This option is not available with the free Lite version of DONATION.

 

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 automatically, and then puts you into this SQL Select window.

 

When you select Database ⇒ SQL Select (or come to here after clicking OK in the Custom Reports window), the following window comes up on which you can enter a SQL Select statement:

 

SqlSelectWindow

 

If you have come into here from Reports Custom Report, the SQL statement will already be filled in to the large entry box in the window.

 

You can save a SQL statement that you enter here (or that was created by a Custom Report) 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 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 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.

 

If you enter the program with the Donors Only Password, you cannot run SQL that references any database tables or columns that include financial information, such as pledges, donations, receipts, bank deposits and cash counts.

 

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 Donation Date and Category Selection

 

There are six arguments that can be used in different combinations for selecting only a single donation date, a range of donation dates, a range of donation dates and optionally a donation category, the current fiscal year, or the current fiscal year and optionally a donation category, to include in the data selected by the report. These would be included in a WHERE clause in the report (which restricts the data to data satisfying a given condition). There are radio buttons in the Custom Reports window that make these be included automatically, but you can also use them directly if you understand what you are doing. The following are the options:

 

Please note that these arguments are case-sensitive, so for instance :donation_date would not work if you wanted the first option below, :DONATION_DATE.

 

:DONATION_DATE - if this is present, when you Run the report, a window will come up prompting for a single donation date to include in the report. This  argument would generally be used in a SQL phrase such as:
 
WHERE donation.date_received = :DONATION_DATE
 

:DONATION_FROM_DATE and :DONATION_TO_DATE - if these are both present, when you Run the report, a window will come up prompting for starting and ending donation dates to include in the report. Example SQL:
 
WHERE donation.date_received between :DONATION_FROM_DATE and :DONATION_TO_DATE
 

:DONATION_FROM_DATE, :DONATION_TO_DATE and :CATEGORYNUM - if all three of these are present, when you Run the report, a window will come up prompting for starting and ending donation dates to include in the report, and optionally a single donation category to include. Example SQL:
 
WHERE donation.date_received between :DONATION_FROM_DATE and :DONATION_TO_DATE
AND (:CATEGORYNUM = -1 OR category.categorynum = :CATEGORYNUM)
 
Note: the ":CATEGORYNUM = -1 OR" part makes the SQL work even if you don't select a category in the window prompting for a range of dates and an optional category.
 

: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. (Note:when you do this, for it to work you have to use the donors_last_2years view in place of the usual donors table. This is done automatically for you when come from the Custom Reports window.) Example SQL:
 
FROM donors_last_2years donors
...
WHERE donation.date_received between :FISCAL_START_DATE and :FISCAL_END_DATE
 

:FISCAL_START_DATE, :FISCAL_END_DATE and :CATEGORYNUM - if all three of these are 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, a window will come up showing you the fiscal year start and end dates, and prompting for an optional single donation category to include. See the previous two bullet points for notes about the :CATEGORYNUM argument, and the use of the donors_last2_years view. Example SQL:
 
FROM donors_last_2years donors
...
WHERE donation.date_received between :FISCAL_START_DATE and :FISCAL_END_DATE
AND (:CATEGORYNUM = -1 OR category.categorynum = :CATEGORYNUM)

 

There is one additional code that can be used in the SQL in this window, which does not cause any prompting. It is :FIRST_FISCAL_RECEIPT_YEAR, and it will be replaced by 0 if fiscal year receipting and data entry is not turned on in the Maintenance Organization Info window, or by the number of the fiscal year in which that option was turned on if it is turned on. This is mostly for use in SQL generated by the Reports Custom Reports window, when displaying receipt numbers.

 

Details and Examples

 

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, and to hold the current year number you are working on), donor, donation, category (for donation categories), donor_category1 and donor_category2 (for the donor categories) and receipt. There are a couple of other tables, but they are used only for very technical purposes and you are unlikely to want to select from them.

 

There is also a view (which behaves like a table) called donors_last_2years, which includes all donors in the current working year, plus all donors from the previous working year that are are not present in the current working year, either because you deleted them, or because they were added after the initial copy of all donors to the new year, when you first switched to the new year.

 

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

Select * from category

Where year_number = 2016

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 = 2016

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 things like column names and keywords like "Select". 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\Donation.

 

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 or Custom Reports, if you are not quite satisfied with the results.

 

If you need further help with this option, feel free to contact support for assistance.

 

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.

 

Making Changes with SQL Statements

 

It is also possible to use this window 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 we are helping you with a technical support issue, this may be appropriate. You will need us to give you a special password in order to run such SQL commands.

 

Note to Users of Version 2.70 or Earlier of DONATION

 

If you previously used version 2.70 or earlier (last released in mid-2007), 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 (for example using "select * from tablename", or consulting the file TABLES.SQL) to determine the new field names. If you get stuck, contact technical support.