SELECT ... WHERE FieldValue in (SELECT ...)

Eugene

the question is how in generel the SELECT in SELECT SQL query can be interpreted in Navision (how do i do this kind of SELECT in Navision)

the example could be as follows:

I want to select those lines in 81 Gen. Journal Lines table where Posting Group is in the subset of "FA Posting Groups".Code values for which "FA Posting Groups"."Acquisition Cost Account" is some constant in question (let's say '01 016').
Namely:

SELECT *
FROM "81 Gen. Journal Lines" as GJL
WHERE GJL."Posting Group" IN
( SELECT "Code"
FROM "FA Posting Groups"
WHERE "Acquisition Cost Account" = '01 016'
);

how do i do it in navision ?

kriki

I see 2 ways for this:
1) If you have few values in "FA Posting Groups" (they have to smaller then 1024 bytes), you can do this:

// make a filter of the "FA Posting Groups"
txtFilter := '';
recFAPostingGroups.RESET;
recFAPostingGroups.SETCURRENTKEY(...);
recFAPostingGroups.SETRANGE("Acquisition Cost Account",'01 016');
IF recFAPostingGroups.FIND('-') THEN
  REPEAT
     txtFilter := txtFilter + '|' + recFAPostingGroups.Code;
  UNTIL recFAPostingGroups.next = 0;
txtFilter := COPYSTR(txtFilter,2):

recGenJournalLine.RESET;
recGenJournalLine.SETCURRENTKEY(...);
recGenJournalLine.SETFILTER("Posting Group",txtFilter);
IF recGenJournalLine.FIND('-') THEN
  REPEAT
    ...
  UNTIL recGenJournalLine.next = 0;

2) In case you have a lot of values, you need to use a temptable

// create a temptable for "FA Posting Groups" (a temptable is in memory, so it decreases DB-access and goes faster)
recFAPostingGroups.RESET;
recFAPostingGroups.SETCURRENTKEY(...);
recFAPostingGroups.SETRANGE("Acquisition Cost Account",'01 016');
IF recFAPostingGroups.FIND('-') THEN
  REPEAT
    tmpFAPostingGroups := recFAPostingGroups;
    tmpFAPostingGroups.INSERT(FALSE); // tmpFAPostingGroups is a temptable
  UNTIL recFAPostingGroups.next = 0;

recGenJournalLine.RESET;
recGenJournalLine.SETCURRENTKEY(...);
// this is slower, because you can't put a filter but have to test all records
IF recGenJournalLine.FIND('-') THEN
  REPEAT
    IF tmpFAPostingGroups.get(recGenJournalLine."Posting Group") THEN BEGIN
      ...
    END;
  UNTIL recGenJournalLine.next = 0;

Eugene

thanx , i indeed run into trouble with 1024 limitation that's the reason i ask it here. Temporary table is indeed more universal solution but i was actually thinking if it would be possible to use FlowField here in Gen Journal table with Lookup into groups table and then filter on based on this new Flowfield

kriki

Eugene wrote:

thanx , i indeed run into trouble with 1024 limitation that's the reason i ask it here. Temporary table is indeed more universal solution but i was actually thinking if it would be possible to use FlowField here in Gen Journal table with Lookup into groups table and then filter on based on this new Flowfield

Also that is a possibility. But you need to add the field. This field you will only use with this filtering, so it is a little overkill. And instead of that you control if a T81-record is good, Navision will do that. But it will be somewhat faster.