PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • 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
  • GRANT
  • Available rights
  • REVOKE
  • CREATE VIEW
  • DROP VIEW
  • CREATE MATERIALIZED VIEW
  • DROP MATERIALIZED VIEW
  • REFRESH MATERIALIZED VIEW
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Overview
The main SQL statements allowed in the SQL queries managed by WINDEV and WEBDEV are as follows:
See a documentation specific to the SQL language for more details.
To find out all the SQL commands (functions, clauses, operators, ...) that can be used in a SQL query managed by HFSQL, see Commands that can be used in an SQL query managed by HFSQL.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Notes:
  • 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

SELECT

The SELECT statement is used to find records in one or more files.
Use format:
SELECT [ALL/DISTINCT] NamesOfItems AS NameOfItemsUsed
FROM FileName AS UsedFileName
[WHERE FileJoins [AND] SelectionConditions]
[GROUP BY NameOfItems [HAVING Condition]]
[ORDER BY NameOfItems]
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 Euros will be considered. The result will be sorted by total order amount in ascending order:
SELECT CUSTOMER.CustomerLName AS CustomerLName,
CUSTOMER.ZipCode AS ZipCode,
CUSTOMER.City AS City,
SUM(ORDERS.TotalIOT) AS TotalIOT
FROM CUSTOMER AS CUST, ORDERS AS ORD
WHERE CUST.CustNum = ORD.CustNum
AND ZipCode LIKE '75%'
GROUP BY CustomerLName, ZipCode, City
HAVING TotalIOT > 3000
ORDER BY TotalIOT ASC
Notes:
  • The DISTINCT keyword is used to delete the duplicate records from the result of the query.
  • See Selection conditions in a SQL query for more details.
  • A Select query can be created in the query editor. See Creating a select query for more details.
  • Versions 19 and later
    In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:
    SELECT CustomerName, Age>18 AS Adult FROM Customer
    New in version 19
    In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:
    SELECT CustomerName, Age>18 AS Adult FROM Customer
    In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:
    SELECT CustomerName, Age>18 AS Adult FROM Customer
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 (NAME, value) VALUES ( 'Rome a Rick',
    (SELECT MAX(NUMBER) FROM table2) )

    UPDATE customer SET gdttl = (SELECT SUM(invoice.grand_total)
    FROM invoice WHERE invoice.reference=customer.reference)

    SELECT NamesOfItems AS NameOfItemsUsed,
    (SELECT COUNT(*) FROM Table2 WHERE Item=NameOfItemsUsed)
    FROM Table1
Note: 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 (NAME, value) VALUES ( 'Rome a Rick',
    (SELECT MAX(NUMBER) FROM table2) )

    UPDATE customer SET gdttl = (SELECT SUM(invoice.grand_total)
    FROM invoice WHERE invoice.reference=customer.reference)

    SELECT NamesOfItems AS NameOfItemsUsed,
    (SELECT COUNT(*) FROM Table2 WHERE Item=NameOfItemsUsed)
    FROM Table1
Note: 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 (NAME, value) VALUES ( 'Rome a Rick',
    (SELECT MAX(NUMBER) FROM table2) )

    UPDATE customer SET gdttl = (SELECT SUM(invoice.grand_total)
    FROM invoice WHERE invoice.reference=customer.reference)

    SELECT NamesOfItems AS NameOfItemsUsed,
    (SELECT COUNT(*) FROM Table2 WHERE Item=NameOfItemsUsed)
    FROM Table1
Note: 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 Order_Line.*,Orders.OrderNum, ...
FROM Orders, Order_Line
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 Order_Line.*,Orders.OrderNum, ...
FROM Orders, Order_Line
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 Order_Line.*,Orders.OrderNum, ...
FROM Orders, Order_Line
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 NameOfStoredProcedure(NumericParameter,'StringParameter',....) [FROM DUAL]
Notes:
  • 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.
INSERT

INSERT

The INSERT statement is used to add a record into a data file. It is using the following format:
INSERT INTO FileName (NameOfItems)
VALUES (ValueOfItems)
Example: The following SQL codes are used to insert a new customer into the CUSTOMER file:
  • all the customer details are provided:
    INSERT INTO CUSTOMER
    VALUES (77, 'Moore', 'Julia', '1 rue Giono',...
    '69000', 'Lyon')
  • only the number, the name name and the first name of the customer are supplied:
    INSERT INTO CUSTOMER (CustNum, CustomerLName, CustomerFName)
    VALUES (77, 'Moore', 'Julia')
Notes:
  • In WINDEV, the INSERT statement is single-tuple: It only adds one record at a time. The following query is not accepted:
    INSERT INTO CUSTOMER VALUES
    (1, 'Doe', 'John')
    (2, 'Wolff', 'Walter')
    (3, 'Black', 'Roger')
  • An Insert query can be created in the query editor. See Creating an insert query for more details.
  • To find out the value of the automatic identifier during an INSERT query on HFSQL files (query run by HExecuteSQLQuery, HExecuteQuery or SQLExec): the SQL.Idauto variable is used to find out the value of the identifier.
  • The INSERT command cannot be used to copy data from a HFSQL file into another HFSQL file.
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 is using the following format:
INSERT INTO FileName (NameOfItems)
VALUES (ValueItems add 1), (ValueItems add 2), ... , (ValueItems add N)
Examples: The following SQL code lets you insert:
  • a new customer in the CUSTOMER file (the complete customer information is provided).
    INSERT INTO CUSTOMER
    VALUES (77, 'Moore', 'Julia', '1 rue Giono',...
    '69000', 'Lyon')
  • a new customer in the CUSTOMER file: only the number, the name name and the first name of the customer are supplied:
    INSERT INTO CUSTOMER (CustNum, CustomerLName, CustomerFName)
    VALUES (77, 'Moore', 'Julia')
  • Versions 20 and later
    3 products in a PRODUCT file:
    INSERT INTO PRODUCT
    VALUES ('Ref01', 'Mineral water'), ('Ref02', 'Butter'), ('Ref03', 'Sponge')
    New in version 20
    3 products in a PRODUCT file:
    INSERT INTO PRODUCT
    VALUES ('Ref01', 'Mineral water'), ('Ref02', 'Butter'), ('Ref03', 'Sponge')
    3 products in a PRODUCT file:
    INSERT INTO PRODUCT
    VALUES ('Ref01', 'Mineral water'), ('Ref02', 'Butter'), ('Ref03', 'Sponge')
Notes:
  • An Insert query can be created in the query editor. See Creating an insert query for more details.
  • To find out the value of the automatic identifier during an INSERT query on HFSQL files (query run by HExecuteSQLQuery, HExecuteQuery or SQLExec): the SQL.Idauto variable is used to find out the value of the identifier.
  • The INSERT command cannot be used to copy data from a 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 FileName [(NameOfItems)] SELECT...
Notes:
  • 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 is using the following format:
UPDATE FileName
SET NameOfItem = Expression
[WHERE Conditions]
Example: The following SQL code is used to modify the title and the name of the customer named Montgomery:
UPDATE CUSTOMER
SET Title = 'Mrs',
CustomerLName = 'Darwin'
WHERE CustomerLName = 'Montgomery'
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 PRODUCT SET SalesPrice = 1000
    WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS)
    -- Sub-query in WHERE: OK

    The following query is not accepted:
    UPDATE Product
    SET SalesPrice = (SELECT AVG(ORDERS.UnitPrice) FROM ORDERS
    -- Sub-query in SET: NOT VALID
    WHERE ORDERS.ProductRef = PRODUCT.ProductRef
    GROUP BY ORDERS.ProductRef)
    WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS)

    Similarly, the following query is not accepted:
    UPDATE Article INNER JOIN PRODUCT ON (Article.ArticleID = Product.ProductID)
    -- Join in UPDATE: NOT VALID
    SET Article.SalesPrice = Product.SalesPrice
    WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS)
  • An Update query can be directly created in the query editor. See Creating an update query for more details.
  • 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 CUSTOMER
    SET Address = 'Rue de l\'écluse'
    WHERE CustomerLName = '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 is using the following format:
DELETE FROM FileName
WHERE Conditions
Example: The following SQL code is used to delete the customers born between 01/01/1971 and 01/01/1975:
DELETE FROM CUSTOMER
WHERE CUSTOMER.DateOfBirth BETWEEN '19710101' AND '19750101'
Note: A Delete query can be created in the query editor. See Creating a delete query for more details.
CREATE TABLE
The CREATE TABLE statement is used to describe a table and to create it. It is using the following format:
CREATE TABLE TableName
(
Description of item1,
Description of item2,
...
Description Index1,
Description Index2,
...
)
In this syntax:
  • the "Description of item" parameters correspond to:
    ItemName type  [DEFAULT 'value' | DEFAULT NULL] [NOT NULL]
      [UNIQUE] [PRIMARY KEY]  [AUTO_INCREMENT] [COMMENT 'caption'] [COLLATE 'language']
    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 IndexName ( itm1 [ASC | DESC] [CI] [AI] [PI], itm2 ... ) [UNIQUE]
    where:
    • ASC and DESC: is used to define the sort 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.
Note: This type of query cannot be created with the wizard found in the query editor.

Available types

The available types are:
CHARACTERCharacter 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) BINARYBinary string on size
BINARY(Size)Binary string on size
VARBINARY(Size)Binary string on size
BLOBBinary memo
CLOBText memo
TEXTText memo
NCLOBUnicode memo
NTEXTUnicode memo
NUMBER(Precision)Integer
NUMBER(Precision, scale)Integer
DECIMAL(Precision)Real
DECIMAL(Precision, scale)Real
TINYINT UNSIGNEDUnsigned 1-byte integer
SMALLINT UNSIGNEDUnsigned 2-byte integer
INTEGER UNSIGNEDUnsigned 4-byte integer
BIGINT UNSIGNEDUnsigned 8-byte integer
TINYINTSigned 1-byte integer
SMALLINTSigned 2-byte integer
INTEGERSigned 4-byte integer
BIGINTSigned 8-byte integer
FLOAT4-byte real
REAL8-byte real
DOUBLE PRECISION8-byte real
MONEYCurrency
DATEDATE
DATETIMEDate time
TIMETime
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 the types of data associated with the columns in exit of the SELECT query.
It is using the following format:
CREATE TABLE TableName AS SelectQuery [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.
Note: 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 the types of data associated with the columns in exit of the SELECT query.
It is using the following format:
CREATE TABLE TableName AS SelectQuery [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.
Note: 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 the types of data associated with the columns in exit of the SELECT query.
It is using the following format:
CREATE TABLE TableName AS SelectQuery [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.
Note: 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 is using the following format:
DROP TABLE [ IF EXISTS ] name [, ...]
Notes:
  • 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 is using the following format:
ALTER TABLE TableName Action [, Action]....
In this syntax:
  • the "Action" parameters correspond to one of the following actions:
    ADD [COLUMN] Description of item
    ADD [COLUMN] (Description of item1 [,Description of item2]....)
    DROP [COLUMN] [IF EXISTS] ItemName
    DROP [COLUMN] [IF EXISTS] (NameItem1 [, NameItem2]...)
    ALTER [COLUMN] ItemName [SET DATA] TYPE alter_type_desc
    ALTER [COLUMN] ItemName SET DEFAULT <value>
    ALTER [COLUMN] ItemName DROP DEFAULT
    ADD [UNIQUE] INDEX [<IndexName>] (Description of index [, Description of index2]...)
    DROP INDEX [IF EXISTS] IndexName
    DROP INDEX [IF EXISTS] (NameIndex1 [, NameIndex2]...)
    RENAME COLUMN ItemName TO New_ItemName
  • The "Description of item" parameters correspond to:
    ItemName type  [DEFAULT 'value' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY]
     [AUTO_INCREMENT] [COMMENT 'caption'] [COLLATE 'language']
    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.
    • 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:
    ItemName [ASC | DESC] [CI] [AI] [PI]
    where:
    • ASC and DESC: is used to define the sort 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.
Notes:
  • This type of query cannot be created with the wizard found in the query editor.
  • To use the table modified during ALTER (without analysis linked to the project), HDeclareExternal must be used after the execution of the ALTER TABLE query.
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 is using the following format:
ALTER TABLE TableName Action [, Action]....
In this syntax:
  • the "Action" parameters correspond to one of the following actions:
    ADD [COLUMN] Description of item
    ADD [COLUMN] (Description of item1 [,Description of item2]....)
    DROP [COLUMN] [IF EXISTS] ItemName
    DROP [COLUMN] [IF EXISTS] (NameItem1 [, NameItem2]...)
    ALTER [COLUMN] ItemName [SET DATA] TYPE alter_type_desc
    ALTER [COLUMN] ItemName SET DEFAULT <value>
    ALTER [COLUMN] ItemName DROP DEFAULT
    ADD [UNIQUE] INDEX [<IndexName>] (Description of index [, Description of index2]...)
    DROP INDEX [IF EXISTS] IndexName
    DROP INDEX [IF EXISTS] (NameIndex1 [, NameIndex2]...)
    RENAME COLUMN ItemName TO New_ItemName
  • The "Description of item" parameters correspond to:
    ItemName type  [DEFAULT 'value' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY]
     [AUTO_INCREMENT] [COMMENT 'caption'] [COLLATE 'language']
    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.
    • 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:
    ItemName [ASC | DESC] [CI] [AI] [PI]
    where:
    • ASC and DESC: is used to define the sort 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.
Notes:
  • This type of query cannot be created with the wizard found in the query editor.
  • To use the table modified during ALTER (without analysis linked to the project), HDeclareExternal must be used after the execution of the ALTER TABLE query.
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 is using the following format:
ALTER TABLE TableName Action [, Action]....
In this syntax:
  • the "Action" parameters correspond to one of the following actions:
    ADD [COLUMN] Description of item
    ADD [COLUMN] (Description of item1 [,Description of item2]....)
    DROP [COLUMN] [IF EXISTS] ItemName
    DROP [COLUMN] [IF EXISTS] (NameItem1 [, NameItem2]...)
    ALTER [COLUMN] ItemName [SET DATA] TYPE alter_type_desc
    ALTER [COLUMN] ItemName SET DEFAULT <value>
    ALTER [COLUMN] ItemName DROP DEFAULT
    ADD [UNIQUE] INDEX [<IndexName>] (Description of index [, Description of index2]...)
    DROP INDEX [IF EXISTS] IndexName
    DROP INDEX [IF EXISTS] (NameIndex1 [, NameIndex2]...)
    RENAME COLUMN ItemName TO New_ItemName
  • The "Description of item" parameters correspond to:
    ItemName type  [DEFAULT 'value' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY]
     [AUTO_INCREMENT] [COMMENT 'caption'] [COLLATE 'language']
    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.
    • 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:
    ItemName [ASC | DESC] [CI] [AI] [PI]
    where:
    • ASC and DESC: is used to define the sort 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.
Notes:
  • This type of query cannot be created with the wizard found in the query editor.
  • To use the table modified during ALTER (without analysis linked to the project), HDeclareExternal must be used after the execution of the ALTER TABLE query.
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 is using the following format:
CREATE [UNIQUE] INDEX [IndexName] ON TableName (Description of index [, Description of index]... )
In this code, the "Description of index" parameters correspond to:
ItemName [ASC | DESC] [CI] [AI] [PI]
where:
  • ASC and DESC: is used to define the sort 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.
Note: 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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 is using the following format:
CREATE [UNIQUE] INDEX [IndexName] ON TableName (Description of index [, Description of index]... )
In this code, the "Description of index" parameters correspond to:
ItemName [ASC | DESC] [CI] [AI] [PI]
where:
  • ASC and DESC: is used to define the sort 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.
Note: 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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 is using the following format:
CREATE [UNIQUE] INDEX [IndexName] ON TableName (Description of index [, Description of index]... )
In this code, the "Description of index" parameters correspond to:
ItemName [ASC | DESC] [CI] [AI] [PI]
where:
  • ASC and DESC: is used to define the sort 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.
Note: 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType 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.
-- Creating a materialized view
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
--';' to be able to use another SQL statement after
--With a composite key
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,Disp ASC CI AI PI,MemberType ASC)
GRANT
Versions 20 and later
The GRANT queries are used to grant rights on a HFSQL server. This statement consists in granting thehAuhorized rights to the element. It is using the following format:
  • Grant the requested rights on the specified tables to the specified users:
    GRANT right[, right[, ... ]] ON [TABLE] table [, table [, ...]]  
    TO user [, user [, ...]]
  • Grant the requested rights on the specified databases to the specified users:.
    GRANT right[, right[, ... ]] ON DATABASE database [, database [, ...]]
    TO user [, user [, ...]]
  • Grant the requested rights on the server.
    GRANT right[, right[, ... ]]
    TO user [, user [, ...]]
Note:
  • This type of query cannot be created with the wizard found in the query editor.
  • These queries cannot be used on a HFSQL Classic database, nor on a database accessed via native access.

Available rights

The available rights are:
SQL rightHFSQL right
INSERThRightsInsert
LOCKhRightsLock
MANAGE RIGHTShRightsChangeRights
OWNERhRightsChangeOwner
MANAGE DUPLICATEhRightsManageDuplicate
SELECThRightsRead
MAINTENANCEhRightsMaintenance
ALTERhRightsAutoModif
UPDATEhRightsUpdate
DELETEhRightsDelete
DELETE FILEhRightsDeleteFile
REFERENCEShRightsChangeLink
CONNECThRightsConnection
ENCRYPTED CONNECThRightsEncryptedConnection
CREATEhRightsCreateFile
DEBUGhRightsDebug
RUN PROCEDUREhRightsRunProcedure
MANAGE REFERENCEShRightsManageIntegrity
MANAGE PROCEDUREhRightsManageProcedure
MANAGE TRIGGERhRightsManageTrigger
FORBID ACCESShRightsNoDatabaseAccess
BACKUPhRightsBackup
DELETE DATABASEhRightsDeleteDB
STOPhRightsStopServer
CHANGE PASSWORDhRightsChangePassword
CREATE DATABASEhRightsCreateDB
DISCONNECThRightsDisconnectClient
SEND MESSAGEhRightsSendMessageToClient
MANAGE TASKhRightsManageTask
MANAGE SERVERhRightsManageServer
MANAGE USERhRightsManageUser
READ LOGhRightsReadLogStat
PRIORITYhRightsPriority
REPLICATEhRightsServerReplication
SEE USERhRightsSeeUser
New in version 20
The GRANT queries are used to grant rights on a HFSQL server. This statement consists in granting thehAuhorized rights to the element. It is using the following format:
  • Grant the requested rights on the specified tables to the specified users:
    GRANT right[, right[, ... ]] ON [TABLE] table [, table [, ...]]  
    TO user [, user [, ...]]
  • Grant the requested rights on the specified databases to the specified users:.
    GRANT right[, right[, ... ]] ON DATABASE database [, database [, ...]]
    TO user [, user [, ...]]
  • Grant the requested rights on the server.
    GRANT right[, right[, ... ]]
    TO user [, user [, ...]]
Note:
  • This type of query cannot be created with the wizard found in the query editor.
  • These queries cannot be used on a HFSQL Classic database, nor on a database accessed via native access.

Available rights

The available rights are:
SQL rightHFSQL right
INSERThRightsInsert
LOCKhRightsLock
MANAGE RIGHTShRightsChangeRights
OWNERhRightsChangeOwner
MANAGE DUPLICATEhRightsManageDuplicate
SELECThRightsRead
MAINTENANCEhRightsMaintenance
ALTERhRightsAutoModif
UPDATEhRightsUpdate
DELETEhRightsDelete
DELETE FILEhRightsDeleteFile
REFERENCEShRightsChangeLink
CONNECThRightsConnection
ENCRYPTED CONNECThRightsEncryptedConnection
CREATEhRightsCreateFile
DEBUGhRightsDebug
RUN PROCEDUREhRightsRunProcedure
MANAGE REFERENCEShRightsManageIntegrity
MANAGE PROCEDUREhRightsManageProcedure
MANAGE TRIGGERhRightsManageTrigger
FORBID ACCESShRightsNoDatabaseAccess
BACKUPhRightsBackup
DELETE DATABASEhRightsDeleteDB
STOPhRightsStopServer
CHANGE PASSWORDhRightsChangePassword
CREATE DATABASEhRightsCreateDB
DISCONNECThRightsDisconnectClient
SEND MESSAGEhRightsSendMessageToClient
MANAGE TASKhRightsManageTask
MANAGE SERVERhRightsManageServer
MANAGE USERhRightsManageUser
READ LOGhRightsReadLogStat
PRIORITYhRightsPriority
REPLICATEhRightsServerReplication
SEE USERhRightsSeeUser
The GRANT queries are used to grant rights on a HFSQL server. This statement consists in granting thehAuhorized rights to the element. It is using the following format:
  • Grant the requested rights on the specified tables to the specified users:
    GRANT right[, right[, ... ]] ON [TABLE] table [, table [, ...]]  
    TO user [, user [, ...]]
  • Grant the requested rights on the specified databases to the specified users:.
    GRANT right[, right[, ... ]] ON DATABASE database [, database [, ...]]
    TO user [, user [, ...]]
  • Grant the requested rights on the server.
    GRANT right[, right[, ... ]]
    TO user [, user [, ...]]
Note:
  • This type of query cannot be created with the wizard found in the query editor.
  • These queries cannot be used on a HFSQL Classic database, nor on a database accessed via native access.

Available rights

The available rights are:
SQL rightHFSQL right
INSERThRightsInsert
LOCKhRightsLock
MANAGE RIGHTShRightsChangeRights
OWNERhRightsChangeOwner
MANAGE DUPLICATEhRightsManageDuplicate
SELECThRightsRead
MAINTENANCEhRightsMaintenance
ALTERhRightsAutoModif
UPDATEhRightsUpdate
DELETEhRightsDelete
DELETE FILEhRightsDeleteFile
REFERENCEShRightsChangeLink
CONNECThRightsConnection
ENCRYPTED CONNECThRightsEncryptedConnection
CREATEhRightsCreateFile
DEBUGhRightsDebug
RUN PROCEDUREhRightsRunProcedure
MANAGE REFERENCEShRightsManageIntegrity
MANAGE PROCEDUREhRightsManageProcedure
MANAGE TRIGGERhRightsManageTrigger
FORBID ACCESShRightsNoDatabaseAccess
BACKUPhRightsBackup
DELETE DATABASEhRightsDeleteDB
STOPhRightsStopServer
CHANGE PASSWORDhRightsChangePassword
CREATE DATABASEhRightsCreateDB
DISCONNECThRightsDisconnectClient
SEND MESSAGEhRightsSendMessageToClient
MANAGE TASKhRightsManageTask
MANAGE SERVERhRightsManageServer
MANAGE USERhRightsManageUser
READ LOGhRightsReadLogStat
PRIORITYhRightsPriority
REPLICATEhRightsServerReplication
SEE USERhRightsSeeUser
REVOKE
Versions 20 and later
The REVOKE queries are used to remove rights (switch the rights to hInherit) on a HFSQL server. It is using the following format:
  • Set the requested rights as inherited on the specified tables for the specified users.
    REVOKE right[, right [, ... ]] ON [TABLE] table [, table [, ...]]  
    FROM user [, user [, ...]]
  • Set the requested rights as inherited on the specified databases for the specified users.
    REVOKE right[, right [, ... ]] ON DATABASE database [, database [, ...]]
    FROM user [, user [, ...]]
  • Set the requested rights as inherited on the server.
    REVOKE right[, right [, ... ]] FROM user [, user [, ...]]
Note
  • 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 the Native Accesses.
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 a HFSQL server. It is using the following format:
  • Set the requested rights as inherited on the specified tables for the specified users.
    REVOKE right[, right [, ... ]] ON [TABLE] table [, table [, ...]]  
    FROM user [, user [, ...]]
  • Set the requested rights as inherited on the specified databases for the specified users.
    REVOKE right[, right [, ... ]] ON DATABASE database [, database [, ...]]
    FROM user [, user [, ...]]
  • Set the requested rights as inherited on the server.
    REVOKE right[, right [, ... ]] FROM user [, user [, ...]]
Note
  • 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 the Native Accesses.
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 a HFSQL server. It is using the following format:
  • Set the requested rights as inherited on the specified tables for the specified users.
    REVOKE right[, right [, ... ]] ON [TABLE] table [, table [, ...]]  
    FROM user [, user [, ...]]
  • Set the requested rights as inherited on the specified databases for the specified users.
    REVOKE right[, right [, ... ]] ON DATABASE database [, database [, ...]]
    FROM user [, user [, ...]]
  • Set the requested rights as inherited on the server.
    REVOKE right[, right [, ... ]] FROM user [, user [, ...]]
Note
  • 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 the Native Accesses.
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 a SQL view. The SQL views are assimilated to temporary files in memory. In most cases, a SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.
See SQL view for more details.
It is using the following format:
CREATE VIEW View [ ( Alias 1, Alias 2, ..., Alias N) ] AS SELECTQuery
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 a SQL view containing the company, the name and the phone of customers:
CREATE VIEW V_Customers
AS SELECT Company, NAME, Phone FROM Customers
New in version 20
CREATE VIEW is used to create a SQL view. The SQL views are assimilated to temporary files in memory. In most cases, a SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.
See SQL view for more details.
It is using the following format:
CREATE VIEW View [ ( Alias 1, Alias 2, ..., Alias N) ] AS SELECTQuery
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 a SQL view containing the company, the name and the phone of customers:
CREATE VIEW V_Customers
AS SELECT Company, NAME, Phone FROM Customers
CREATE VIEW is used to create a SQL view. The SQL views are assimilated to temporary files in memory. In most cases, a SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.
See SQL view for more details.
It is using the following format:
CREATE VIEW View [ ( Alias 1, Alias 2, ..., Alias N) ] AS SELECTQuery
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 a SQL view containing the company, the name and the phone of customers:
CREATE VIEW V_Customers
AS SELECT Company, NAME, Phone FROM Customers
DROP VIEW
Versions 20 and later
DROP VIEW is used to delete a view.
DROP VIEW [IF EXISTS] View 1 [, View 2, ...View 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] View 1 [, View 2, ...View 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] View 1 [, View 2, ...View 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 View [ ( Alias 1, Alias 2, ...Alias N) ]
AS SELECT statement [WITH [ NO ] DATA]
  • The WITH DATA keyword creates a view with data.
  • The WITH NO DATA keyword creates an empty view without data.
Note
  • This type of query cannot be created with the wizard found in the query editor.
  • These queries may not operate in HFSQL Classic or with the Native Accesses.
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 View [ ( Alias 1, Alias 2, ...Alias N) ]
AS SELECT statement [WITH [ NO ] DATA]
  • The WITH DATA keyword creates a view with data.
  • The WITH NO DATA keyword creates an empty view without data.
Note
  • This type of query cannot be created with the wizard found in the query editor.
  • These queries may not operate in HFSQL Classic or with the Native Accesses.
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 View [ ( Alias 1, Alias 2, ...Alias N) ]
AS SELECT statement [WITH [ NO ] DATA]
  • The WITH DATA keyword creates a view with data.
  • The WITH NO DATA keyword creates an empty view without data.
Note
  • This type of query cannot be created with the wizard found in the query editor.
  • These queries may not operate in HFSQL Classic or with the Native Accesses.
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] View 1 [, View 2, ...View 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] View 1 [, View 2, ...View 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] View 1 [, View 2, ...View 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 View [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 View [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 View [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.
Minimum required version
  • Version 9
This page is also available for…
Comments
Colocar em Ordem Crescente/Decrescente
Nessa dica de hoje,
Vou Ensinar como colocar
Em ordem Crescente e Decrescente
//=============
In this tip today,
I will teach how to put
In ascending and descending order
//=============
Dans cette astuce aujourd'hui,
Je vais apprendre à mettre
Dans l'ordre croissant et décroissant
//-----colocar em ordem descrescente
gs_ordem_consulta="desc"
mostra_cli(gn_classificacao)
ReturnToCapture(EDT_PROCURA)
// ordem crescente
gs_ordem_consulta is string="asc"
mostra_cli(gn_classificacao)
//===============
http://windevdesenvolvimento.blogspot.com.br/2017/03/aula-1083-windev-tabela-050-tabela.html

https://www.youtube.com/watch?v=45JUSTthT0I

De matos
Mar. 10 2017
Exemplo Drop Table - Eliminar Tabela
IF YesNo("Deseja Eliminar Tabela Municipio") THEN
ds_DS_DATA_SOURCE is Data Source
s_SQL is string=""
s_SQL = [
DROP TABLE municipios
]
IF HExecuteSQLQuery(ds_DS_DATA_SOURCE,hQueryDefault,s_SQL) THEN
Error("Erro Query " + CR + HErrorInfo())
ELSE
Info("Tabela Municipio foi Eliminada")
END
END

// Blog Com Video Explicando drop table
// http://windevdesenvolvimento.blogspot.com.br/2016/01/windev-sql-15-sql-dropt-table-eliminar.html
De matos AMARILDO
Jan. 08 2016