CAN-FIND in dynamic queries

Progress 4GL (or now ABL) don't allow to use CAN-FIND in dynamic queries. This often affects browsers or complex conditions, especially not allowing to use NOT CAN-FIND. Sometimes You need to get Customers for which You don't have created Invoices. Help comes with OUTER-JOIN and temp-tables.

First, lets look at the example issue. I need to have a query which give me all Customers for whom I don't have Invoices.

First what You could think is this:

But You'll get an error: CAN-FIND is invalid within an OPEN QUERY. (3541)

So let's try step-by-step. Firstly, lets go for simple join which will give us only Customers who have some Invoices. Here we also facing another issue, if we want to count invoices for each customer. FIRST-OF and LAST-OF cannot be used in queries with BREAK BY. That have to be done manually.

But still, this will only give You Customers with Invoices

And here with helpful hand comes OUTER-JOIN.

Adding OUTER-JOIN to Invoice condition, You'll get all customers, whether they have invoices or not! You have to remember, that You agreed for cases, that You will not have Invoice record caught, so.... IF AVAIL have to be implemented.

We are almost there, now how to throw away Customers that have Invoices?

Here You can use simple trick by adding a dummy TEMP-TABLE with a decoy record and there check availability of Invoice for Customer. It looks like that:

Condition for a ttDummy will give You just those cases where OUTER-JOIN provides Customers without Invoice - et voilà!

This trick is very useful when You have to build query dynamically for multiple tables, especially in complex browsers. From my experience it performs also pretty well.

That's all folks!