- Selection condition
- Creating a view on a filtered data file
In french: HCréeVue
Not available with this kind of connection
From version 20
, the SQL views and the materialized views are available.
The views handled by this function correspond to the former mechanism of views. They do not correspond to SQL views or to materialized views.
Creates a HFSQL view.
Reminder: A HFSQL view corresponds to a "memory image" of a data file. A view is stored in memory, which insulates it from the modifications made to the associated data file. Once created, a view can be handled like any HFSQL data file.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
// Create a view
MyView is Data Source
HCreateView(MyView, CUSTOMER, "*", "NAME,CITY", "NAME]='A'", hViewDefault)
<Result> = HCreateView(<> , <Data file> [, <List of items> [, <> [, <Selection condition>]]] [, <Creation mode> [, <>]])
- True if the view was created,
- False if a problem occurred. HError is used to identify the error.
<>: Data source (or character string)
Name of the view to create. A Data Source variable is used to optimize the access time to the view items.
<Data file>: Character string (with or without quotes)
Name of data file used. . You also have the ability to use a data source.
<List of items>: Optional character string
List of items in the view. In this list, the different items are separated by commas or by CR characters (Carriage Return).
If this parameter is not specified or if it corresponds to "*", the items found in the data file will be included in the view.
<>: Optional character string
List of sort items for the view. In this list, the sort items are separated by commas or by CR characters (Carriage Return). The sort is performed according to the order of the items in the list.
The sort direction can be specified for each item:
- a "+" sign in front of the item name indicates an ascending sort order.
- a "-" sign in front of the item name indicates a descending sort order.
If this parameter is not specified or if it corresponds to an empty string (""), no sort is performed in the view.
A single sort item must be specified in <Sort Items>.
<Selection condition>: Optional character string
Selection condition of records. All items found in the data file can be included in the condition (key or non-key items).
No selection condition is applied if this parameter is not specified or if it corresponds to an empty string. This selection condition can be a character string in Ansi or Unicode format.
<Creation mode>: Optional Integer constant (combination of constants)
Configures the creation mode of view.
|hViewLocking||The records read in the data file and included in the view are locked.|
|hViewDefault||Default creation mode of view|
|hViewPostponed||. The data found in the view will be the data found during the execution of the view.|
|hViewDistinct||The view is created and the duplicates are deleted according to the sort key (in this case, the sort items must be specified)|
If several items are specified, the duplicates are eliminated according to the composite key of the specified sort items.
If the hViewDistinct
constant is specified, there is no need to specify a sort item in <Sort Items>. In this case, the hViewDistinct
constant is ignored.
|hViewExclusive||Optimizes the creation time of view by locking the entire source file.|
|hViewBreakable||The view creation can be interrupted by pressing the ESC key.|
This constant is not available.
<>: Optional integer
Number of records per page (for a view used by remote access).
This parameter is ignored.
The general syntax of a condition has the following format:
"CustName>'Smith' and ZipCode=34 or ZipCode=32"
The supported operators depend on the type of items used in the condition:
Notes about the selection condition:
|<>||Different||Valid for all types|
|>||Greater than||Valid for all types|
|>=||Greater than or equal to||Valid for all types|
|<||Less than||Valid for all types|
|<=||Less than or equal to||Valid for all types|
|=||Strictly equal to||Valid for all types|
|~=||Almost equal to: ignores the space characters on the right, the accented characters, the lowercase and uppercase characters||Valid for string types only|
|~~||Very flexible equality: does not distinguish between lowercase and uppercase characters, ignores the space characters found before and after the string to test, ignores the lowercase accented characters, ignores the space characters and the punctuation characters inside the strings.||Valid for string types only|
|]||Contains: takes the lowercase and uppercase characters into account||Valid for string types only|
|~]||Contains: ignores the lowercase and uppercase characters||Valid for string types only|
|]=||Starts with: takes the lowercase and uppercase characters into account||Valid for string types only|
- The constant strings must be enclosed in simple quotes.
- <Item Name> must only contain letters, digits and underscore characters ("_"). If <Item Name> contains other characters (apostrophe, ...), the name of the item must be enclosed in double quotes.
- Comparisons between strings are performed according to the ASCII value of the characters and not according to the lexicographic value ('a' > 'Z').
- The binary memos and the composite keys cannot be part of a selection condition.
- If a <string constant> contains a simple quote (or a double quote), this simple quote (or double quote) must be preceded by a backslash character (\).
- . Indeed, the HFSQL engine analyzes the selection condition and it uses these statistics to define the most discriminating items that will be used to optimize the browse operations performed on the data files.
- This selection condition can be a character string in Ansi or Unicode format.
Creating a view on a filtered data file
HCreateView takes into account the filter applied to the source data file of the view.
This characteristic is supported by the views created from HFSQL Classic or Client/Server, Hyper File 5.5 and xBase data files.
This page is also available for…