A CAN-FIND can't be used in a query. How can the following be achieved without using extra (temp) tables?
"Show all customers who don't have any invoices".
OPEN QUERY BROWSE-1 FOR EACH customer
WHERE NOT CAN-FIND(FIRST invoice OF customer).
-> CAN-FIND is invalid within an OPEN QUERY. (3541)
Use the FOR EACH or FIND statements if a CAN-FIND is necessary, or use a FIND trigger or IF statement to select out records with a CAN-FIND in a QUERY.
Any ideas?
Why do you feel that it is important not to use a temp-table?
First of all I'd just like to know if it's possible without a temp-table, I'm starting to think it isn't...
Secondly, we're working with a fat client application that has a browse that operates directly on the customer table. Adding a temp-table layer in between would hit the performance I guess?
customer table. Adding a temp-table layer in between
would hit the performance I guess?
That depends! There are a number of query situations where a temp-table populated once and used in a join might save a number of (almost) full table scans and enhance the performance dramatically.
The way that you have described your data I don't think that it is possible.
As for the "performance hit"... the temp-table could help to mitigate the hit that you're going to take from doing table-scans.
If I was really concerned about the performance of this query I'd add a de-normalized field to the customer table for # of invoices and select customers where num-invoices = 0. The down side to that, of course, is that you would then have to ensure that the field is properly managed.
OK, thanks for the answers guys!
You might also want to consider that, by browsing on the customer table directly, your UI is connected to the database directly, which is not considered desirable modern architecture. By populating a temp-table and coupling the UI to the temp-table, you decouple from the database. Better yet, if there are joins involved, as there are here, you can create a ProDataSet to hold the data. If there is any updating involved, you might also want to look into the TRACKING-CHANGES features.