[ << ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
For data retrieval BeeBase offers two ways: the programming feature and the query editor.
The programming feature allows you to install buttons in table masks which, when pressed, call program functions. The usage of this feature is described in the structure editor chapter (see Structure editor) and in the chapter about programming BeeBase (see Programming BeeBase).
This chapter describes the usage of the query editor, a dialog where you can enter queries and view the output in a scrolling list-view.
14.1 Select-from-where queries | How a query looks like. | |
14.2 Query editor | How to enter and manage your queries. | |
14.3 Exporting queries as text | Export the result of a query as a text file. | |
14.4 Exporting queries as PDF | Export the result of a query as a PDF file. | |
14.5 Printing queries | Print the result of a query. | |
14.6 Query examples | Some examples. |
BeeBase offers a select-from-where query similar to the one in SQL database systems. The query allows you to list the record contents from one or more tables. Only records matching certain criteria are included in the output. The (incomplete) syntax of an select-from-where query is
SELECT exprlist FROM tablelist [WHERE test-expr] [ORDER BY orderlist] |
where exprlist is a comma separated list of expressions to be printed (usually the field names) or a simple star * matching all fields of the specified tables, tablelist is a comma separated list of tables whose records are examined, test-expr is the expression that is tested for each set of records to be included in the output, and orderlist is a comma separated list of fields that defines the order for listing the output. Please note that the WHERE and ORDER BY fields are optional, denoted by the brackets [].
For example, the query
SELECT * FROM table |
lists the field contents of all records in the given table.
SELECT field1 FROM table WHERE (LIKE field2 "*Madonna*") |
lists the value of the field1 field in all records of table where the contents of field field2 contain the word `Madonna'.
For more information about the select-from-where query including its full syntax, see Programming BeeBase, for more example see Query examples.
For entering and running queries, open the query editor by choosing menu item `Program - Queries'. The query editor is able to manage several queries, however only one query is displayed at a time. The query editor window contains the following items:
The query editor is a non-modal dialog. This means that you can leave the query editor open and still work with the rest of the application. You can close the query editor at any time by clicking on the close button in the window title bar.
You can export the results of a select-from-where query to a text file by pressing the `Export' button. This will open a window containing
After you pressed the `Export' button, BeeBase will open the specified file and write out the query result including a header line containing the list header. The fields are written in the order of the columns in the list.
On Windows, Mac OS X, Linux and MorphOS you can export the query results to a PDF file by pressing the `PDF' button.
A window is opened that contains the following elements:
After pressing the `Create PDF' button, BeeBase opens the specified file and writes out the query result including a header line containing the query field names. The fields are written in the order of the columns in the list.
After you have run a query you can print the result by clicking on the `Print' button in the query editor.
On Windows, Mac OS X and Linux this opens the standard print dialog.
If you are running the GTK version of BeeBase, the dialog contains a custom page `Font' where you can specify a font and enable the shrinking of all contents such that they fit into the page width of the selected paper and orientation. After pressing the `Print' button the query results are sent to the selected printer.
On MorphOS the `Print' button opens a similar dialog when exporting queries as PDF (see Exporting queries as PDF).
The window contains the following elements:
After pressing the `Create PDF' button, BeeBase generates a temporary PDF file and opens it with the external viewer (see External viewer). You can then use the printing facilities in the external viewer for sending the query results to your printer.
On other Amiga systems, a print dialog containing the following items is shown:
After you are done with all settings, click on the `Ok' button to start the print job.
To give you an impression of the power of the select-from-where queries this section gives you some sample queries.
Suppose we have two tables `Person' and `Dog'. `Person' has a string field `Name', an integer field `Age', and two reference fields `Father' and `Mother' that refer to the father and mother records in table `Person'. The table contains the following records:
Name Age Father Mother
--------------------------------
p1: Steffen 26 p2 p3
p2: Dieter 58 NIL NIL
p3: Marlies 56 NIL NIL
p4: Henning 57 NIL NIL
|
`Dog' has a string field `Name', a choice field `Color' and a reference field `Owner' that refers to the owner in the `Person' table. The table contains the following records:
Name Color Owner
-----------------------
d1: Boy white p3
d2: Streuner grey NIL
|
Given these data the following sample select-from-where queries can be run:
SELECT * FROM Person |
results to:
Name Age Father Mother
--------------------------
Steffen 26 Dieter Marlies
Dieter 58
Marlies 56
Henning 57
|
(For the reference fields the `Name' field of the referenced record is printed.)
SELECT Name "Child", Age, Father.Name "Father", Father.Age "Age", Mother.Name "Mother", Mother.Age "Age" FROM Person WHERE (AND Father Mother) |
results to:
Child Age Father Age Mother Age
----------------------------------
Steffen 26 Dieter 58 Marlies 56
|
SELECT Name, Color, (IF Owner Owner.Name "No owner") "Owner" FROM Dogs |
results to:
Name Color Owner
------------------------
Boy white Marlies
Streuner grey No owner
|
SELECT a.Name, a.Age, b.Name, b.Age FROM Person a, Person b WHERE (> a.Age b.Age) |
results to:
a.Name a.Age b.Name b.Age
---------------------------
Dieter 58 Steffen 26
Marlies 56 Steffen 26
Henning 57 Steffen 26
Dieter 58 Marlies 56
Henning 57 Marlies 56
Dieter 58 Henning 57
|
[ << ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
This document was generated on September, 28 2024 using texi2html