Who would have thought I would ever do an article about the WX Query Builder, much less Advanced Queries! The first 18 months I used WX I doubt I created any queries at all. I knew SQL so well and was use to just writing my own statements that I just did everything with HExecuteSQLQuery.

Slowly I have came around or perhaps I have just drank enough Koolaid 🙂 I now use the standard HyperFile statements as much as possible (HReadSeek, HModify, etc.) and I use standard WX Queries as much as I can. This article will why my change of heart and then  look at one of the more advanced queries I have. It uses 2 Queries and a CASE statement.

So why the change of heart? Many reasons, but the main reason is when working with other developers it makes my code much easier for them to read. Since I do so many mentoring and contracting projects, I work with many different developers. Often these developers have different levels of programming expertise, and many of them are just beginning with WinDev or WebDev. And many of the project I work on are intended to be continue or maintained by others. By using the more “standard” out of the box approach when possible, it gives us a common ground to communicate by. Code using the standard HyperFile statements makes the code much easier to read, even for someone that has never seen WLanguage before. It doesn’t take much imagination to figure out what HReadSeekFirst or HModify is going to do!

By using the standard WX queries, it also gives folks less familiar with SQL the ability to review, maintain and use those queries. It is also very easy to consume WX queries within the IDE for tables, comboboxes, reports, etc. Another advantage is when something changes and all the lookups of a product type need to be further limited based on a new condition, there is only one place to change, the query. And like magic everywhere that query is consumed will now include that extra limitation. Add to all of this the ability to set a query parameter to null and have that portion of the filtering ignored and WX queries become very hard to ignore, although I managed to for a long time 😦

Now like most things its not all milk and honey, the standard query editor can not create as complex of a SQL statement as I sometimes need; mainly concerning advanced topics like ANY clauses, HAVING clauses, etc. But even if you are unable to create the query via the query editor, the SQL of the query can be hand code, however depending on how complex it is this will sometimes make it harder to consume then in the table and combo controls. And there are still times that I will pull out the old sledgehammer HExecuteSQLQuery, its just that now I have a lot of other tools and options that are more refined that I can use and I don’t need it as often.

So now lets look at a fairly complex query that I created in WinDev for a recent project. In the below table the Centre column presented some real challenges. The requirements were:

1. A Study can be linked to more than one centre
2. Most studies only have one centre
3. If a Study has multiple centres configured then the column should read “Multiple Centres”
4. If a Study only has one centre configured then the column should display the name of that centre
5. If a Study does not have any centres configured then a warning message should display in the column “No Centres Linked!”
6. There is a cross reference table (StudyCentre) that has a record for each combination of Study and Centre

Some developers first instinct might be to based the table control on the Study database table. Then in the Display Row event add code to determine the value of the Centre column. That would of course work, but could cause performance issues, especially once you get a couple 100 Studies in the database.

My first instinct was to write some crazy SQL statement that myself, Andy Stapleton, and a few other developers would be able to understand 🙂 But then I took another swig of Koolaid and decided to see what I could do via the Query Editor. It took some doing but I got it done! Here is the resulting Query

StudySpec is the Study database table, so you expected to see it in the Query. PriceTable is there because we are looking up the Pricing Label. But what is qryStudyCentreCount? The requirements said the database table we needed to use was StudyCentre. Well  as I am sure you have guessed qryStudyCentreCount is another WX Query, yes one of the powers of WX Queries is that they can consume other Queries. Now be warned this can cause performance issues if you get carried away, just like writing complex SQL statements can. If you don’t structure it correctly to take advantage of indexes etc. it will happily read every record looking for matches, which can take a while if you have 100,000 records. So as with any advanced feature be careful that you use it appropriately.

So let’s take a look at the qryStudyCentreCount Query first.

There is the StudyCentre database table we were expecting, and it is joining the Centre table in order to get the Centre name since only the IDs are stored in the cross reference table. Let’s look at the List of Elements in the Query Editor for this query.

To create this Query, you can start with a new Query. Then add the StudyCentreID to the Query. On the right side table drop down the StudyCentre table, then select the StudyCentreID field, and click on the right arrow to send it to the List of Elements.

This adds it to the List of Elements

Now just click on the Count button and select “Count on the selected item…”.

At the bottom of the List of elements, you see the details of the selected item.

Double click on the Name and change it to something more friendly like CentreCount.

Why are we counting StudyCentreID? Once we get the rest of the query built this will give use the count of records for each grouping.

Now use the same method to add StudySpecID to the List of Elements. We don’t need to do anything extra to it.

And finally drop down the Centre table on the left side and add CentreName to the query.

We are not quite done, take a look at details of the CentreName.

Notice that it says that the CentreName will be used to group the calculation elements (aggregate functions in SQL speak). This would give us one record for each Centre, which is not what we want. So we need to use an aggregate function on it. Since it is a string we can’t use aggregates like COUNT, SUM, or AVG; however can use MIN or MAX. Either one will work for us, I tend to use MAX but there is no reason other than personal preference.

So click on the Average, Max, Min button and chose Maximum from the drop down menu.

Rename the column to CentreName

And we get our final version of the Query.

So what exactly have we accomplished? Since we did not use an aggregate function on StudySpecID it will be used to group the records (GROUP BY clause), so we will get one record for each study. The COUNT on StudyCentreID will tell us how many records were combined in the group. If there was only one record in the group (one centre for the study) then MAX(CentreName) would contain that Centre Name, so would MIN since there is only one. If there is more than one record in the group, the MAX(CentreName) will contain the name that would be alphabetically last, but we don’t really care because if there is more than one centre we are going to display “Multiple Centres”, we will see how that is accomplished shortly.

We can test the Query and see that we are getting the expected results. Press the single GO button (Test the Query).

And we will get the Preview screen below

Notice that the two Studies that do not have Centres configured is not in this list. That is because this list is based off the StudyCentre table so it doesn’t have any records for those Studies. We will see how we handle the “No Centres Linked!” a little later.

So lets move back to the qryStudySpecForBrowse now. Let’s create it from scratch.

Start by adding several fields from StudySpec that will be displayed in the table control.

Next add PriceTableName from the PriceTable

So that was the easy part now let’s add our newly created qryStudyCentreCount Query. You will notice if you scroll all the way down in the left table that Queries are listed. So add the CentreCount field from qryStudyCentreCount

Next we want to create a Calculated Item, Press the button at the bottom left and choose “New calculated item”

On the next screen change the name to something that makes sense like CentreName.

Now we are going to get into a little advanced SQL, which isn’t the point of this article so I won’t go into great detail concerning the syntax, the point of this article is the fact that we can use the advanced SQL inside the Query editor. You will notice on the screen that you can use the screen to get field names and functions into the calculation or you can just type it in, if you are familiar with the SQL syntax. We want to create the following calculation:

Let’s break that statement down. CASE is SQL’s equivalent of WLanguage’s SWITCH statement.

So this statement returns the CentreName if the Count is 1. Remember we used MAX in our Query so if Count is one the Centre Name will be correct.

If the Count is NULL then the calculation will return “No Centres Linked!”. Why NULL instead or 0? If there are not StudyCentre records for the Study then there would be no record for that Study in the qryStudyCentreCount Query, so when we JOIN it into this Query we would not find a record and the fields from qryStudyCentreCount will contain NULL. NULL and 0 are not the same thing but that is a conversation for another day.

And finally any other value (Such as 3) and the statement returns “Multiple Centres”

We now have our desired Query that we can use for our table control.

We do have just a bit more work to do however. When we save the query we are likely to get a message similar to this

That is because the Query editor doesn’t know what fields should be used to link the qryStudyCentreCount and the StudySpec table together. If we didn’t have a link declared in the Analysis between PriceTable and StudySpec we would have a similar issue with that relationship. When you press OK you get a screen similar to this:

Notice the Cartesian join, that is the one we need to fix. Double click on it and you will get the “Describing the joins” window.

Press the Add button and a window pops up asking what field should be used for the join. We want to use StudySpec.StudySpecID and qryStudyCentreCount.StudySpecID.

When we return to the “Describing the joins” window, we will see something like this.

We want to turn on the check box for “Include the Price Table / StudySpec that have no qryStudyCentreCount

This is a very important step. We are creating an OUTER JOIN in SQL speak. Without that check box on we would be creating an INNER JOIN. With an INNER JOIN we would not see the Studies that don’t have any Centres.

While we are at it, I happen to know that all StudySpec records don’t have a PriceTableID supplied, so we need to change that JOIN to an OUTER JOIN as well by double clicking on it and turning on the appropriate check box

If we test the Query via the Test button we will see that we are now getting the expected results

Hopefully this article has convinced you to take another look at WX Queries if you are not using them, and also given you some inspiration for creating some more complex queries with it.

Be sure to go over to wxLive.us and watch the Uncle Pete’s corner webinar that is the companion to this article. 

Uncle Pete’s Corner is webinar series on all things WX, to watch the recorded version of this webinar, many other WX related webinars or to watch future ones live go to wxLive.us

[suffusion-the-author display=’author’]

[suffusion-the-author display=’description’]

• WX