ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / HFSQL functions
  • Influence of sort items
  • Handling the view items after a join
  • Joins: different search results according to the order of the views in the join
  • Hyper File 5.5
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
HMergeView (Function)
In french: HFusionneVue
ODBCNot available with this kind of connection
Warning
From version 20, SQL views and the materialized views are available.
The views handled by this function correspond to the former view mechanism. They do not correspond to SQL views or to materialized views.
Creates an HFSQL view from two views created beforehand (HCreateView). Several operations can be performed when merging the views.
// Merge two views
MyView is Data Source
ViewCustomer84 is Data Source
...
HMergeView(MyView, ViewCustomer84, ViewCustomer84, hViewUnion, "+CustName")
Syntax
<Result> = HMergeView(<Destination view> , <First view> , <Second view> , <Type of operation> , <Sort item of first view> [, <Sort item of second view> [, <Creation mode> [, <Page size>]]])
<Result>: Boolean
  • True if the view was created,
  • False if a problem occurs. HError is used to identify the error.
<Destination view>: Character string
Name of view to create, result of operation performed on the two specified views.
<First view>: Character string
Name of first view included in the operation.
<Second view>: Character string
Name of second view included in the operation.
<Type of operation>: Integer constant
Operation that will be performed between the two specified views:
hViewIntersectionRows common to <First view> and to <Second view>.
hViewJoinJoin between the first view and the second view.
hViewSubtractionRows of <First view> without the common rows of <Second view>.
hViewUnionCombination of all rows of <First view> and <Second view>.
The structure of views must be identical.
hViewUnionExCombination of all non-common rows of <First view> and <Second view>.
The structure of views must be identical.
<Sort item of first view>: Character string
Name of the item that will be used on the first view to perform the operation. This item will also be used to sort the destination view.
<Sort item of second view>: Optional character string
Name of the item that will be used on the second view to perform the operation. If this parameter is not specified, it is equal to the value of <Sort item of first view>.
<Creation mode>: Optional Integer constant (or combination of constants)
Configures the creation mode of view.
hViewAddStores the content of the view next time HExecuteView is called.
hViewBreakableThe view creation can be interrupted by pressing the ESC key.
hViewDefaultDefault creation mode of view.
hViewDistinctThe view is created without duplicates according to the sort key.
hViewExclusiveOptimizes the creation time of view by locking the entire source data file.
hViewLockingThe records read in the data file and included in the view are locked.
hViewPostponedThe view is created but it will be run later by HExecuteView.
<Page size>: Optional integer
Number of records per page (for a view used by remote access).
Remarks

Influence of sort items

The sort items are used to define the items on which the requested operation will be performed. Therefore, an intersection operation will return all the records of the first view for which <Sort item of first view> is equal to <Sort item of second view> for at least one record of the second view.

Handling the view items after a join

The items issued from a join are named as follows:
  • if the item name exists in only one of the source views, this item name is kept in the join.
  • if the item name exists in both source views, the item of the join corresponding to the item of the second view is renamed "NameView2_ItemName".
The following syntax also allows you to access the items found in the join:
<Name of source view>_<Item name>
Examples:
  • The View1 named "CustomerView" contains the items: CustomerID, OrderID, Name.
  • The View2 named "OrderView" contains the items: OrderID, Name, Product.
  • The join named "CustomerOrder" is created from the views named "CustomerView" and "OrderView" on the "OrderID" item. The "CustomerOrder" view contains the following items: CustomerID, OrderID, Name, OrderView_Name, Product.
    Accessing the item:Syntax used
    CustomerIDCustomerOrder.CustomerID
    or
    CustomerOrder.CustomerView_CustomerID
    OrderIDCustomerOrder.OrderID
    or
    CustomerOrder.CustomerView_OrderID
    or
    CustomerOrder.OrderView_OrderID
    NameCustomerOrder.Name
    or
    CustomerOrder.CustomerView_Name
    OrderView_NameCustomerOrder.OrderView_Name
    ProductCustomerOrder.Product
    or
    CustomerOrder.OrderView_Product

Joins: different search results according to the order of the views in the join

During a join between two views, the index of second view is used to perform searches on the result of the join. The search result can differ depending on the characteristics of the index used in the join.
Example:
  • Characteristics of key item named MyItem in View1: Case-insensitive searches.
  • Characteristics of item named MyItem in View2: Case-sensitive search.
A merge is performed between these two views. Depending on the order of the merged views, the result of the search will be different:
  • HMergeView(View3, View1, View2): the search is performed on view2: 15 records are found.
  • HMergeView(View3, View2, View1): the search is performed on view1: 63 records are found.
WINDEVWEBDEV - Server codeHyper File 5.5

Hyper File 5.5

To handle a Hyper File 5 database with HMergeView, you must perform the merge with HCreateView_55.
Component: wd290hf.dll
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help