Background

SQL is a (very simple) programming language. It is the standard "language" to access any "grown up" database, such as Interbase, Oracle and Microsoft SQL Server. The SQL statements can be fed into a database in either of two ways:

1. From a Delphi (or any other) program - Delphi is set up to take the strain out of working out how to send through the statements.
   
   
2. Directly into Interbase through IBConsole that comes with Interbase.

The SQL commands fall into two main categories:

1. the Metadata statements that control the structure of the database and tables and
   
   
2. the Data statements that control the data within the database.

The good news is that the entire language is made up of between 20 and 50 statements - so it is not too taxing to learn. The problem with SQL, therefore, is not its rules, but how to apply them to make use of the vast power of Interbase. To be of maximum help:

• this guide will provide only a cursory introduction to SQL statements. The majority of the guide will deal with concepts, practical requirements and solutions to help you get the most from Interbase in the minimum time. It deals primarily with the data manipulation aspects. More extensive details of SQL can be found in the Interbase SQL guide.
• attached to this guide, you should find the file Practical.SQL which you can open in any simple text editor, such as Notepad or Delphi. The file provides a gaggle of SQL statements which carry out different functions we have had to generate in applications, and which should help you deal with a number of practical problems that can be solved by Interbase easily, once you have the answer how to do it.

Metadata SQL - some concepts

The Metadata SQL commands in Interbase control the structure of the database, and not its data contents. You are unlikely to need these statements when first using Interbase, so this guide is either for the brave, foolhardy or for those who want to understand the concepts for a future time. The commands are well explained in manuals. There are a few tips below to help deal with a number of practical issues:

The Database itself

The commands allow you to "create" and to "drop" a database (eg. of sql: create database 'employee.gdb' and drop database.

• Watch out - "drop" means delete the entire database, and there are precious few (ie. no) controls to prevent accidental use of this statement.
• Distinguish these commands from the commands that allow you to "connect" and "disconnect" from the database.
• These commands only work for an existing database.
• Connecting (eg. of sql:Connect 'machine1:c:\general\interbase\data\IBDemo.gdb' user 'SYSDBA' password 'masterkey' )is the equivalent of saying "Use the database I am about to give you for all the commands I give you until I disconnect".
• Disconnecting (eg. of sql: Disconnect 'IBDemos.gdb') is the termination.
• One pair of commands that is clearly in the "confusing" series are the "Commit" and "Rollback" statements. When you send through to Interbase a series of commands, by default Interbase treats them as being temporary pending a call to Commit (eg. of sql: Commit) or Rollback (eg. of sql: Rollback). If you Commit explicitly (or if you call it indirectly either by using from within Delphi a TTransaction component to StartTransaction and/or CommitTransaction, or by using a sql statement followed by a command to close the database), the transaction will be cast in stone. Alternatively, if you call the Rollback statement directly or indirectly, the transaction (or all transactions since the last commit or rollback command) will be reversed and eliminated from the database.

Table control

You can create or alter a table (eg. of sql : Create table WebCounter (ref integer, visit_date date, visitors_ip_address string); - remember the semi-colon) once you have connected to the database.

• You can drop (ie. delete, along with all its data - again, no warnings, so be very careful ) the table (eg of sql: Drop table WebCounter ) or, alter the table (eg of sql : Alter table WebCounter add column Page_visited varchar(60) default 'HomePage'; or Alter table WebCounter drop Page_visited; ). This is invaluable if you want to add a column to a table you have already set up, or drop it.
• If you want to change a column (eg. a column with a reference as an integer to a reference as an integer), you first need to create a new "temporary" column, transfer all the data from the "old" to the "temporary" column, drop the "old" column, add the "new" column you want to create, copy the data from the "temporary" column to the "new" column, and then drop the "temporary" column. Easy, isn't it!

When modifying a table, you may find that you are not permitted to "drop" a column.

• If a column is used in a stored procedure, or a calculated field, the column is labeled as "having a dependency".
• You will have to delete all the "dependencies" before you can delete a column - then re-enter the dependencies you have just created. This is a good reason to get the table structure right before you create large numbers of dependencies (if you can).

There are some other Metadata statements that are useful.

Generators

A Generator set (eg. of sql : CREATE GENERATOR VISIT_GEN; or SET GENERATOR VISIT_GEN TO 1; or DROP GENERATOR VISIT_GEN; ) is an Interbase mechanism to generate a number count.

• You can set it to, say, 1. Each time you then call the Generator, Interbase tells you what the current number is and then increases if for you automatically.
• To call the Generator, you will probably want to set up a simple stored procedure that you can call either from Interbase or from a Delphi program (eg. of sql : CREATE procedure NEW_VISIT RETURNS (NEW_REF integer) AS BEGIN NEW_REF=gen_id(visit_gen,1); END^ - note the strange syntax of semi-colon and ^ symbol. See the explanation of importing SQL scripts into Interbase for an explanation).
• You can then get the next number in a sequence by calling the stored procedure New_visit, and the new reference will be returned in the output parameter "new_ref".

Triggers

A Trigger set (eg. of sql : Create trigger set_emp_no for employee before insert as begin new.ref=gen_id(employee_gen,1); end^ ) instructs Interbase to carry out the statements in the Trigger statement automatically (ie. without be called explicitly) every time data in the database is updated. The example statement will set the "ref" field in a record immediately before it is inserted to the table "employee", with the next value in the generator "employee_gen" .

• The Trigger set allow you to set up automatic procedures either before or after any of an insert, update or delete to a table. Vastly useful if you want to force a series of updates on the basis, say, of a delete. Hugely frustrating if you also want use the new reference number just created within the Delphi program that set the Trigger in motion. For this reason, using Triggers to create unique reference numbers may be less preferable than calling a stored procedure to return the new generator number, and including the new reference number with the other information of the record that is to be inserted to the table.
• A warning about Triggers - the "rollbacks" do not work quite as you might expect. Generators, for example, will not be set back to their earlier value, even within a rolledback Transaction series.

Indexes

Indexes allow InterBase to locate data (dramatically) more quickly. An index has two key components to it - the field(s) that you will want to search on and whether the field(s) are unique (e.g. a Reference number will probably need to be unique, but you may well need to accommodate several people sharing a birth date or a last name).

One particular type of index that is usually needed is an index on the Field(s) which uniquely identify a record within a table (e.g. the unique reference number given to each record, or a Social Security ID, or a post code and House number/name combination within an Address table). This is called the Primary key. Those who don't feel comfortable with what they are doing might want to see Creating indexes - Tips, prior to "doing it".

An index set (create and drop) is used with in searching and ordering by the indexed column. (eg. of sql: Create unique ascending index SurnameX on person (lastname, firstname, password); or Drop index SurnameX; )

• If an index is unique, you can not enter a second record with the same details as the first. Useful if you want to restrict entries to just one, say, password.
• Breaching this rule will be fed back to a program as an error message which you will have to trap in Delphi if you want to provide a graceful message to the program user. In certain situations, therefore, you may prefer to check for the existence of a particular record from within Delphi before passing the record through to Interbase to update.

Data manipulation - SQL

6.3.1 Insert

You insert a record to an Interbase table (or tables) with the Insert command (eg. of sql: INSERT INTO Nominal_Code (ref,nl_Code, nl_Name, BalanceSheet_Category,Report_Category) VALUES (1, 100, 'Shares', 'b', 'fa'); ).

6.3.2 Update

You update a record that already exists within an Interbase table (or tables) with the Update command (eg. of sql: UPDATE employees SET new.salary=old.salary*1.1 ). With the update statement, you would usually want to control the situations where you update a record, such as update salary only where the employee has been awarded a "satisfactory" rating or more. For control over whether you carry out one Update command depending on whether a given circumstance is met, see the explanation of the Where statement.

With other update statements, you want even more control using an IF statement, such as updating a stock record if a stock item has been ordered from a customer and can be fulfilled, or inserting a stock re-order item for a supplier in another table if the customer order can not be fulfilled. To use the more precise control of an IF statement where you want to select from more than one action in different circumstances, you would need the facility of grouping a number of SQL statements together using a Stored Procedure.

Delete

You delete a record that already exists within an Interbase table (or tables) with the Delete command (eg. of sql: DELETE from employees ). WARNING! This command deletes all records in the table employees, and there are no warnings. With the delete statement, you would usually want to control the situations where you delete a record, such as delete a record from the employees table only where the employee reference is the given number. For control over whether you carry out one Delete depending on whether a given circumstance is met, see the explanation of the Where statement.

With other delete statements, you want even more control using an IF statement, such as deleting a fulfilled sales order if the order has been copied to the archive file, or doing the archive if not and then deleting the record. To use the more precise control of an IF statement where you want to select from more than one action in different circumstances, you would need the facility of grouping a number of SQL statements together using a Stored Procedure.

Select

The Select statements are very powerful, and there are a few more things you may want to achieve, ranging from the simple to the very advanced.

1. You pull out a record set from within an Interbase table (or tables) with the Select command (eg. of sql: SELECT ref, first, last FROM employees ).
   
   
2. You specify the fields you want to see (such as ref, first and last in the example). If you want all fields, you can use the '*' character (eg. of sql: SELECT * FROM employees; ).
   
   
3. On occasions, you want to pull out a field, but report it in a more user friendly way. You can achieve this using the "AS" word (eg. of sql: SELECT first, last AS surname FROM employees; - this will report the result set with all first names headed as "first" and all last names headed as "surname").
   
   
4. You may also want to join fields together into one field, such as report a name as the merging of "first" and a space and "last". You can achieve this using the '||' characters (eg. of sql: SELECT ref, first || ' ' || last AS FullName, salary FROM employees; ). We have found this facility particularly useful when creating a result set that will be displayed within an Internet application, where you want a field in the result set to be a link to, say, a document or image. You can achieve this effect by pulling out a column, say, surname, which merges the html with the database fields (eg. of sql: SELECT first, '<a href="http://webserver/application.exe/employeelink?Ref=' || ref || '>' || last || '</a> AS surname from employees; ).

With the select statement, you would usually want to pull out only certain records, such as select all records from the employees table where they are in a given department. For control over the content of selected records depending on whether a given circumstance is met, see the explanation of the Where statement.

6.4 Great SQL tips

Where

The data manipulation sql set (insert, update, delete and select) all work in conjunction with the "where" clause. The "where" clause allows you to limit the sql command to doing just what you tell it to do. An example is selecting out all records from employees, but only if the employee is in the IT department, or updating all salaries, but only where the employee has been evaluated as "satisfactory or better".

1. The "where" clause within the SQL command allows you to compare any field of any table with a given value. The value can be a number, a string, a date or another field. The comparison can be=, <, > or various others (eg. of sql : select ref, first, last, salary, department where department='IT' and salary > 125000 or update employees set new.salary=old.salary + 55000 where department='IT' and old.salary < 95000 ) ).
   
   
2. If you are comparing strings, you often want to use a Wildcard character ("*" in windows). The Interbase equivalent is '%' (eg. of sql: select ref, first, last, salary, department where last like 'Smi%'; to pull out anyone with the Surname starting with Smi and followed by anything, such as Smith, Smithe, Smile ect.) You can also use the '%' before a letter series (eg. of sql: SELECT ref, first, last, salary, department FROM employees WHERE department like '%IT%'; - this would pull out any department with IT anywhere in the string). Note that you have to use the LIKE word. Using the "=" symbol would not work, because Interbase would be looking for the character string that was exactly equal to '%IT%' (ie. the '%' would be treated as a character instead of a wildcard)
   
   
3. You can use the AND word to limit the result set to those that meet both (or all) of the AND statements (eg. of sql: SELECT * FROM employees WHERE department='IT' and salary <=90000 and salary >=20000; )
   
   
4. You can also use the OR word to expand the result set to those that meeting any of the OR statements (eg. of sql: SELECT * FROM employees WHERE (department='IT') OR (salary <=90000 AND salary >=20000); ).
   
   
5. Note where you mix both the OR word and the AND word that you should use brackets to be clear about what you intend. Each statement within the bracket is evaluated before any statement outside a bracket. If the statement in the example above has been without brackets (SELECT * FROM employees WHERE department='IT' OR salary <=90000 AND salary >=20000;), Interbase would not have know whether the statement meant pull out everyone within the IT department and also pull out anyone with a salary between 20K and 90K (as intended in the example) or SELECT * FROM employees WHERE (department='IT' OR salary <=90000) AND (salary >=20000);, which is everyone who is either in the IT department or who has a salary less than 90K, but who also must have a salary > 20K. The latter example would preclude people in the IT department who earn less than 20K, which is clearly not intended.
   
   
6. You may want to compare with one value does not equal another. There are several alternatives, but the one method that we have found infallible is to use the NOT word in front of the statement to negate (eg. of sql: SELECT * FROM employees WHERE not (department like 'IT%' ); ). If you try another method (eg. WHERE department <> 'IT') within a larger SQL command, and items do not get reported as you would expect, remember the slightly uncomfortable solution above.
   
   
7. There are (many) times where you would want to use parameters for the comparison values. This would allow you the facility to set up a SQL statement which you could amend at run time (eg. which does not work: SELECT * FROM employees WHERE salary > :salary ; ). You would want to be able to "invoke" the select command substituting the :salary parameter with, say, the amount input by a user. To achieve this result, can use two alternatives. Either set up a Stored Procedure, which does take parameters, or generate the SQL within Delphi at run time using one of the Interbase components.
   
   
8. In all the above examples, we have compared a field with a fixed value. You can also compare the value with another field within the database. You may want to select all sales where the customer's county is different from the county of the department who serves that customer (eg. of sql: SELECT Customers.Ref FROM Customers, Departments WHERE Customers.Department_ref=Departments.Ref AND not (Customers.Postcode=Departments.County); ) . This example joins two table together, for which you may want to see SQL : Multiple Tables
   
   
9. A very powerful extension to comparing a field with another field is the ability to compare a field with a result set from another sql command (what?!?). Say you wanted to select all records from employees in the IT department where the salary was less than the maximum salary being paid to a Secretary. The WHERE clause can compare the Salary of the employee with the result set of a separate select statement that pulled out the maximum salary of an employee where the department was 'Secretary' (eg. of sql: SELECT ref, first, last FROM employees WHERE department='IT' AND salary < ( SELECT max(salary) FROM employees WHERE department='Secretary') ;). In this example, the bracketed SELECT statement returns a single result. (How? See Getting Statistics from the Database). A quick warning - if the sub-select statement is complicated or, itself, uses another sub-set, this can be a hugely time consuming process. The solution, if you find the time delay is unacceptable, is to use Stored Procedures
   
   
10. Another very powerful extension to comparing a field within another field, is the ability to compare a field if it is within a result set of another SELECT statement, using the IN word. Say you wanted to pick out all accounts that were within the profit and loss account, and the profit and loss accounts were recorded in a separate table. The WHERE clause would compare the Account_ref of the accounts with the list of Account_refs from the master table that were profit and loss items (eg. of sql: SELECT sum(account_value) FROM accounts WHERE account_ref IN (select account_ref from Accounts_Master where Account_type='Profit and loss'); ). A quick warning - if the sub-select statement provides a long result set, this can be a hugely time consuming process. The solution, if you find the time delay is unacceptable, is to use Stored Procedures
    
    
11. The "Where" statement applies equally to the Update, Delete and Insert statements.
    
    
12. There are a number of other comparators that can be fun, such as "Starting with", "Containing" and others. These are explained in more detail in the Interbase SQL manuals.

Order by

You sort a result set into a desired order with the Order by statement.

1. You can Order by any field or fields within the original table (eg. of sql : SELECT * FROM employees WHERE department='IT' ORDER BY last; )
   
   
2. You can Order by more than one field at a time (eg. of sql: SELECT * FROM employees ORDER BY last, first; ).
   
   
3. You can select restricted fields, and order by a field that is not displayed, if you ever find this to be of benefit (eg. of sql: SELECT ref, first FROM employees ORDER BY last; )
   
   
4. The default sort order is to sort in ascending order, but you can define it as descending (eg. of sql: SELECT * FROM employees ORDER BY last desc, first asc; - where desc is short for descending and asc is short for ascending)

If you have defined an index which puts a result set in the same order as the Group By statement, Interbase will automatically detect this and will use the index to speed up the search and sort. You can not use the name of the index within the Group By statement(!)

Multiple tables

You will often want to select data which involves more than one table at a time. You may have data in two tables which you want to combine in one report. An example is where you keep employee details in one table, and address of everyone on another table, and you want to report the names and address of all employees.

1. To use multiple tables, you can refer to them in the SQL statement. The tables must be "joined" in a logical way. Within an Employees table, for example, if the address is not stored directly in the Employees table, then there must be a reference in the Employees table to the record in the Address table, to be able to match the records. The Select statement must link the two tables with the "WHERE" statement. (eg. of sql: SELECT employees.first, employees.last, address.address1, address.address2, address.county, address.postcode FROM address, employees WHERE employees.address_ref=address.ref; ).
   
   
2. You can create shorthand for the full name of the table to save typing, by "defining" the shorthand immediately following the table's name (eg. of sql : SELECT e.first, e.last, add.address1, add.address2, add.county, add.postcode FROM address add, employees e WHERE employees.address_ref=address.ref; )
   
   
3. One word of warning - when you "join" tables (as above), Interbase dumbly does what you instruct. If there is a unique reference within the address table, then there will only ever be one record from the address table matched with any one employee record (since the employee record can not have more than one Address_ref in its Address_ref field). But, if you ask interbase to join two tables where there can be many matches for each record, you will get a huge number of records returned.
   
   If, for example, you had "joined" the tables with the statement "select * from address, employees WHERE employee_ref < address_ref", then Interbase would have returned a result set whose fields were every field from both the address and the employee table. The number of items in the result set would have been huge. Interbase would start with the address table. It would then look at the first employee record and see if the condition were matched. If so, the combined record would be added to the result set. Then, with the same address record, it would then look at the next employee record. Again, if the condition were satisfied, the combined record would be added to the record set. And so on until the end of the employee table. At this point, there may be several hundred records. But Interbase would only have completed the first record of the Address table. It would then carry out the same process with the second address record, by which time another few hundred records may have been added to the result set. Then Interbase would have gone on to the third address record, and then the fourth and so on, until the end. If there were several hundred records in both the Employees and the Address files, it is easily conceivable the result set would have exceeded half a million.
   
   The symptom of an excessive join is Interbase taking several minutes to several hours to process the query and perhaps, before it is able to finish, running out of memory. The solution is to check the SQL statement by trying to reperform the computer logic, to ensure that the join only ever matches one record in one table with one record in the other table, unless you specifically want there to be a many-to-one relationship.
   
   
4. The "join" above is known as an "inner join" because it returns only records that are in BOTH tables. This means that a record in the employee table that has a matching record in the address table will be reported, but a record in the employee table without a matching record (such as the employee's address has not yet been entered to the system), is not reported.
   
   There are many times where you want to report all records in one table, and the only the matching information from the other table. An example would be reporting all work in progress and, if an invoice has been raised, including the invoice information, but still reporting the work in progress even if the invoice has not been raised.
   
   The join that achieves this is an "outer join". Inconsistently, the outer join requires you to use an additional word "on" to define the linking reference fields, in place of the "where" clause in the examples to date (eg. of sql: SELECT wip.ref, wip.job_name, wip.value, inv.amount FROM work_in_progress wip LEFT OUTER JOIN invoices inv ON inv.wip_ref=wip.ref WHERE wip.completed_flag='False'; - note using joins does not prevent the use of the "WHERE" clause, but it does require the ON clause ). The word LEFT or RIGHT is used to tell Interbase which of the two tables is the base for which all records will be reported even without a match. In the example, the work_in_progress table is on the LEFT of the statement, and this is the table that you want to be reported in full, even where there is no match.
   
   
5. The OUTER JOIN statement is explained above. The prior examples of inner joins use the WHERE clause to achieve the match. You can also achieve the same result using the INNER JOIN statement (eg. of sql: SELECT e.first, e.last, add.address1, add.address2, add.county, add.postcode FROM address add, employees e ON employees.address_ref INNER JOIN address.ref; )
   
   
6. There are often occasions where you will want to "chain" more than two tables together. You can chain joins conceptually joining two tables together, then joining the result set with the third table (and so on if you have more than three tables to join). This is useful where you are reporting information from more than two tables in one report, such as reporting details from our ebooks site which reports the name of a reader (from the address table), the date of the purchase (from the order table) and the name of the book bought (from the book table) (eg. of sql : SELECT p.ref,p.first, p.last, o.orderdate, d.bookref, b.booktitle FROM address p INNER JOIN orders o ON o.personref=p.ref LEFT OUTER join order_details d ON d.orderref=o.ref LEFT OUTER JOIN books b ON b.ref=d.bookref order by last, orderdate; - if you want to use a further WHERE clause, it comes after the final ON statement, irrespective of which file the restrictions apply to).
   
   
7. There are often occasions where even this level of combination is inadequate for what you want to achieve. Although conceptually the second join works on the result set of the first join, there is very little manipulation you can carry out prior to the second join. This can be too limiting. An example of the sql constraints are where may need to get the sales totals of each salesperson on which to calculate commission, you need to report the result in descending order of sales totals. There is no sql command you can use to Order on the results of a calculated amount. You will come across several other circumstances where you need to carry out a function (such as selection, grouping, sorting) on the results of another group.
   
   The solution is to use Views or Stored Procedures, which you are then able to chain together to your hearts content.

Multiple databases

Joining data between two tables all held within the one database is useful. You may find the need to join data held in two or more databases.

All rules that apply to joining tables from a single database apply equally to joining tables from two separate databases. The additional issues that are explained below relate to the need to "login" to both databases which may have different usernames and passwords, and the syntax SQL requires to understand to look in the two databases.

1. The connection issue is simple. For each database you want to connect to, you must enter the username and password. One method to achieve this within a single sql command is to achieve the connections within the Delphi program. Create (or drop onto a form) two separate Delphi Interbase TIBDatabase components, and fill in the details and, if appropriate, the username and passwords. (How? See TIBDatabase components.)
   
   
2. The SQL syntax requires the databases to be within quotation marks, preceded by a colon (eg. of sql: SELECT own.NAME, own.OWNER FROM ":invDB:OWNER" own, ":crownDB:COMPANY" co WHERE co.REF=inv.COMP_REF - where invDB and crownDB are the TIBDatabase names allocated in the Delphi program.) To complete this example, fill in a TIBQuery component, filling in the SQL in the SQL property of the TIBQuery component, and the query is ready to be called by your Delphi program.

Views

Views are sql statements that are "frozen" into a procedure within Interbase. Typically, but not always, a View will return a result set. Once set up, the view can then be called and used as if it is a Table. There are some restrictions (see below), but the value comes from the ability to carry out a reasonably complex SQL statement within a view, and then to use the View as if it were a table, against which you can perform a separate SQL statement.

Views are a quirk of Interbase, so they have to be set up within Interbase. You can set them up as follows:

1. Create a SQL script file, which you will be able to use to "import" the view into Interbase (Example? See the attached file Tables.SQL, and look out for the "Create View" statement
   
   
2. Create the View, which attaches a basic SQL statement to the instruction to Interbase to file the statement within its Views Metadata (eg. of sql: CREATE VIEW Full_Name (iRef integer,iDepartment varchar(30)) AS SELECT ref, first || ' ' || last as EntireName, AddressRef FROM employee WHERE ref >=:iRef and department=:iDepartment;- where the View is called "Full_Name" and two input parameters have been defined as "iRef", which is an integer and iDepartment which is a string. The output is the result set generated by the "Select" statement.)
   
   
3. Once a View has been created, it can be used from any SQL statement (from Interbase or from a Delphi program as described in earlier sections) as if it is a Table (eg. of sql: SELECT * FROM Full_Name(27, 'IT') ORDER BY ref desc; - note that the View has an input parameter, so when calling it, you have to include the parameter after "Full_Name" in brackets. If there had been no input parameters, there would have been no brackets after "Full_Name".
   
   
4. You can create a view which uses the result set of another view (and so on indefinitely). As with any SQL command, if the first view has parameters, the second view must provide the parameter call syntax (eg. of sql : CREATE VIEW Addressed_Full_Name (iRef integer,iDepartment varchar(30)) AS SELECT * FROM Full_Name(:iRef, :iDepartment), Address WHERE Address.ref=Full_Name.AddressRef ORDER BY Full_Name.EntireName; - this is called as any other view. When Interbase hits the "table" Full_Name within the SQL statement, it knows this is a view, the second view is put on hold and the first view is run before processing of the second view resumes.
   
   
5. Views have a couple of limitations - if the limitations prevent you from doing something you need to do, you should almost certainly be using Stored Procedures. (How? See the Chapter on Stored Procedures). One limitation relates to using Views to update, insert or delete data. Views can be used for this purpose, but generally only if there is no ambiguity in the SQL commands to Interbase. If you use a view simply with a SQL command to Update a record, using a where statement, the update should work fine. However, if your view has changed the information from the form of a basic table, or if it is no longer clear which table the information came from, then you are prohibited from using a View for this purpose. For more details, see the Interbase manuals. Another limitation is the inability to define an output parameter.

Statistics

Reporting statistics from data within the database is easy. Used in conjunction with Views and/or Stored Procedures, they provide you with the ability to provide almost any report that may be requested.

1. The basic statistics come from a Select statement, using words like "Sum", "Count" and "Avg" (eg. of sql: SELECT count(ref), avg(VAT), sum(Sales_Value) FROM sales; - this provides the total number of transactions, the average of the VAT and the sum of the Sales_Value.)
   
   
2. The above example provides one total only for the entire database. If you wanted to group a number of like items together and provide the sum for each of the groups, you can achieve this by using the GROUP BY statement. For example, you may have a table of Sales, and you want to know how much are the sales by product line (eg. of sql: SELECT product_line, sum(sales_value) FROM sales GROUP BY product_line; ).
   
   
3. You can also get sub-totals of sub_totals using the same technique. If you wanted to group the total sales value of each product lines sold each sales person, you can do so (eg. of sql : SELECT sales_person, product_line, sum(sales_value) FROM sales GROUP BY sales_person, product_line; .)
   
   
4. You can use any normal SQL statements within the statements, such as ORDER BY, and WHERE clauses.
   
   
5. If you wanted to use the results of the Statistics for a further action, such as sorting the results by descending value of the sum of the sales_value, you will fund you are not permitted to do so within the one sql statement. The simple solution is to create a View with the statistics, then use the View within a second SQL statement to carry out the action that you wanted. (How? See Views)

Sundry

There are several other functions that may help you. They include:

1. If you want to change one datatype to another, use the Cast function. For example, you may want to convert a reference defined as an integer to a string to consolidate within HTML (eg. of sql : SELECT cast(ref as varchar(20)) FROM sales WHERE Payment_flag='False'; )
   
   
2. String comparisons are case sensitive. Programmers and Users are not always consistent when obtaining or entering data (eg. Names) that may be used within case sensitive comparisons (eg. passwords). Interbase has a function UPPER, which converts a field into its Upper case equivalent. This allows comparison of the upper case of one field with the upper case of the other, and removes the case sensitivity problems, if this is what you want to do (eg. of sql : SELECT ref, name FROM employee WHERE upper(first)=upper(:InputParameter); ). Curiously, there is no LOWER equivalent in Interbase.
   
   
3. Interbase has a very limited number of functions (such as CAST and UPPER). There are many occasions you would want these functions extended (such as stripping out blanks, selecting out the left three characters of a string, using a MID function, formatting Currency strings and SIN functions). There are a large number of functions that perform these tasks, and which are available free. Check out the Interbase manuals for External Functions which will give you a pointer to where to get the suite of External Functions (also called User Defined Functions), and how to instruct Interbase where to find the code for them. When you have "imported" an External Function, you can use it in the same way as UPPER and CAST above, using parameters where required by the Function (eg. of sql : SELECT F_FixedDecimalPoint(PRICE,2) FROM Sales; - where FixedDecimalPoint is the imported user defined function, Price is a field in the Sales database, and "2" is the number of decimal places to which the number should be rounded).
   
   
4. Date handling in Interbase is primitive. You may have to use the US format for date entry (ie. mm/dd/yy, instead of the UK configuration of dd/mm/yy), when entering the date as a string. Interbase stores the Date as DateTime. This causes huge problems when trying to pull out transactions on, say, a particular day, since the date stored (which includes the time tagged to the end), will not necessarily equal the date you have input for comparison. There are several solutions to date problems. One is to specify time consistently when you enter a date (messy), another is to use a User Defined Function to strip out the time element of a date field (for which you need the UDF and you need to import it), another is always to use dates within a range (such as 'DateField >=1/1/2000 00:00 and DateField <='1/1/2000 23:59', in place of 'DateField=1/1/2000').