SET FinancialRelations = '123,14413,2417,2342340,24444566,5555,8888,9999';
The idea is that the (comma separated) relationnumbers listed in that variable are excluded in some loadstatements (like when loading the relations table).
The current implementation is
where match(relationNumber,$(FinancialRelations ))=0
>>I think this can be risky, for eg when relation 100 must be excluded and relation 1 and 10 exists... I think that then relation 1 and 10 are excluded (and should not be).
What is the easiest way to exclude those relations?
in SQL I would just write
select * FROM relations where relationNumber not in (select number from RelationsToExclude)
I know I can load the RelationsToExclude in an inline table with this script
load Num(SubField('$(BoekhoudkundigeRelaties)',',')) as RelationsToExclude
Andd probably the easiest way is to use where not exists.... But I wonder if there is no easier way... Since when using not exists... I need the field relationsToExclude also in my relationsTable which I do not need (and I need an additional resident load...)
Or am I just making things to complicated and is there an easier way?
1: I didn't know that where not exists() accepts more than 1 parameter (and therefore I thought that my relations table had to have the "relationsToExclude" column)... So this is something I learn (and will test asap). Thanks!
2: I don't understand why the comparison with match will work fine...
$(FinancialRelations) is one large string with commaseparated relationnumbers...
when this string contains the value 100 ( so relation 100 must be excluded)
and my relation table contains relation number 1 and 10... then I assume that Where not Match( relationNumber, $(FinancialRelations)); also excludes 1 and 10...