I do apologise if this is a blatant developer oversight but I'm struggling to understand why these two statements provide different results...
The FOR EACH statement provides multiple resulting permit rows but the QUERY-PREPARE only provides the first matching permit row. Here are the two statements...
1) FOR EACH
for each usertab where usertab.user-id = p-id,
each permit where index(permit.activity,'.') > 0
and (can-do(permit.can-run, usertab.profile-name)
or permit.can-run = ' '),
first menugrp of permit :
display permit.activity.
end.
2) QUERY-PREPARE
/* *************************** Definitions ************************** */
DEFINE QUERY qUserMenu FOR usertab,
permit,
menugrp.
/* ******************** Data-Source Definitions ******************** */
DEFINE DATA-SOURCE srcUserMenu FOR QUERY qUserMenu usertab KEYS (user-id),
permit KEYS (activity),
menugrp KEYS (menu-group-no).
QUERY qUserMenu:query-prepare
(SUBSTITUTE("for each usertab where usertab.user-id = &1",
QUOTER(STRING(p-id)))
+ ", each permit where index(permit.activity,'.') > 0"
+ " and (can-do(permit.can-run, usertab.profile-name) or permit.can-run = ' ')"
+ ", first menugrp of permit" ).
Note that if I change the 'each permit' to 'last permit', I do in fact retrieve the last matching record. I have also tried simplifying the condition of the 'each permit' statement and still only retrieve one record.
Steve
each permit where index(permit.activity,'.') > 0
and (can-do(permit.can-run, usertab.profile-name)
or permit.can-run = ' '),
I don't know if this is related to your question or not, but INDEX and CAN-DO cannot be evaluated using an index. So on your permit table you are always running a full table scan. Worse that that, you are doing multiple full table scans, one per iteration of the first table.
For the first term (the index function) using a logical field in the permit table that is updated using a trigger would be an alternative. That logical field could be part of an index.
For the can-do phrase a word-indexed field would be a good alternative. In that case the first phrase could also be part of the word index, when using a logical token in the word index like CONTAINS ("permit_activity_ge_0") or something like that.
Thanks Mike, your input is much appreciated. I understand the issues now regarding the inefficiency...however, the table only has a few hundred records in it. I do want to receive a multiple record result but at the same time understand why the difference.
Apologies for leading you up the garden path...the two statements resolve identically in fact. The cause of only one record being present in the QUERY-PREPARE version is that one of the elements of the key was a duplicate in the table..and we all know that ProDataSets will not allow duplicates don't we!!!
Lesson learned!