PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • SQL query with external join to a data file (query editor)
  • SQL query with external join by programming
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
In SQL language, an external join is used to exclude or include file records with or without correspondences in the linked files.
By default, when creating a query between 2 linked data files, only the records with a correspondence between the 2 files are processed. To process the records without correspondence, an external join must be added.
Let's see how to perform an external join by using:
SQL query with external join to a data file (query editor)
This query is used to select the records found in a data file (main file) with a correspondence (join) to another data file (fsecondary file or linked file), knowing that all the records found in the main file do not necessarily have a correspondence in the secondary file.
In this example, we are going to select the records found in CUSTOMER file (main file) with the sum of orders in ORDER file (secondary file) for each customer. Some customers may have no order. Therefore, an external join must be added into the query to process the customer without orders.
The different steps for creating this SQL query with external join are as follows:
  1. Select "File .. New .. Query". The wizard for query creation starts.Click New among the quick access buttons.
    • Click "Queries" in the wheel that is displayed. In the window that is displayed, click "Queries".
    • The wizard for query creation starts.
  2. Select the "Select" option and validate.Specify that you want to create a select query ("Select" option).
    Wizard for query creation

    Go to the next wizard step.
  3. The description window of query is displayed.
  4. Give a name and a caption to the query:
    Name and caption of query
  5. In the left section of the description window, choose the file items that will be used (in our example, the CUSTOMER file and the CustomerName, Company and City items then the ORDER file and the TOTALBT item).
  6. Double-click the names of items to add them into the list of query items:
    Selection of items
  7. To perform the sum of orders, click the "Sum" button in the right section of the editor. The following window is displayed in the editor:
    Sum
  8. Validate. The description window of query is upated:
    Query description
  9. Validate the description window of query. The following query is displayed in the editor:
    Displaying the query in the editor
  10. To add the external join, double-click the "Join" area.
    join
  11. The description window of the join is displayed.
  12. To select the direction for the external join, check the "include..." box corresponding to the requested direction. In our example, the records found in CUSTOMER file without orders are included.
    Definition of joins
  13. Validate. The description window of query is upated.
    Query description
  14. Save the query (CTRL S).
  15. Press the F2 key to see the SQL code:
    SQL code of query
  16. Run the query test (GO in the quick access buttons).
  17. The query can be run in the program by HExecuteQuery.
SQL query with external join by programming
The SQL queries can be directly written by programming in the WLanguage code. To do so, you must:
  1. Create a Data Source variable to represent the query at run time.
  2. Create a Character String variable to contain the SQL code of the query and write the SQL code in this variable.
  3. Run the SQL query with HExecuteSQLQuery.
  4. Browse the result with the HReadXXX functions.
Code sample
Src1 is Data Source
sSQLCode is string

// Select the products whose price is greater than ...
sSQLCode = [
SELECT
CUSTOMER.CustomerName AS CustomerName,
CUSTOMER.Company AS Company,
CUSTOMER.City AS City,
SUM(ORDERS.TotalBT) AS the_sum_TotalBT
FROM
CUSTOMER
LEFT OUTER JOIN
ORDERS
ON CUSTOMER.CustNum = ORDERS.CustNum
GROUP BY
CUSTOMER.CustomerName,
CUSTOMER.Company,
CUSTOMER.City
]

HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.CustomerName, Src1.Company, Src1.the_sum_TotalBT)

END
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment