2.2Query editor & SQL
The essentials in 30 seconds
- A query is an interrogation of the database; WinDev offers two complementary approaches: the visual query editor (which generates the SQL) and direct SQL for complex cases.
- The essential SQL building blocks: SELECT (what), FROM (where), WHERE (filter), ORDER BY (sort), JOIN (combine linked files), GROUP BY + aggregates (SUM, COUNT, AVG, MIN/MAX).
- Two major good practices: make the query parameterised (reusable) and filter on the database side with WHERE rather than bringing everything back and then sorting in WLangage.
Running mini-case — Yacine queries his trading company's sales
Yacine, a WinDev developer at an SME trading electrical equipment in Oran, must display, next to each order, the customer name and not just their code. He opens the visual query editor, selects the Commande and Client files, ticks the useful columns and sets a join on the customer code: WinDev generates the SQL, with no risk of syntax errors. For the boss's dashboard, he adds a GROUP BY with SUM to obtain revenue per customer. He then realises that the query is hard-coded on a period: he makes it parameterised ({pDateDebut, pDateFin}) so he can reuse it from one month to the next, and takes care to keep filtering on the database side (WHERE) rather than bringing back all the rows and sorting in WLangage.
Self-contained case for this section: it returns in the course, diagram, script and 48h challenge of section 2.2.
1. Two ways to write a query
A query is an interrogation of the database: “give me the orders over €1000 with the customer name”. WinDev offers (1) the query editor: a visual tool where you choose the files, tick the columns, define conditions, sorts and groupings — WinDev generates the corresponding SQL, ideal for starting out without syntax errors; (2) direct SQL (SELECT, FROM, WHERE, ORDER BY…), for complex cases or when you know the language. Since HFSQL supports SQL, the two coexist.
2. The SQL building blocks and the join
The essential notions: SELECT (which columns), FROM (which files), WHERE (filter rows), ORDER BY (sort). The join (JOIN) combines several linked files — indispensable for displaying the customer's name next to their order. GROUP BY with aggregates (SUM, COUNT, AVG, MIN/MAX) produces statistics and totals per category.
3. Reuse and filter on the database side
A query is reusable everywhere: as the source of a Table control, the source of a report, or traversed in WLangage. Two good practices: parameters (the city or period becomes a variable instead of being hard-coded — one reusable query instead of ten) and filtering on the database side (let the engine filter with WHERE rather than bringing everything back and sorting in WLangage: infinitely faster).
🔎 Concrete example — 🔎 Concrete example — Join & parameters
Display the customer name and calculate revenue per customer: Yacine writes the corresponding SQL.
SELECT C.NomClient, SUM(L.MontantLigne) AS ChiffreAffaires
FROM Client C
JOIN Commande CD ON CD.CodeClient = C.CodeClient
JOIN LigneCommande L ON L.NumCommande = CD.NumCommande
WHERE CD.DateCommande >= {pDateDebut} // parameter, not hard-coded
GROUP BY C.NomClient
ORDER BY ChiffreAffaires DESC
Reading : JOIN displays the customer name; GROUP BY + SUM give the revenue
Reflex : filter ON THE DATABASE SIDE (WHERE) and parameterise the periodReading: JOIN combines the linked files to display the customer name; GROUP BY + SUM total the revenue per customer.
Lesson: the {pDateDebut} parameter makes the query reusable, and WHERE lets the engine filter on the database side — much faster.
Section vocabulary
- Query
- An interrogation of the database: selecting, filtering, sorting and grouping data; reusable as a table source, a report source or in WLangage.
Simple example: Yacine writes a “revenue per customer” query.
What it is not: a simple file display with no condition or sort.
Reflex: interrogate the data → a query.
- Query editor
- WinDev's visual tool: you choose the files, tick the columns, define conditions, sorts and groupings; WinDev generates the SQL.
Simple example: Yacine joins Client and Commande without writing a line of SQL.
What it is not: typing the query by hand in direct SQL.
Reflex: create visually without syntax errors → the query editor.
- WHERE (filtering)
- The condition that keeps only certain rows; placed on the database side, it lets the engine use its indexes and send only what is useful.
Simple example: Yacine displays only the orders for the desired period.
What it is not: loading everything then filtering in WLangage afterwards.
Reflex: reduce the volume sent → filter on the database side with WHERE.
- Join (JOIN)
- Combine several linked files in the same query — indispensable for displaying the customer name next to their order.
Simple example: Yacine joins Commande and Client on the customer code.
What it is not: displaying only the customer identifier, without their name.
Reflex: cross information from several files → a join.
- GROUP BY + aggregates
- Group to calculate statistics: SUM (total), COUNT (number), AVG (average), MIN/MAX.
Simple example: Yacine totals revenue per customer.
What it is not: listing each order line by line without a total.
Reflex: totals per category → GROUP BY with aggregates.
In practice — In practice — Creating and using a query
- Create a query with the visual editor: choose the files, tick the useful columns, add the selection conditions and the sort, like Yacine.
- Add a join if you need information from several linked files (display the customer name on the order line).
- For statistics, use GROUP BY with aggregates (SUM, COUNT, AVG) — for example revenue per customer or per sales rep.
- Make the query parameterised (the city or period becomes a parameter) so you can reuse it, and let filtering happen on the database side (WHERE).
Step-by-step script — write Yacine's query (5 min)
1) Open the query editor, add the Client, Commande and LigneCommande files. 2) Tick NomClient and set a join on the customer code. 3) Add a GROUP BY on NomClient with SUM(MontantLigne) for revenue. 4) Declare a {pDateDebut} parameter instead of hard-coding the period. 5) Keep filtering on the database side (WHERE) and test the result.
This query will serve as the source for a Table control (2.4) and for a report (2.3): write it once, reuse it everywhere.
Copy-paste template — my parameterised query
SELECT C.NomClient, SUM(L.MontantLigne) AS CA
FROM Client C JOIN Commande CD ON CD.CodeClient = C.CodeClient
JOIN LigneCommande L ON L.NumCommande = CD.NumCommande
WHERE CD.DateCommande >= {pDateDebut}
GROUP BY C.NomClient ORDER BY CA DESC
Rules : (1) parameterised query = reusable
(2) DATABASE-SIDE filtering (WHERE) > bringing everything back and sorting in WLangageSimple selection
Yacine lists the products whose stock is below the threshold, with a simple WHERE, without a join.
Customer join
Yacine displays the customer name next to each order by joining Commande and Client.
Statistics per sales rep
Yacine groups with GROUP BY and SUM for revenue per sales rep.
Parameterised query
Yacine replaces the hard-coded period with {pDateDebut, pDateFin}: the query serves every month.
✓ Best practices
- Start with the visual editor: it generates the SQL without syntax errors.
- Use a join to display linked information (customer name).
- Parameterise the query and filter on the database side (WHERE).
✗ Common pitfalls
- Bringing everything back and then sorting in WLangage (network/memory saturation).
- Writing criteria hard-coded instead of parameterising the query.
- Forgetting the join and displaying only the customer code.
Key takeaways
- Two complementary approaches: visual query editor (generates the SQL) and direct SQL.
- Key notions: SELECT, FROM, WHERE, ORDER BY, JOIN, GROUP BY + aggregates (SUM, COUNT, AVG, MIN/MAX).
- A query is reusable as a table source, a report source or traversed in WLangage.
- Good practices: parameterised queries and filtering on the database side (WHERE).
48h challenge — apply it tomorrow
Like Yacine, create a query that joins two linked files (for example Orders and Customers) to display the customer name, then add a GROUP BY with SUM for a total per customer. Finally turn the period into a parameter {pDateDebut} and explain in one sentence why filtering on the database side is faster than bringing everything back and sorting in WLangage.
- I create a query with the visual editor and read the generated SQL.
- I know how to use a join and aggregates (GROUP BY, SUM).
- I parameterise my queries and filter on the database side.
Frequently asked questions
Why should you filter on the database side rather than bringing all the data back?
Because the engine uses its indexes and sends only the useful rows, whereas bringing everything back saturates the network and memory only to throw away most of the data — the most common performance mistake. Put the conditions in the query (WHERE), let the database sort (ORDER BY) and delegate the aggregates.
Is the query editor enough, or should you write direct SQL?
The visual editor is enough for the vast majority of needs (selection, filters, sorts, joins, groupings) and generates SQL without syntax errors. Direct SQL is for complex cases or when you want fine-grained control; the two approaches coexist because HFSQL supports SQL.
Why should you filter on the database side rather than bringing all the data back?