Query-ontwerp

In Query-ontwerp kunt u een database-query maken of bewerken.

Om toegang te krijgen tot deze functie..

In a database file window, click the Queries icon, then choose Edit - Edit.


Notitiepictogram

De meeste databases gebruiken query’s om databasetabellen te filteren of te sorteren teneinde records op uw computer weer te geven. Weergaven bieden dezelfde functionaliteit als query’s, maar op de server. Als uw database zich op een server bevindt die weergaven ondersteunt, kunt u weergaven gebruiken om de records op de server te filteren en zodoende de weergavetijd te verkorten.


Notitiepictogram

Selecteren van de opdracht Weergave maken van de tabpagina Tabellen van een databasedocument, toont u het venster Weergaveontwerp dat lijkt op het hier beschreven venster Queryontwerp.


De lay-out van de query-ontwerpweergave wordt opgeslagen met de gemaakte query, maar kan als een gemaakte weergave worden opgeslagen.

De ontwerpweergave

Als u een query wilt maken, klikt u op het pictogram Query's in een databasedocument. Klik vervolgens op Query maken in ontwerpweergave.

The lower pane of the Design View is where you define the query. To define a query, specify the database field names to include and the criteria for displaying the fields. To rearrange the columns in the lower pane of the Design View, drag a column header to a new location, or select the column and press +arrow key.

Boven in het query-ontwerpvenster worden de pictogrammen van de werkbalk Query-ontwerp en de werkbalk Ontwerp weergegeven.

Als u een query wilt testen, dubbelklikt u op de querynaam in het databasedocument. Het queryresultaat wordt weergegeven in een tabel die vergelijkbaar is met de gegevensbronverkenner. N.B.: de tabel die wordt weergegeven, is een tijdelijke tabel.

Toetsen in query-ontwerpweergave

Sleutel

Functie

F4

Voorbeeld

F5

Query uitvoeren

F7

Tabel of query toevoegen


Bladeren

Wanneer u Queryontwerp voor het eerst opent, ziet u een dialoogvenster waarin u eerst de tabel of query moet selecteren die de basis zal vormen voor uw nieuwe zoekopdracht.

Dubbelklik op de velden om deze aan de query toe te voegen. Met slepen en neerzetten kunt u relaties definiëren.

Notitiepictogram

Tijdens het maken van een query kunt u de geselecteerde tabellen niet bewerken.


Tabellen verwijderen

Wilt u een tabel uit het ontwerpvenster verwijderen, dan klikt u op de bovenste rand van de tabel en opent u het contextmenu. Kies de opdracht Verwijderen om de tabel uit het ontwerpvenster te verwijderen. U kunt de tabel ook verwijderen met de toets Delete.

Tabel verplaatsen en tabelformaat wijzigen

U kunt het formaat van tabellen wijzigen of tabellen verplaatsen. Wilt u tabellen verplaatsen, dan sleept u de bovenste rand naar de gewenste plek. Het formaat van de tabellen kunt u wijzigen door de randen of hoeken van de tabel naar binnen of naar buiten te slepen.

Tabelrelaties

Als er gegevensrelaties tussen een veldnaam in de ene tabel en een veldnaam in een andere tabel bestaan, kunt u deze relaties voor uw query gebruiken.

If, for example, you have a spreadsheet for articles identified by an article number, and a spreadsheet for customers in which you record all articles that a customer orders using the corresponding article numbers, then there is a relationship between the two "article number" data fields. If you now want to create a query that returns all articles that a customer has ordered, you must retrieve data from two spreadsheets. To do this, you must inform LibreOfficeDev about the relationship which exists between the data in the two spreadsheets.

To do this, click a field name in a table (for example, the field name "Item-Number" from the Customer table), hold down the mouse button and then drag the field name to the field name of the other table ("Item-Number" from the Item table). When you release the mouse button, a line connecting the two fields between the two table windows appears. The corresponding condition that the content of the two field names must be identical is entered in the resulting SQL query.

Het maken van een query die op verschillende verwante bladen is gebaseerd, is alleen mogelijk als u LibreOfficeDev als de interface voor een relationele database gebruikt.

Notitiepictogram

U kunt geen tabellen uit verschillende databases benaderen in een query. Query's voor meerdere tabellen kunnen alleen binnen dezelfde database worden gemaakt.


Specifying the relation type

If you double-click on the line connecting two linked fields or call the menu command Insert - New Relation, you can specify the type of relation in the Relations dialog.

U kunt ook op de Tab-toets drukken totdat de verbindingsvector is geselecteerd (wordt groter weergegeven). Druk vervolgens op Shift+F10 om het contextmenu te openen en kies de opdracht Bewerken. Sommige databases ondersteunen alleen een subset van de mogelijke join-typen.

Relaties verwijderen

Wanneer u een relatie tussen twee tabellen wilt verwijderen, klikt u op de verbindingslijn en drukt u vervolgens op de Del-toets.

U kunt de items in Betrokken velden ook verwijderen via het dialoogvenster Relaties. Of druk op de Tab-toets totdat de verbindingvector wordt gemarkeerd en druk op Shift+F10 om het contextmenu te openen. Kies vervolgens de opdracht Verwijderen.

Defining the query

Selecteer voorwaarden om de query te definiëren. Elke kolom van de ontwerptabel accepteert een gegevensveld voor de query. De voorwaarden in één rij zijn via een Booleaanse EN gekoppeld.

Specifying field names

First, select all field names from the tables that you want to add to the query. You can do this either by drag-and-drop or by double-clicking a field name in the table window. With the drag-and-drop method, use the mouse to drag a field name from the table window into the lower area of the query design window. As you do this, you can decide which column in the query design window will receive the selected field. A field name can also be selected by double-clicking. It will then be added to the next free column in the query design window.

Velden verwijderen

Als u een veld uit de query wilt verwijderen, klikt u op de kolomkop van het veld en kiest u de opdracht Verwijderen in het contextmenu.

Saving the query

Use the Save icon on the Standard toolbar to save the query. You will see a dialog that asks you to enter a name for the query. If the database supports schemas, you can also enter a schema name.

Schema

Voer de naam in van het schema dat aan de query- of tabelweergave is toegewezen.

Naam query-/tabelweergave

Voer de naam in van de query-/tabelweergave.

Gegevens filteren

To filter data for the query, set the desired criteria in the lower area of the query design window. The following options are available:

Veld

Enter the name of the data field that is referred to in the Query. All settings made in the filter option rows refer to this field. If you activate a cell here with a mouse click you'll see an arrow button, which enables you to select a field. The "Table name.*" option selects all data fields with the effect that the specified criteria will be applied to all table fields.

Alias

Specifies an alias. This alias will be listed in the query instead of the field name. This makes it possible to use user-defined column labels. For example, if the data field is named PtNo and, instead of that name, you would like to have PartNum appear in the query, enter PartNum as the alias.

In a SQL statement, aliases are defined as follows:

SELECT kolom AS alias FROM tabel.

Voorbeeld:

SELECT "ArtikelNr" AS "ArtikelNummer" FROM "Item"

Tabel

The corresponding database table of the selected data field is listed here. If you activate this cell with a mouse click, an arrow will appear which enables you to select a different table for the current query.

Sorteren

If you click on this cell, you can choose a sort option: ascending, descending and unsorted. Text fields will be sorted alphabetically and numerical fields numerically. For most databases, administrators can set the sorting options at the database level.

Zichtbaar

If you mark the Visible property for a data field, that field will be visibly displayed in the resulting query. If you are only using a data field to formulate a condition or make a calculation, you do not necessarily need to display it.

Criteria

Specifies a first criteria by which the content of the data field is to be filtered.

of

Here you can enter one additional filter criterion for each line. Multiple criteria in a single column will be interpreted as boolean OR.

You can also use the context menu of the line headers in the lower area of the query design window to insert a filter based on a function:

Functies

The functions which are available here depend on those provided by the database engine.

If you are working with the embedded HSQL database, the list box in the Function row offers you the following options:

Optie

SQL

Effect

Geen functie

Er wordt geen functie uitgevoerd.

Gemiddelde

AVG

Berekent het rekenkundige gemiddelde van een veld.

Aantal

COUNT

Determines the number of records in the table. Empty fields can either be counted (a) or excluded (b).

a) COUNT(*): Door een asterisk als argument op te nemen, worden alle records in de tabel geteld.

b) COUNT(column): Passing a field name as an argument counts only those records for fields in which the field name in question contains a value. Records with fields having Null values (i.e. fields containing no characters) will not be counted.

Maximum

MAX

Determines the highest value of a record for that field.

Minimum

MIN

Determines the lowest value of a record for that field.

Som

SUM

Calculates the sum of the values of records for the associated fields.

Groeperen

GROUP BY

Groups query data according to the selected field name. Functions are executed according to the specified groups. In SQL, this option corresponds to the GROUP BY clause. If a criterion is added, this entry appears in the SQL HAVING sub-clause.


U kunt ook direct functie-aanroepen in de SQL-instructie invoeren. De syntaxis hiervoor is:

SELECT FUNCTION(kolom) FROM tabel.

De functie-aanroep voor het berekenen van een som is in SQL bijvoorbeeld:

SOM SELECTEREN ("Prijs") VAN "Artikel".

Except for the Group function, the above functions are called Aggregate functions. These are functions that calculate data to create summaries from the results. Additional functions that are not listed in the list box might be also possible. These depend on the specific database engine in use and on the current functionality provided by the Base driver used to connect to that database engine.

To use other functions not listed in the list box, you must enter them manually under Field.

You can also assign aliases to function calls. If you not wish the query string to be displayed in the column header, enter a desired substitute name under Alias.

De corresponderende functie in een SQL-instructie is:

SELECT FUNCTION() AS alias FROM tabel

Voorbeeld:

AANTAL SELECTEREN(*) ALS aantal VAN "Item"

Notitiepictogram

If you run such a function, you cannot insert any additional columns for the query other than as an argument in a "Group" function.


Voorbeelden

In het volgende voorbeeld zal een query worden uitgevoerd op twee tabellen: de tabel met producten met het veld "Product_Nr" en de tabel met leveranciers met het veld "Leverancier_Naam". Tevens bevatten beide tabellen het veld "Leveranciers_Nr."

De volgende stappen moeten worden uitgevoerd om een query met alle leveranciers te maken die meer dan drie items leveren.

  1. Voeg de tabellen Item en Leveranciers aan het query-ontwerp toe.

  2. Koppel de velden "Leverancier_Nr" van de twee tabellen, als er nog geen relatie van dit type is.

  3. Double-click on the "Item_No" field from the "Item" table. Display the Function line using the context menu and select the Count function.

  4. Voer >3 in als een criterium en verwijder het vinkje bij Zichtbaar.

  5. Dubbelklik op het veld "Naam_Leverancier" in de tabel met leveranciers en kies de functie Groeperen.

  6. Voer de query uit.

Als "Prijs" (van een artikel) en "Leveranciersnummer" (de leverancier van het artikel) velden zijn in de tabel Product, kunt u de gemiddelde prijs van een artikel weergeven met de volgende query:

  1. Voeg de tabel Product toe in het query-ontwerp.

  2. Dubbelklik op de velden "Prijs" en "Leveranciersnummer".

  3. Schakel de regel Functie in en selecteer de functie Gemiddelde via het veld "Prijs".

  4. U kunt ook "Gemiddelde" op de regel voor de aliasnaam (zonder aanhalingstekens) invoeren.

  5. Kies Groeperen voor het veld "Leveranciers_Nr".

  6. Voer de query uit.

De volgende contextmenu's en symbolen zijn beschikbaar:

Functies

Shows or hides a row for the selection of functions.

Tabelnaam

Geeft de rij voor de tabelnaam weer of verbergt deze.

Aliasnaam

Toont of verbergt de rij voor de aliasnaam.

Duidelijke waarden

Retrieves only distinct values from the query. This applies to multiple records that might contain several repeating occurrences of data in the selected fields. If the Distinct Values command is active, you should only see one record in the query (DISTINCT). Otherwise, you will see all records corresponding to the query criteria (ALL).

Als de naam "Jansen" bijvoorbeeld meerdere keren voorkomt in de adresdatabase, gebruikt u de opdracht Duidelijke waarden om aan te geven dat de naam Jansen maar één keer in de query mag worden weergegeven.

Wanneer u een query maakt met meerdere velden, moet de combinatie van waarden uit alle velden uniek zijn, zodat het resultaat kan worden gegenereerd op basis van een specifieke record. Stel u hebt een "Jansen in Alkmaar" en een "Jansen in Middelburg". Met de functie Duidelijke waarden zullen zowel "Jansen in Alkmaar" als "Jansen in Middelburg" worden weergegeven. Wanneer er bijvoorbeeld twee keer een "Jansen in Alkmaar" is, wordt hier maar één van weergegeven.

In SQL komt deze opdracht overeen met de opdracht DISTINCT.

Limiet

Allows you to limit the maximum number of records returned by a query.

If a Limit construction is added, you will get at most as many rows as the number you specify. Otherwise, you will see all records corresponding to the query criteria.

Filtervoorwaarden formuleren

When formulating filter conditions, various operators and commands are available to you. Apart from the relational operators, there are SQL-specific commands that query the content of database fields. If you use these commands in the LibreOfficeDev syntax, LibreOfficeDev automatically converts these into the corresponding SQL syntax via an internal parser. You can also enter the SQL command directly and bypass the internal parser. The following tables give an overview of the operators and commands:

Operator

Betekenis

Aan de voorwaarde is voldaan als...

=

gelijk aan

... de inhoud van het veld identiek is aan de opgegeven expressie.

The operator = will not be displayed in the query fields. If you enter a value without any operator, the = operator is automatically assumed.

<>

niet gelijk aan

... de inhoud van het veld niet overeenkomt met de opgegeven expressie.

>

groter dan

... de inhoud van het veld groter is dan de opgegeven waarde.

<

kleiner dan

... de inhoud van het veld kleiner is dan de opgegeven waarde.

>=

groter dan of gelijk aan

... de inhoud van het veld groter is dan of gelijk is aan de opgegeven expressie.

<=

kleiner dan of gelijk aan

... de inhoud van het veld kleiner is dan of gelijk is aan de opgegeven expressie.


LibreOfficeDev-opdracht

SQL-opdracht

Betekenis

Aan de voorwaarde is voldaan als...

IS EMPTY

IS NULL

is null

... the field contains no data. For Yes/No fields with three possible states, this command automatically queries the undetermined state (neither Yes nor No).

IS NOT EMPTY

IS NOT NULL

is niet leeg

... the field is not empty, i.e it contains data.

LIKE

placeholder (*) for any number of characters

placeholder (?) for exactly one character

LIKE

placeholder (%) for any number of characters

Placeholder (_) for exactly one character

is een element van

... the data field must contain the indicated search string in the order given. The position of the (*) placeholder indicates whether the searched for string (x) occurs before the placeholder (x*), after the placeholder (*x) or between the placeholders (*x*). Instead of the familiar file system (*) placeholder, you can also use the SQL (%) character as a placeholder in SQL queries in the LibreOfficeDev interface.

The (*) or (%) placeholder stands for any number of characters. The question mark (?) in the LibreOfficeDev interface or the underscore (_) in SQL queries is used to represent exactly one character.

NOT LIKE

NOT LIKE

Is geen element van

... the field does not contain data having the specified expression.

BETWEEN x AND y

BETWEEN x AND y

valt binnen de interval [x,y]

... the field contains a data value that lies between the two values x and y.

NOT BETWEEN x AND y

NOT BETWEEN x AND y

Valt niet binnen de interval [x,y]

... the field contains a data value that does not lie between the two values x and y.

IN (a; b; c...)

Note that semicolons are used as separators in all value lists!

IN (a, b, c...)

bevat a, b, c...

... the field name contains one of the specified expressions a, b, c,... Any number of expressions can be specified, and the result of the query is determined by a boolean OR operator. The expressions a, b, c... can be either numbers or characters

NOT IN (a; b; c...)

NOT IN (a, b, c...)

bevat niet a, b, c...

... the field does not contain one of the specified expressions a, b, c,...

= TRUE

= TRUE

heeft de waarde WAAR

... het veld heeft de waarde WAAR.

= FALSE

= FALSE

heeft de waarde ONWAAR

... the field data value is set to false.


Voorbeelden

='Ms.'

geeft de velden terug die "Mw." bevatten.

<'2001-01-10'

geeft data terug die vóór 10 januari 2001 voorkomen

LIKE 'g?ve'

returns records with field content such as "give" and "gave".

LIKE 'S*'

returns records with field contents such as "Sun".

BETWEEN 10 AND 20

returns records with field content between the values 10 and 20. (The fields can be either text fields or number fields).

IN (1; 3; 5; 7)

returns records with the values 1, 3, 5, 7. If the field name contains an item number, for example, you can create a query that returns the item having the specified number.

NOT IN ('Smith')

returns records that do not contain "Smith".


Like Escape-reeks: {escape 'escape-character'}

Bijvoorbeeld: select * from Item where Item Name like 'The *%' {escape '*'}

Dit voorbeeld geeft u alle records waarvan de waarden beginnen met 'The *'. Dit betekent dat u ook kunt zoeken op tekens die normaal als jokerteken worden beschouwd, zoals *, ?, _, % of de punt.

Outer Join Escape-reeks: {oj outer-join}

Voorbeeld: select Artikel.* from {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR}

Tekstvelden opvragen

To query the content of a text field, you must put the expression between single quotes. The distinction between uppercase and lowercase letters depends on the database in use. LIKE, by definition, is case-sensitive (though some databases don't interpret this strictly).

Datumvelden opvragen

Datum velden worden weergegeven als #Datum# om ze duidelijk als data te identificeren. Datum, tijd en datum/tijd constanten (letterlijke) die in condities worden gebruikt kunnen van de SQL Escape Syntax type zijn of standaard SQL2 syntax.

Datumtype-element

SQL Escape syntax #1 - kan verouderd zijn

SQL Escape syntax #2

SQL2 syntax

Datum

{D'YYYY-MM-DD'}

{d 'YYYY-MM-DD'}

'YYYY-MM-DD'

Tijd

{D'HH:MM:SS'}

{t 'HH:MI:SS[.SS]'}

'HH:MI:SS[.SS]'

DatumTijd

{D'YYYY-MM-DD HH:MM:SS'}

{ts 'YYYY-MM-DD HH:MI:SS[.SS]'}

'YYYY-MM-DD HH:MI:SS[.SS]'


Bijvoorbeeld: selecteer {d '1999-12-31'} van wereld.jaren

Voorbeeld: selecteer * van mijntabel waar jaren='1999-12-31'

All date expressions (date literals) must be enclosed with single quotation marks. (Consult the reference for the particular database and connector you are using for more details.)

Ja/Nee-velden opvragen

Als u Ja/Nee-velden wilt opvragen, moet u de volgende syntaxis gebruiken voor dBASE-tabellen:

Status

Querycriterium

Voorbeeld

Ja

voor dBASE-tabellen: niet gelijk aan een opgegeven waarde

=1 geeft alle records waarin het Ja/Nee-veld de status "Ja" of "Aan" heeft (geselecteerd in zwart),

Nee

.

=0 retourneert alle records waarvoor het Ja/Nee-veld de status 'Nee' of 'Uit' heeft (geen selectie).

Null

IS NULL

IS NULL geeft alle records waarin het Ja/Nee-veld geen van de waarden Ja of Nee heeft (geselecteerd in grijs).


Notitiepictogram

De syntaxis is afhankelijk van het gebruikte databasesysteem. U moet er ook rekening mee houden dat Ja/Nee-velden anders gedefinieerd kunnen worden (slechts 2 statussen in plaats van 3).


Parameterquery's

Parameter query's maken het voor de gebruiker mogelijk om tijdens de uitvoering waarden in te voeren. Deze waarden worden gebruikt in de criteria voor het selecteren van getoonde records. Ieder van die waarden is geassocieerd met de naam van een parameter, die wordt gebruikt om de gebruiker een invoermelding te geven tijdens het uitvoeren van de query.

Parameter namen worden zowel in de Ontwerpview als in de SQL-view voorafgegaan door een dubbele punt. Als dezelfde waarde meer dan eens in een query voorkomt, wordt dezelfde parameter gebruikt.

In het eenvoudigste geval, als de gebruiker een waarde invoert ter vergelijking, wordt de parameter-naam met de voorafgaande dubbele punt eenvoudig ingevoerd in het criterium Rij. In SQL modus moet dit worden getypt als WHERE "Field" = :Parameter_name

Waarschuwingspictogram

Namen van parameters mogen niet de karakters <space>`!"$%^*()+={}[]@'~#<>?/, bevatten. Ze mogen ook niet dezelfde veldnamen hebben als voor SQL gereserveerde woorden. Ze mogen wel hetzelfde zijn als aliassen.


Tippictogram

Een zinvolle constructie voor het selecteren van records op basis van delen van de inhoud van een tekstveld, is het toevoegen van een verborgen kolom met "LIKE '%' || :Part_of_field || '%'" als het criterium. Dit zal records met een exacte overeenkomst selecteren. Als een hoofdlettergevoelige test gewenst is, is een oplossing het gebruiken van LOWER (Field_Name) als het veld en LIKE LOWER ( '%' || :Part_of_field || '%' ) als het criterium. Merk op dat de spaties in het criterium belangrijk zijn; als ze weg worden gelaten, zal de SQL parser het gehele criterium interpreteren als tekst die gevonden moet worden. In SQL modus zou dit moeten worden getypt als LOWER ( "Field_Name" ) LIKE LOWER ( '%' || :Part_of_field || '%' ).


Parameter query's mogen gebruikt worden als de gegevensbron voor subformulieren, om de gebruiker de mogelijkheid te geven om het aantal weergegeven records te beperken.

Parameter opgeven

Het venster Parameter Imput vraagt de gebruiker om de waarden van de parameter in te voeren. Voer voor iedere query parameter een waarde in en bevestig met OK of het typen van Enter.

De waarden die door de gebruiker worden ingevoerd mogelijk bestaan uit ieder karakter dat is toegestaan voor de SQL van het relevante criterium; dit kan afhankelijk zijn van het onderliggende database-systeem.

Tippictogram

De gebruiker kan gebruik maken van SQL jokertekens "%" (willekeurige tekenreeks) of "_" (willekeurig enkel teken) als deel van de waarden, om records met behulp van meer complexe criteria te verkrijgen.


SQL-modus

SQL staat voor "Structured Query Language" en beschrijft de instructies voor het bijwerken en beheren van relationele databases.

In LibreOfficeDev you do not need any knowledge of SQL for most queries, since you do not have to enter the SQL code. If you create a query in the query designer, LibreOfficeDev automatically converts your instructions into the corresponding SQL syntax. If, with the help of the Switch Design View On/Off button, you change to the SQL view, you can see the SQL commands for a query that has already been created.

You can formulate your query directly in SQL code. Note, however, that the special syntax is dependent upon the database system that you use.

If you enter the SQL code manually, you can create SQL-specific queries that are not supported by the graphical interface in the Query designer. These queries must be executed in native SQL mode.

By clicking the Run SQL command directly icon in the SQL view, you can formulate a query that is not processed by LibreOfficeDevand sent directly to the database engine.