Yes, in order to achieve this a special type of link between the database tables needs to be created. This functionality is available from version 3.05.0036 onwards.
If we take an example using the sports2000 demo database and say that we want to create a query to find customers who have no orders.
To do this we need to create a link between our Customer and Order tables in our sports2000 database. In this case it will be probably best to create the link from the Subject wizard as it’s likely that we only want to use this special type of link in certain Subjects.
So first we create a new Subject. This will use the Customer and Order table. Once we have selected the Order table we can select Modify Link so that we can specify our special type of link.
To get the behaviour we want the link needs to be set as an outer join as shown below.
When we select Next and move onto the next step in the Link Wizard we can specify the join between the two tables. Select the 'Allow manual editing' check box so we can type in the required link.
What we must do to get the query that uses this subject to return only customers which have no orders is to add the following code onto the end of the join 4GL:
|,FIRST ttdatapaint-<dbname>-<tablename> WHERE NOT AVAILABLE <dbname>.<tablename
<dbname> is the name of the database the table we are checking for a record in is located and,
<tablename> is the name of the table we are checking for a record in
In our example on the sports2000 database this would become:
|FOR EACH sports2000.Customer, EACH sports2000.Order OUTER-JOIN WHERE sports2000.Order.Custnum = sports2000.Customer.custnum, FIRST ttdatapaint-sports2000-Order WHERE NOT AVAILABLE sports2000.Order
This is should in the screenshot below:
Then create the subject in the same way as you normally would. Any Queries or Reports created using this Subject will now only return Customers who have no Orders.