SQL statements

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Overview
SELECT
SELECT
Special case: Multi-file SELECT
Special cases: SELECT without FROM or SELECT using a virtual table
Running a stored procedure
INSERT
INSERT
INSERT with selection
UPDATE
DELETE
CREATE TABLE
Available types
CREATE TABLE AS
DROP TABLE
ALTER TABLE
CREATE INDEX
DROP INDEX
GRANT
REVOKE
CREATE VIEW
DROP VIEW
CREATE MATERIALIZED VIEW
DROP MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

SQL statements

Overview

The main SQL statements allowed in the SQL queries managed by WINDEV and WEBDEV are as follows:

SELECT
INSERT
UPDATE
DELETE
CREATE TABLE
Versions 20 and later
CREATE TABLE AS
New in version 20
CREATE TABLE AS
CREATE TABLE AS
DROP TABLE
Versions 18 and later
ALTER TABLE
New in version 18
ALTER TABLE
ALTER TABLE
Versions 18 and later
CREATE INDEX
New in version 18
CREATE INDEX
CREATE INDEX
Versions 24 and later
DROP INDEX
New in version 24
DROP INDEX
DROP INDEX
Versions 20 and later
GRANT
New in version 20
GRANT
GRANT
Versions 20 and later
REVOKE
New in version 20
REVOKE
REVOKE
Versions 20 and later
CREATE VIEW
New in version 20
CREATE VIEW
CREATE VIEW
Versions 20 and later
DROP VIEW
New in version 20
DROP VIEW
DROP VIEW
Versions 20 and later
CREATE MATERIALIZED VIEW
New in version 20
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
Versions 20 and later
DROP MATERIALIZED VIEW
New in version 20
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW
Versions 20 and later
REFRESH MATERIALIZED VIEW
New in version 20
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW

For more details, see a documentation specific to the SQL language.

To discover all the SQL commands (functions, clauses, operators, etc.) that can be used in an SQL query managed by HFSQL, see Commands that can be used in an SQL query managed by HFSQL.

Remark: From version 19, HFSQL is the new name of HyperFileSQL.

Remarks:

These statements can be used:
- in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
- in the SQL code of queries run by HExecuteSQLQuery.
Versions 15 and later
The " character can be used as delimiter of identifier in the SQL queries as specified by the SQL92 standard.
New in version 15
The " character can be used as delimiter of identifier in the SQL queries as specified by the SQL92 standard.
The " character can be used as delimiter of identifier in the SQL queries as specified by the SQL92 standard.


	Important	The user version of query editor cannot be used to create queries in SQL code using the INSERT, UPDATE or DELETE commands.

SELECT

The SELECT statement is used to find records in one or more files.

Use format:

SELECT [ALL/DISTINCT] NomDesRubriques AS NomDesRubriquesUtilisé
FROM NomDesFichiers AS NomDesFichiersUtilisé
[WHERE JointuresFichiers [AND] ConditionsSélection]
[GROUP BY NomDesRubriques [HAVING Condition]]
[ORDER BY NomDesRubriques]

Example: The following SQL code is used to display the total sum of orders per customer living in Paris. The result will be grouped by customer name, by zip code and by city. Only the customers with a total amount of orders exceeding 3000 Dollars will be considered. The result will be sorted by total order amount in ascending order:

SELECT CLIENT.NomClient AS NomClient,

CLIENT.CodePostal AS CodePostal,

CLIENT.Ville AS Ville,

SUM(COMMANDE.TotalTTC) AS TotalTTC

FROM CLIENT AS CLI, COMMANDE AS COM

WHERE CLI.NumClient = COM.NumClient

AND CodePostal LIKE '21%'

GROUP BY NomClient, CodePostal, Ville

HAVING TotalTTC > 3000

ORDER BY TotalTTC ASC

Remarks:

The DISTINCT keyword is used to delete the duplicate records from the result of the query.
For more details, see Selection conditions in an SQL query on selection conditions.
A Select query can be created in the query editor. For more details, see Creating a select query.
Versions 19 and later
In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:

SELECT NomClient, Age>18 AS Majeur FROM Client

New in version 19
In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:

SELECT NomClient, Age>18 AS Majeur FROM Client

In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:

SELECT NomClient, Age>18 AS Majeur FROM Client

Versions 20 and later

The SELECT statement can be used as a scalar expression, which means that the SELECT statement can be used instead a value or a column in the statements such as INSERT, UPDATE, SELECT, etc.
For example:

INSERT INTO TABLE (nom, valeur) VALUES ( 'Rome a Rick',
(SELECT MAX(nombre) FROM table2) )

UPDATE client SET cattc = (SELECT SUM(facture.total_ttc)
FROM facture WHERE facture.reference=client.reference)

SELECT NomDesRubriques AS NomDesRubriquesUtilisé,
(SELECT COUNT(*) FROM Table2 WHERE Rub=NomDesRubriquesUtilisé)
FROM Table1

Remark: The SELECT query used as scalar expression must return a result on a single column and a single row.

New in version 20

The SELECT statement can be used as a scalar expression, which means that the SELECT statement can be used instead a value or a column in the statements such as INSERT, UPDATE, SELECT, etc.
For example:

INSERT INTO TABLE (nom, valeur) VALUES ( 'Rome a Rick',
(SELECT MAX(nombre) FROM table2) )

UPDATE client SET cattc = (SELECT SUM(facture.total_ttc)
FROM facture WHERE facture.reference=client.reference)

SELECT NomDesRubriques AS NomDesRubriquesUtilisé,
(SELECT COUNT(*) FROM Table2 WHERE Rub=NomDesRubriquesUtilisé)
FROM Table1

Remark: The SELECT query used as scalar expression must return a result on a single column and a single row.

The SELECT statement can be used as a scalar expression, which means that the SELECT statement can be used instead a value or a column in the statements such as INSERT, UPDATE, SELECT, etc.
For example:

INSERT INTO TABLE (nom, valeur) VALUES ( 'Rome a Rick',
(SELECT MAX(nombre) FROM table2) )

UPDATE client SET cattc = (SELECT SUM(facture.total_ttc)
FROM facture WHERE facture.reference=client.reference)

SELECT NomDesRubriques AS NomDesRubriquesUtilisé,
(SELECT COUNT(*) FROM Table2 WHERE Rub=NomDesRubriquesUtilisé)
FROM Table1

Remark: The SELECT query used as scalar expression must return a result on a single column and a single row.

Versions 16 and later

Special case: Multi-file SELECT

The SELECT statement can also be used to select in a multi-file query all the columns of a data file via the following syntax:

SELECT Ligne_Commande.*,Commande.NumCommande, ...

FROM Commande, Ligne_Commande

This syntax is very useful for the joins and it avoids entering the name of each item, which is a long and fastidious operation.

New in version 16

Special case: Multi-file SELECT

The SELECT statement can also be used to select in a multi-file query all the columns of a data file via the following syntax:

SELECT Ligne_Commande.*,Commande.NumCommande, ...

FROM Commande, Ligne_Commande

This syntax is very useful for the joins and it avoids entering the name of each item, which is a long and fastidious operation.

Special case: Multi-file SELECT

The SELECT statement can also be used to select in a multi-file query all the columns of a data file via the following syntax:

SELECT Ligne_Commande.*,Commande.NumCommande, ...

FROM Commande, Ligne_Commande

This syntax is very useful for the joins and it avoids entering the name of each item, which is a long and fastidious operation.

Versions 19 and later

Special cases: SELECT without FROM or SELECT using a virtual table

The SELECT statement can also be used to run queries that do not apply to tables:

by using the syntax without "FROM" clause.
Example:

SELECT TO_CHAR(12,'RN')
by using the virtual DUAL table. This virtual table contains a single item (named "dummy") and a single record.
Example:

SELECT CURRENT_DATE FROM DUAL

New in version 19

Special cases: SELECT without FROM or SELECT using a virtual table

The SELECT statement can also be used to run queries that do not apply to tables:

by using the syntax without "FROM" clause.
Example:

SELECT TO_CHAR(12,'RN')
by using the virtual DUAL table. This virtual table contains a single item (named "dummy") and a single record.
Example:

SELECT CURRENT_DATE FROM DUAL

Special cases: SELECT without FROM or SELECT using a virtual table

The SELECT statement can also be used to run queries that do not apply to tables:

by using the syntax without "FROM" clause.
Example:

SELECT TO_CHAR(12,'RN')
by using the virtual DUAL table. This virtual table contains a single item (named "dummy") and a single record.
Example:

SELECT CURRENT_DATE FROM DUAL

Running a stored procedure

The SELECT statement can also be used to run stored procedures directly.

The following syntax is used to run the stored procedure:

SELECT NomDeLaProcédureStockée(ParamètreNumérique,'ParamètreChaîne',....) [FROM DUAL]

Remarks:

The stored procedure must be a stored function: it must always return a value. Otherwise, a runtime error will occur.
If the name of the stored procedure is identical to the name of a function or procedure found in the project, its name must be prefixed by the name of the set of stored procedures.
The result of a stored procedure can also be one of the parameters of a SELECT query.

For more details, see Stored procedures and queries (HFSQL Client/Server).

INSERT

The INSERT statement is used to add :

a record into a data file.
Versions 20 and later
several records in a data file.
New in version 20
several records in a data file.
several records in a data file.

It uses the following format:

INSERT INTO NomDuFichier (NomDesRubriques)
VALUES (ValeurRubriques ajout 1), (ValeurRubriques ajout 2), ... , (ValeurRubriques ajout N)

Examples: The following SQL code lets you insert:

a new customer in the CUSTOMER file (the complete customer information is provided).

INSERT INTO CLIENT
VALUES (77, 'Martin', 'Julie', '1 rue Giono',...
'69000', 'Lyon')
a new customer in the CUSTOMER file: only the customer's number, last name and first name are provided:

INSERT INTO CLIENT (NumClient, NomClient, PrénomClient)
VALUES (77, 'Martin', 'Julie')
Versions 20 and later
3 products in a PRODUCT file:

INSERT INTO PRODUIT
VALUES ('Ref01', 'Eau minérale'), ('Ref02', 'Beurre'), ('Ref03', 'Eponge')

New in version 20
3 products in a PRODUCT file:

INSERT INTO PRODUIT
VALUES ('Ref01', 'Eau minérale'), ('Ref02', 'Beurre'), ('Ref03', 'Eponge')

3 products in a PRODUCT file:

INSERT INTO PRODUIT
VALUES ('Ref01', 'Eau minérale'), ('Ref02', 'Beurre'), ('Ref03', 'Eponge')

Remarks:

An Insert query can be created in the query editor. For more details, see Creating an insert query.
To find out the value of the automatic identifier in an INSERT query on HFSQL files (query run by HExecuteSQLQuery, HExecuteQuery or SQLExec), use the SQL.IdAuto variable.
The INSERT command cannot be used to copy data from an HFSQL file into another HFSQL file.
The H.NbRecModificationQuery variable is used to find out the number of inserted records.

INSERT with selection

The INSERT statement can also be used to insert a selection of records made by a SELECT query. Its use format becomes:

INSERT INTO NomDuFichier [(NomDesRubriques)] SELECT ...

Remarks:

The number of items to insert must be identical to the number of values returned by the SELECT query.
All the files items are taken into account if no item is specified in the INSERT statement.
This type of query cannot be created in the query editor.

UPDATE

The UPDATE statement is used to update the records in a file. It uses the following format:

UPDATE NomDuFichier
SET NomDeLaRubrique = Expression
[WHERE Conditions]

Examples:

The following SQL code is used to modify the title and the name of the customer named Montgomery:

UPDATE CLIENT
SET Civilité = 'Mme',
NomClient = 'Darwin'
WHERE NomClient = 'Montgomery'
The following SQL code is used to modify the name of the Client file with the name of the Client_Svg file for records that have the same identifier:

UPDATE CLIENT, CLIENT_SVG
SET CLIENT.Nom = CLIENT_SVG.Nom
WHERE CLIENT.ID = CLIENT_SVG.ID

Notes regarding the use of the UPDATE statement in WINDEV:

The WHERE clause accepts the sub-selections (by SELECT), if the sub-selection is relative to another file.
The SET expression accepts no sub-selection (no SELECT command just after SET). Similarly, no join can be performed in the UPDATE section of the query.
The following query is accepted:

UPDATE ARTICLE SET PrixVente = 1000
WHERE RefArticle IN (SELECT COMMANDE.RefArticle FROM COMMANDE)
-- Sous-requête dans le WHERE: OK

The following query is not accepted:

UPDATE Article
SET PrixVente = (SELECT AVG(COMMANDE.PrixUnit) FROM COMMANDE
-- Sous-requête dans le SET: NON VALIDE
WHERE COMMANDE.RefArticle = ARTICLE.RefArticle
GROUP BY COMMANDE.RefArticle)
WHERE RefArticle IN (SELECT COMMANDE.RefArticle FROM COMMANDE)

Similarly, the following query is not accepted:

UPDATE Article INNER JOIN PRODUIT ON (Article.IdArticle = Produit.IdProduit)
-- Jointure dans le UPDATE: NON VALIDE
SET Article.PrixVente = Produit.PrixVente
WHERE RefArticle IN (SELECT COMMANDE.RefArticle FROM COMMANDE)
An Update query can be directly created in the query editor. For more details, see Creating a Change Request.
The quote is used as delimiter for the strings in the SQL queries. If the value to write must contain a quote, this value must be preceded by the backslash character ('\'). For example:

UPDATE CLIENT
SET Adresse = 'Rue de l\'écluse'
WHERE NomClient = 'Montgomery'
The H.NbRecModificationQuery variable is used to find out the number of updated records.

DELETE

The DELETE statement is used to delete records from a file. It uses the following format:

DELETE FROM NomDuFichier
WHERE Conditions

Example: The following SQL code is used to delete the customers born between 01/01/1971 and 01/01/1975:

DELETE FROM CLIENT

WHERE CLIENT.DateNaissance BETWEEN '19710101' AND '19750101'

Remark: A Delete query can be created in the query editor. For more details, see Creating a Delete Request.

CREATE TABLE

The CREATE TABLE statement is used to describe a table and to create it. It uses the following format:

CREATE TABLE NomTable
(
Description de la rubrique1,
Description de la rubrique2,
...
Description Index1,
Description Index2,
...
)

In this syntax:

the "Description of item" parameters correspond to:

NomRubrique type [DEFAULT 'valeur' | DEFAULT NULL] [NOT NULL]
[UNIQUE] [PRIMARY KEY] [AUTO_INCREMENT] [COMMENT 'libellé'] [COLLATE 'langue']

where:
- ItemName: name that will be given to the item.
- Type: Type of the item (chosen among the available types presented below).
- DEFAULT: is used to define the default value of the item.
- Versions 16 and later
  NOT NULL: is used to prevent from assigning the NULL value to the item.
  New in version 16
  NOT NULL: is used to prevent from assigning the NULL value to the item.
  NOT NULL: is used to prevent from assigning the NULL value to the item.
- AUTO_INCREMENT: is used to define an automatic identifier for the integer items.
- COMMENT: is used to define the caption of the item.
- COLLATE: is used to define the language for sorting the item (for example: 'FR_BE').
the "Description of index" parameters correspond to:

INDEX NomIndex ( rub1 [ASC | DESC] [CI] [AI] [PI], rub2 ... ) [UNIQUE]

where:
- ASC and DESC: allows you to define the sorting direction.
- CI: is used to specify that the case is ignored.
- AI: is used to specify that the accented characters are ignored.
- PI: is used to specify that the punctuation is ignored.

Remark: This type of query cannot be created with the wizard found in the query editor.

Available types

The available types are:


CHARACTER	Character string
CHARACTER(Size)	String on size
VARCHAR(Size)	String on size
Versions 19 and later CHARACTER VARYING(Size) New in version 19 CHARACTER VARYING(Size) CHARACTER VARYING(Size)	String on size
Versions 19 and later CHAR VARYING(Size) New in version 19 CHAR VARYING(Size) CHAR VARYING(Size)	String on size
NVARCHAR(Size)	Unicode string on size
VARCHAR(Size) BINARY	Binary string on size
BINARY(Size)	Binary string on size
VARBINARY(Size)	Binary string on size
BLOB	Binary Memo
CLOB	Text Memo
TEXT	Text Memo
NCLOB	Unicode memo
NTEXT	Unicode memo
NUMBER(Precision)	entier
NUMBER(Precision, scale)	entier
DECIMAL(Precision)	Real
DECIMAL(Precision, scale)	Real
TINYINT UNSIGNED	Unsigned 1-byte integer
SMALLINT UNSIGNED	Unsigned 2-byte integer
INTEGER UNSIGNED	Unsigned 4-byte integer
BIGINT UNSIGNED	Unsigned 8-byte integer
TINYINT	Signed 1-byte integer
SMALLINT	Signed 2-byte integer
INTEGER	Signed 4-byte integer
BIGINT	Signed 8-byte integer
FLOAT	4-byte real
REAL	8-byte real
DOUBLE PRECISION	8-byte real
MONEY	Currency
DATE	DATE
DATETIME	Date time
TIME	Time of day

CREATE TABLE AS

Versions 20 and later
The CREATE TABLE AS statement is used to describe a table and to create it from the result of a select query. The table columns have the names and types of data associated with the columns after the SELECT query has been run.

It uses the following format:

CREATE TABLE NomTable AS RequêteSélection [WITH NO DATA]

In this syntax:

SelectQuery is a SELECT query. The query is run and the table is created from the result of this Select query.
The "WITH NO DATA" option is used to create the table structure only (without the data found in the result of the Select query).

Caution: The index of the created table may differ from the one of the source table.

Remark: This type of query cannot be created with the wizard found in the query editor.

New in version 20
The CREATE TABLE AS statement is used to describe a table and to create it from the result of a select query. The table columns have the names and types of data associated with the columns after the SELECT query has been run.

It uses the following format:

CREATE TABLE NomTable AS RequêteSélection [WITH NO DATA]

In this syntax:

SelectQuery is a SELECT query. The query is run and the table is created from the result of this Select query.
The "WITH NO DATA" option is used to create the table structure only (without the data found in the result of the Select query).

Caution: The index of the created table may differ from the one of the source table.

Remark: This type of query cannot be created with the wizard found in the query editor.

The CREATE TABLE AS statement is used to describe a table and to create it from the result of a select query. The table columns have the names and types of data associated with the columns after the SELECT query has been run.

It uses the following format:

CREATE TABLE NomTable AS RequêteSélection [WITH NO DATA]

In this syntax:

SelectQuery is a SELECT query. The query is run and the table is created from the result of this Select query.
The "WITH NO DATA" option is used to create the table structure only (without the data found in the result of the Select query).

Caution: The index of the created table may differ from the one of the source table.

Remark: This type of query cannot be created with the wizard found in the query editor.

DROP TABLE

The DROP TABLE statement is used to physically delete a table. It uses the following format:

DROP TABLE [ IF EXISTS ] name [, ...]

Remarks:

IF EXISTS is used to avoid returning an error if the table does not exist.
This type of query cannot be created with the wizard found in the query editor.

ALTER TABLE

Versions 18 and later
The ALTER TABLE statement is used to change the structure of an existing table. You have the ability to add or delete columns and indexes, to change the type of the existing columns, to rename these columns or the table itself. It uses the following format:

ALTER TABLE NomTable Action [, Action] ....

In this syntax:

the "Action" parameters correspond to one of the following actions:

ADD [COLUMN] Description de rubrique
ADD [COLUMN] (Description de rubrique1 [,Description de rubrique2]....)
DROP [COLUMN] [IF EXISTS] NomRubrique
DROP [COLUMN] [IF EXISTS] (NomRubrique1 [, NomRubrique2]...)
ALTER [COLUMN] NomRubrique [SET DATA] TYPE alter_type_desc
ALTER [COLUMN] NomRubrique SET DEFAULT <valeur>
ALTER [COLUMN] NomRubrique DROP DEFAULT
ADD [UNIQUE] INDEX [<NomIndex>] (Description d'index [, Description d'index2]...)
DROP INDEX [IF EXISTS] NomIndex
DROP INDEX [IF EXISTS] (NomIndex1 [, NomIndex2]...)
RENAME COLUMN NomRubrique TO Nouveau_NomRubrique

The "Description of item" parameters correspond to:

NomRubrique type [DEFAULT 'valeur' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY]
[AUTO_INCREMENT] [COMMENT 'libellé'] [COLLATE 'langue']

where:
- ItemName: name that will be given to the item.
- Type: Type of the item (chosen among the above-mentioned types).
- DEFAULT: is used to define the default value of the item. This value by Default cannot be NULL.
- NOT NULL: is used to prevent from assigning the NULL value to the item.
- AUTO_INCREMENT: is used to define an automatic identifier for the integer items.
- COMMENT: is used to define the caption of the item.
- COLLATE: is used to define the language for sorting the item (for example: 'FR_BE').
the "Description of index" parameters correspond to:

NomRubrique [ASC | DESC] [CI] [AI] [PI]

where:
- ASC and DESC: allows you to define the sorting direction.
- CI: is used to specify that the case is ignored.
- AI: is used to specify that the accented characters are ignored.
- PI: is used to specify that the punctuation is ignored.

Remarks:

This type of query cannot be created with the wizard found in the query editor.
To use the modified table during the ALTER (without project-related analysis), the HDeclareExternal function must be used after the ALTER TABLE query is executed.

New in version 18
The ALTER TABLE statement is used to change the structure of an existing table. You have the ability to add or delete columns and indexes, to change the type of the existing columns, to rename these columns or the table itself. It uses the following format:

ALTER TABLE NomTable Action [, Action] ....

In this syntax:

the "Action" parameters correspond to one of the following actions:

ADD [COLUMN] Description de rubrique
ADD [COLUMN] (Description de rubrique1 [,Description de rubrique2]....)
DROP [COLUMN] [IF EXISTS] NomRubrique
DROP [COLUMN] [IF EXISTS] (NomRubrique1 [, NomRubrique2]...)
ALTER [COLUMN] NomRubrique [SET DATA] TYPE alter_type_desc
ALTER [COLUMN] NomRubrique SET DEFAULT <valeur>
ALTER [COLUMN] NomRubrique DROP DEFAULT
ADD [UNIQUE] INDEX [<NomIndex>] (Description d'index [, Description d'index2]...)
DROP INDEX [IF EXISTS] NomIndex
DROP INDEX [IF EXISTS] (NomIndex1 [, NomIndex2]...)
RENAME COLUMN NomRubrique TO Nouveau_NomRubrique

The "Description of item" parameters correspond to:

NomRubrique type [DEFAULT 'valeur' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY]
[AUTO_INCREMENT] [COMMENT 'libellé'] [COLLATE 'langue']

where:
- ItemName: name that will be given to the item.
- Type: Type of the item (chosen among the above-mentioned types).
- DEFAULT: is used to define the default value of the item. This value by Default cannot be NULL.
- NOT NULL: is used to prevent from assigning the NULL value to the item.
- AUTO_INCREMENT: is used to define an automatic identifier for the integer items.
- COMMENT: is used to define the caption of the item.
- COLLATE: is used to define the language for sorting the item (for example: 'FR_BE').
the "Description of index" parameters correspond to:

NomRubrique [ASC | DESC] [CI] [AI] [PI]

where:
- ASC and DESC: allows you to define the sorting direction.
- CI: is used to specify that the case is ignored.
- AI: is used to specify that the accented characters are ignored.
- PI: is used to specify that the punctuation is ignored.

Remarks:

This type of query cannot be created with the wizard found in the query editor.
To use the modified table during the ALTER (without project-related analysis), the HDeclareExternal function must be used after the ALTER TABLE query is executed.

The ALTER TABLE statement is used to change the structure of an existing table. You have the ability to add or delete columns and indexes, to change the type of the existing columns, to rename these columns or the table itself. It uses the following format:

ALTER TABLE NomTable Action [, Action] ....

In this syntax:

the "Action" parameters correspond to one of the following actions:

ADD [COLUMN] Description de rubrique
ADD [COLUMN] (Description de rubrique1 [,Description de rubrique2]....)
DROP [COLUMN] [IF EXISTS] NomRubrique
DROP [COLUMN] [IF EXISTS] (NomRubrique1 [, NomRubrique2]...)
ALTER [COLUMN] NomRubrique [SET DATA] TYPE alter_type_desc
ALTER [COLUMN] NomRubrique SET DEFAULT <valeur>
ALTER [COLUMN] NomRubrique DROP DEFAULT
ADD [UNIQUE] INDEX [<NomIndex>] (Description d'index [, Description d'index2]...)
DROP INDEX [IF EXISTS] NomIndex
DROP INDEX [IF EXISTS] (NomIndex1 [, NomIndex2]...)
RENAME COLUMN NomRubrique TO Nouveau_NomRubrique

The "Description of item" parameters correspond to:

NomRubrique type [DEFAULT 'valeur' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY]
[AUTO_INCREMENT] [COMMENT 'libellé'] [COLLATE 'langue']

where:
- ItemName: name that will be given to the item.
- Type: Type of the item (chosen among the above-mentioned types).
- DEFAULT: is used to define the default value of the item. This value by Default cannot be NULL.
- NOT NULL: is used to prevent from assigning the NULL value to the item.
- AUTO_INCREMENT: is used to define an automatic identifier for the integer items.
- COMMENT: is used to define the caption of the item.
- COLLATE: is used to define the language for sorting the item (for example: 'FR_BE').
the "Description of index" parameters correspond to:

NomRubrique [ASC | DESC] [CI] [AI] [PI]

where:
- ASC and DESC: allows you to define the sorting direction.
- CI: is used to specify that the case is ignored.
- AI: is used to specify that the accented characters are ignored.
- PI: is used to specify that the punctuation is ignored.

Remarks:

This type of query cannot be created with the wizard found in the query editor.
To use the modified table during the ALTER (without project-related analysis), the HDeclareExternal function must be used after the ALTER TABLE query is executed.

CREATE INDEX

Versions 18 and later
The CREATE INDEX statement (equivalent to the ALTER TABLE statement) is used to create indexes. This statement is used to add indexes to an existing table. It uses the following format:

CREATE [UNIQUE] INDEX [NomIndex] ON NomTable (Description d'index [, Description d'index]... )

In this code, the "Description of index" parameters correspond to:

NomRubrique [ASC | DESC] [CI] [AI] [PI]

where:

ASC and DESC: allows you to define the sorting direction.
CI: is used to specify that the case is ignored.
AI: is used to specify that the accented characters are ignored.
PI: is used to specify that the punctuation is ignored.

Remark: This type of query cannot be created with the wizard found in the query editor.

Versions 20 and later
You have the ability to use the CREATE INDEX statement on a materialized view. In this case, the TableName parameter corresponds to the name of a materialized view. If the view is refreshed, the indexes are automatically updated: there is no need to re-create the indexes.

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

New in version 20
You have the ability to use the CREATE INDEX statement on a materialized view. In this case, the TableName parameter corresponds to the name of a materialized view. If the view is refreshed, the indexes are automatically updated: there is no need to re-create the indexes.

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

You have the ability to use the CREATE INDEX statement on a materialized view. In this case, the TableName parameter corresponds to the name of a materialized view. If the view is refreshed, the indexes are automatically updated: there is no need to re-create the indexes.

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

New in version 18
The CREATE INDEX statement (equivalent to the ALTER TABLE statement) is used to create indexes. This statement is used to add indexes to an existing table. It uses the following format:

CREATE [UNIQUE] INDEX [NomIndex] ON NomTable (Description d'index [, Description d'index]... )

In this code, the "Description of index" parameters correspond to:

NomRubrique [ASC | DESC] [CI] [AI] [PI]

where:

ASC and DESC: allows you to define the sorting direction.
CI: is used to specify that the case is ignored.
AI: is used to specify that the accented characters are ignored.
PI: is used to specify that the punctuation is ignored.

Remark: This type of query cannot be created with the wizard found in the query editor.

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

The CREATE INDEX statement (equivalent to the ALTER TABLE statement) is used to create indexes. This statement is used to add indexes to an existing table. It uses the following format:

CREATE [UNIQUE] INDEX [NomIndex] ON NomTable (Description d'index [, Description d'index]... )

In this code, the "Description of index" parameters correspond to:

NomRubrique [ASC | DESC] [CI] [AI] [PI]

where:

ASC and DESC: allows you to define the sorting direction.
CI: is used to specify that the case is ignored.
AI: is used to specify that the accented characters are ignored.
PI: is used to specify that the punctuation is ignored.

Remark: This type of query cannot be created with the wizard found in the query editor.

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

Example: Creating a materialized view and a composite key on this view.

-- Création d'une vue matérialisée

CREATE MATERIALIZED VIEW MaVueMaterialisee AS 

 SELECT    

      Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte 

  FROM Client  

  WHERE Client.Solde>0 AND Client.Type=2  

  AND Client.Famille IN ('A', 'D', 'O')  

  GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent; 

 --';' pour pouvoir mettre une autre instruction SQL après

 --Avec une clé composée

CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,Aff ASC CI AI PI,TypeAdherent ASC)

DROP INDEX

Versions 24 and later
The DROP INDEX statement is used to physically delete an index from a table. It uses the following format:

DROP INDEX [ IF EXISTS ] NomIndex ON NomTable

where IF EXISTS: prevents returning an error if the index does not exist.

Remark: This type of query cannot be created with the wizard found in the query editor.

New in version 24
The DROP INDEX statement is used to physically delete an index from a table. It uses the following format:

DROP INDEX [ IF EXISTS ] NomIndex ON NomTable

where IF EXISTS: prevents returning an error if the index does not exist.

Remark: This type of query cannot be created with the wizard found in the query editor.

The DROP INDEX statement is used to physically delete an index from a table. It uses the following format:

DROP INDEX [ IF EXISTS ] NomIndex ON NomTable

where IF EXISTS: prevents returning an error if the index does not exist.

Remark: This type of query cannot be created with the wizard found in the query editor.

GRANT

Versions 20 and later
The GRANT queries are used to grant rights on an HFSQL server. This statement is equivalent to setting the element as hAllowed. It uses the following format:

Grant the requested rights on the specified tables to the specified users:

GRANT droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
TO utilisateur [, utilisateur [, ...]]
Grant the requested rights on the specified databases to the specified users:.

GRANT droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
TO utilisateur [, utilisateur [, ...]]
Grant the requested rights on the server.

GRANT droit[, droit [, ... ]]
TO utilisateur [, utilisateur [, ...]]

Remark:

This type of query cannot be created with the wizard found in the query editor.
These queries cannot be used on an HFSQL Classic database, nor on a database accessed via Native Connectors.

Available rights
The available rights are:


SQL right	HFSQL right
INSERT	hRightsInsert
LOCK	hRightsLock
MANAGE RIGHTS	hRightsChangeRights
OWNER	hRightsChangeOwner
MANAGE DUPLICATE	hRightsManageDuplicate
SELECT	hRightsRead
MAINTENANCE	hRightsMaintenance
ALTER	hRightsAutoModif
UPDATE	hRightsUpdate
DELETE	hRightsDelete
DELETE FILE	hRightsDeleteFile
REFERENCES	hRightsChangeLink
CONNECT	hRightsConnection
ENCRYPTED CONNECT	hRightsEncryptedConnection
CREATE	hRightsCreateFile
DEBUG	hRightsDebug
RUN PROCEDURE	hRightsRunProcedure
MANAGE REFERENCES	hRightsManageIntegrity
MANAGE PROCEDURE	hRightsManageProcedure
MANAGE TRIGGER	hRightsManageTrigger
FORBID ACCESS	hRightsNoDatabaseAccess
BACKUP	hRightsBackup
DELETE DATABASE	hRightsDeleteDB
STOP	hRightsStopServer
CHANGE PASSWORD	hRightsChangePassword
CREATE DATABASE	hRightsCreateDB
DISCONNECT	hRightsDisconnectClient
SEND MESSAGE	hRightsSendMessageToClient
MANAGE TASK	hRightsManageTask
MANAGE SERVER	hRightsManageServer
MANAGE USER	hRightsManageUser
READ LOG	hRightsReadLogStat
PRIORITY	hRightsPriority
REPLICATE	hRightsServerReplication
SEE USER	hRightsSeeUser

New in version 20
The GRANT queries are used to grant rights on an HFSQL server. This statement is equivalent to setting the element as hAllowed. It uses the following format:

Grant the requested rights on the specified tables to the specified users:

GRANT droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
TO utilisateur [, utilisateur [, ...]]
Grant the requested rights on the specified databases to the specified users:.

GRANT droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
TO utilisateur [, utilisateur [, ...]]
Grant the requested rights on the server.

GRANT droit[, droit [, ... ]]
TO utilisateur [, utilisateur [, ...]]

Remark:

This type of query cannot be created with the wizard found in the query editor.
These queries cannot be used on an HFSQL Classic database, nor on a database accessed via Native Connectors.

Available rights
The available rights are:


SQL right	HFSQL right
INSERT	hRightsInsert
LOCK	hRightsLock
MANAGE RIGHTS	hRightsChangeRights
OWNER	hRightsChangeOwner
MANAGE DUPLICATE	hRightsManageDuplicate
SELECT	hRightsRead
MAINTENANCE	hRightsMaintenance
ALTER	hRightsAutoModif
UPDATE	hRightsUpdate
DELETE	hRightsDelete
DELETE FILE	hRightsDeleteFile
REFERENCES	hRightsChangeLink
CONNECT	hRightsConnection
ENCRYPTED CONNECT	hRightsEncryptedConnection
CREATE	hRightsCreateFile
DEBUG	hRightsDebug
RUN PROCEDURE	hRightsRunProcedure
MANAGE REFERENCES	hRightsManageIntegrity
MANAGE PROCEDURE	hRightsManageProcedure
MANAGE TRIGGER	hRightsManageTrigger
FORBID ACCESS	hRightsNoDatabaseAccess
BACKUP	hRightsBackup
DELETE DATABASE	hRightsDeleteDB
STOP	hRightsStopServer
CHANGE PASSWORD	hRightsChangePassword
CREATE DATABASE	hRightsCreateDB
DISCONNECT	hRightsDisconnectClient
SEND MESSAGE	hRightsSendMessageToClient
MANAGE TASK	hRightsManageTask
MANAGE SERVER	hRightsManageServer
MANAGE USER	hRightsManageUser
READ LOG	hRightsReadLogStat
PRIORITY	hRightsPriority
REPLICATE	hRightsServerReplication
SEE USER	hRightsSeeUser

The GRANT queries are used to grant rights on an HFSQL server. This statement is equivalent to setting the element as hAllowed. It uses the following format:

Grant the requested rights on the specified tables to the specified users:

GRANT droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
TO utilisateur [, utilisateur [, ...]]
Grant the requested rights on the specified databases to the specified users:.

GRANT droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
TO utilisateur [, utilisateur [, ...]]
Grant the requested rights on the server.

GRANT droit[, droit [, ... ]]
TO utilisateur [, utilisateur [, ...]]

Remark:

This type of query cannot be created with the wizard found in the query editor.
These queries cannot be used on an HFSQL Classic database, nor on a database accessed via Native Connectors.

Available rights
The available rights are:


SQL right	HFSQL right
INSERT	hRightsInsert
LOCK	hRightsLock
MANAGE RIGHTS	hRightsChangeRights
OWNER	hRightsChangeOwner
MANAGE DUPLICATE	hRightsManageDuplicate
SELECT	hRightsRead
MAINTENANCE	hRightsMaintenance
ALTER	hRightsAutoModif
UPDATE	hRightsUpdate
DELETE	hRightsDelete
DELETE FILE	hRightsDeleteFile
REFERENCES	hRightsChangeLink
CONNECT	hRightsConnection
ENCRYPTED CONNECT	hRightsEncryptedConnection
CREATE	hRightsCreateFile
DEBUG	hRightsDebug
RUN PROCEDURE	hRightsRunProcedure
MANAGE REFERENCES	hRightsManageIntegrity
MANAGE PROCEDURE	hRightsManageProcedure
MANAGE TRIGGER	hRightsManageTrigger
FORBID ACCESS	hRightsNoDatabaseAccess
BACKUP	hRightsBackup
DELETE DATABASE	hRightsDeleteDB
STOP	hRightsStopServer
CHANGE PASSWORD	hRightsChangePassword
CREATE DATABASE	hRightsCreateDB
DISCONNECT	hRightsDisconnectClient
SEND MESSAGE	hRightsSendMessageToClient
MANAGE TASK	hRightsManageTask
MANAGE SERVER	hRightsManageServer
MANAGE USER	hRightsManageUser
READ LOG	hRightsReadLogStat
PRIORITY	hRightsPriority
REPLICATE	hRightsServerReplication
SEE USER	hRightsSeeUser

REVOKE

Versions 20 and later
The REVOKE queries are used to remove rights (switch the rights to hInherit) on an HFSQL server. It uses the following format:

Set the requested rights as inherited on the specified tables for the specified users.

REVOKE droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
FROM utilisateur [, utilisateur [, ...]]
Set the requested rights as inherited on the specified databases for the specified users.

REVOKE droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
FROM utilisateur [, utilisateur [, ...]]
Set the requested rights as inherited on the server.

REVOKE droit[, droit [, ... ]] FROM utilisateur [, utilisateur [, ...]]

Remark

This type of query cannot be created with the wizard found in the query editor.
These queries do not operate in HFSQL Classic or via Native Connectors.

Available rights: The available rights are identical to the SQL GRANT command. The table of rights is available with the GRANT command.

New in version 20
The REVOKE queries are used to remove rights (switch the rights to hInherit) on an HFSQL server. It uses the following format:

Set the requested rights as inherited on the specified tables for the specified users.

REVOKE droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
FROM utilisateur [, utilisateur [, ...]]
Set the requested rights as inherited on the specified databases for the specified users.

REVOKE droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
FROM utilisateur [, utilisateur [, ...]]
Set the requested rights as inherited on the server.

REVOKE droit[, droit [, ... ]] FROM utilisateur [, utilisateur [, ...]]

Remark

This type of query cannot be created with the wizard found in the query editor.
These queries do not operate in HFSQL Classic or via Native Connectors.

Available rights: The available rights are identical to the SQL GRANT command. The table of rights is available with the GRANT command.

The REVOKE queries are used to remove rights (switch the rights to hInherit) on an HFSQL server. It uses the following format:

Set the requested rights as inherited on the specified tables for the specified users.

REVOKE droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
FROM utilisateur [, utilisateur [, ...]]
Set the requested rights as inherited on the specified databases for the specified users.

REVOKE droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
FROM utilisateur [, utilisateur [, ...]]
Set the requested rights as inherited on the server.

REVOKE droit[, droit [, ... ]] FROM utilisateur [, utilisateur [, ...]]

Remark

This type of query cannot be created with the wizard found in the query editor.
These queries do not operate in HFSQL Classic or via Native Connectors.

Available rights: The available rights are identical to the SQL GRANT command. The table of rights is available with the GRANT command.

CREATE VIEW

Versions 20 and later
CREATE VIEW is used to create an SQL view. SQL views are equivalent to temporary files in memory. In most cases, an SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.

For more details, see SQL view on SQL views.

It uses the following format:

CREATE VIEW Vue [ ( Alias 1, Alias 2, ..., Alias N) ] AS RequêteSELECT

In this syntax:

View is the name of the view.
Alias 1, Alias 2, Alias N represent the alias names given to the items issued from the SELECT query.
SELECTQuery is the SELECT query used to select the records found in the view.

SQL example: Creating an SQL view containing the company, the name and the phone of customers:

CREATE VIEW V_Clients

AS SELECT Société, Nom, Téléphone FROM Clients

New in version 20
CREATE VIEW is used to create an SQL view. SQL views are equivalent to temporary files in memory. In most cases, an SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.

For more details, see SQL view on SQL views.

It uses the following format:

CREATE VIEW Vue [ ( Alias 1, Alias 2, ..., Alias N) ] AS RequêteSELECT

In this syntax:

View is the name of the view.
Alias 1, Alias 2, Alias N represent the alias names given to the items issued from the SELECT query.
SELECTQuery is the SELECT query used to select the records found in the view.

SQL example: Creating an SQL view containing the company, the name and the phone of customers:

CREATE VIEW V_Clients

AS SELECT Société, Nom, Téléphone FROM Clients

CREATE VIEW is used to create an SQL view. SQL views are equivalent to temporary files in memory. In most cases, an SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.

For more details, see SQL view on SQL views.

It uses the following format:

CREATE VIEW Vue [ ( Alias 1, Alias 2, ..., Alias N) ] AS RequêteSELECT

In this syntax:

View is the name of the view.
Alias 1, Alias 2, Alias N represent the alias names given to the items issued from the SELECT query.
SELECTQuery is the SELECT query used to select the records found in the view.

SQL example: Creating an SQL view containing the company, the name and the phone of customers:

CREATE VIEW V_Clients

AS SELECT Société, Nom, Téléphone FROM Clients

DROP VIEW

Versions 20 and later
DROP VIEW is used to delete a view.

DROP VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE]

If the IF EXISTS keyword is specified and if the views do not exist, an error message is generated.
View 1, View 2, ... View N represent the list of views to delete.
If the RESTRICT keyword is specified, the view is not deleted if it is currently used.
If the CASCADE keyword is specified, the view is deleted as well as the dependent views.

New in version 20
DROP VIEW is used to delete a view.

DROP VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE]

If the IF EXISTS keyword is specified and if the views do not exist, an error message is generated.
View 1, View 2, ... View N represent the list of views to delete.
If the RESTRICT keyword is specified, the view is not deleted if it is currently used.
If the CASCADE keyword is specified, the view is deleted as well as the dependent views.

DROP VIEW is used to delete a view.

DROP VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE]

If the IF EXISTS keyword is specified and if the views do not exist, an error message is generated.
View 1, View 2, ... View N represent the list of views to delete.
If the RESTRICT keyword is specified, the view is not deleted if it is currently used.
If the CASCADE keyword is specified, the view is deleted as well as the dependent views.

CREATE MATERIALIZED VIEW

Versions 20 and later
A materialized view is a persistent view: a file is created on disk from the content of the view. This view can be handled later by an application other than the one that created it.

CREATE MATERIALIZED VIEW is used to create a materialized view.

CREATE MATERIALIZED VIEW Vue [ ( Alias 1, Alias 2, ...Alias N) ]
AS instruction SELECT [WITH [ NO ] DATA]

The WITH DATA keyword creates a view with data.
The WITH NO DATA keyword creates an empty view without data.

Remark

This type of query cannot be created with the wizard found in the query editor.
These queries cannot operate in HFSQL Classic or with Native Connectors.

New in version 20
A materialized view is a persistent view: a file is created on disk from the content of the view. This view can be handled later by an application other than the one that created it.

CREATE MATERIALIZED VIEW is used to create a materialized view.

CREATE MATERIALIZED VIEW Vue [ ( Alias 1, Alias 2, ...Alias N) ]
AS instruction SELECT [WITH [ NO ] DATA]

The WITH DATA keyword creates a view with data.
The WITH NO DATA keyword creates an empty view without data.

Remark

This type of query cannot be created with the wizard found in the query editor.
These queries cannot operate in HFSQL Classic or with Native Connectors.

A materialized view is a persistent view: a file is created on disk from the content of the view. This view can be handled later by an application other than the one that created it.

CREATE MATERIALIZED VIEW is used to create a materialized view.

CREATE MATERIALIZED VIEW Vue [ ( Alias 1, Alias 2, ...Alias N) ]
AS instruction SELECT [WITH [ NO ] DATA]

The WITH DATA keyword creates a view with data.
The WITH NO DATA keyword creates an empty view without data.

Remark

This type of query cannot be created with the wizard found in the query editor.
These queries cannot operate in HFSQL Classic or with Native Connectors.

DROP MATERIALIZED VIEW

Versions 20 and later
DROP MATERIALIZED VIEW is used to physically delete a materialized view from the disk.

DROP MATERIALIZED VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE]

If the IF EXISTS keyword is specified and if the views do not exist, an error message is generated.
View 1, View 2, ... View N represent the list of views to delete.
If the RESTRICT keyword is specified, the view is not deleted if it is currently used.
If the CASCADE keyword is specified, the view is deleted as well as the dependent views.

New in version 20
DROP MATERIALIZED VIEW is used to physically delete a materialized view from the disk.

DROP MATERIALIZED VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE]

If the IF EXISTS keyword is specified and if the views do not exist, an error message is generated.
View 1, View 2, ... View N represent the list of views to delete.
If the RESTRICT keyword is specified, the view is not deleted if it is currently used.
If the CASCADE keyword is specified, the view is deleted as well as the dependent views.

DROP MATERIALIZED VIEW is used to physically delete a materialized view from the disk.

DROP MATERIALIZED VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE]

If the IF EXISTS keyword is specified and if the views do not exist, an error message is generated.
View 1, View 2, ... View N represent the list of views to delete.
If the RESTRICT keyword is specified, the view is not deleted if it is currently used.
If the CASCADE keyword is specified, the view is deleted as well as the dependent views.

REFRESH MATERIALIZED VIEW

Versions 20 and later
REFRESH MATERIALIZED VIEW is used to refresh the content of a materialized view on disk. The view is entirely recalculated.

REFRESH MATERIALIZED VIEW Vue [WITH [ NO ] DATA]

The WITH DATA keyword refreshes the view with the new data.
The WITH NO DATA keyword refreshes the empty view without data.

New in version 20
REFRESH MATERIALIZED VIEW is used to refresh the content of a materialized view on disk. The view is entirely recalculated.

REFRESH MATERIALIZED VIEW Vue [WITH [ NO ] DATA]

The WITH DATA keyword refreshes the view with the new data.
The WITH NO DATA keyword refreshes the empty view without data.

REFRESH MATERIALIZED VIEW is used to refresh the content of a materialized view on disk. The view is entirely recalculated.

REFRESH MATERIALIZED VIEW Vue [WITH [ NO ] DATA]

The WITH DATA keyword refreshes the view with the new data.
The WITH NO DATA keyword refreshes the empty view without data.