Quick question - is the use of BIND variables supported in the JOIN expression?
Because for some reason, when I do, I only get a single record back while I now it should return multiple - if I invert the order I get everything except one back - if I move the variable based condition to the FILTER I goes through all the expected records, but I am not sure if this is the correct result fully as the filtering is not done on the JOIN side.
To explain, I have two datafiles, one contains all my invoices and has an invoice number, the second is a special datafile which contains references to an external platform, but as I do not want to create a datafile per entity in my application, I create a polymorphic table, which looks as follows:
ExternalReferences FILE,DRIVER('TOPSPEED'),NAME('data\references.dat'),PRE(Ref),CREATE
NummerKey KEY(Ref:Nummer),PRIMARY,NOCASE
AccountIdPlatformReferenceReferenceTypeKey KEY(Ref:AccountId,Ref:PlatformReference,Ref:ReferenceType),DUP,NOCASE
AccountIdReferenceTypeKey KEY(Ref:AccountId,Ref:ReferenceType),DUP,NOCASE
AccountIdReferenceTypeEnValueKey KEY(Ref:AccountId,Ref:ReferenceType,Ref:ReferenceValue),DUP,NOCASE
AccountIdKey KEY(Ref:AccountId),DUP,NOCASE
ParentKey KEY(Ref:Parent),DUP,NOCASE
record RECORD
Nummer LONG
Parent LONG
AccountId CSTRING(39)
PlatformReference STRING(128)
ReferenceType LONG
ReferenceValue LONG
END
END
My View is as follows (at least the original version):
OpenInvoices VIEW(Invoices),ORDER('Inv:Data, Inv:Time'),FILTER('Inv:Paid = paidState')
JOIN(ExternalReferences, 'Ref:AccountId = accountId AND Ref:ReferenceType = referenceType AND Ref:ReferentieWaarde = Inv:Number'),INNER
END
END
But this results in the above behavior, below is the looping code:
accountId = '{SOME_ID}' ! Passed through as parameter from the procedure this is in
referenceType = ReferenceTypes:Invoice ! ITEMIZE structure
paidState = 'N' ! Constant value
BIND('accountId', accountId);
BIND('referenceType', referenceType);
BIND('paidState', paidState);
BIND('Inv:Paid', Inv:Betaald);
BIND('Inv:Number', Inv:Bezoeknummer);
BIND('Inv:Date', Inv:Datum);
BIND('Inv:Time', Inv:Tijd);
BIND('Ref:AccountId', Ref:AccountId);
BIND('Ref:ReferenceType', Ref:ReferenceType);
BIND('Ref:ReferenceValue', Ref:ReferenceValue);
OPEN(OpenInvoices);
ASSERT(ERRORCODE() = 0, 'Could not open view: ' & ERROR());
SET(OpenInvoices);
ASSERT(ERRORCODE() = 0, 'Could not set view: ' & ERROR());
LOOP
CLEAR(Invoices);
CLEAR(ExternalReferences);
NEXT(OpenInvoices);
ASSERT(ERRORCODE() = 0 OR ERRORCODE() = 33, 'Something went wrong while looping through the open invoices: ' & ERROR());
IF ERRORCODE() <> 0
BREAK;
END
ASSERT( |
Ref:AccountId = accountId AND Ref:ReferenceType = referenceType AND Ref:ReferentieWaarde = Inv:Number, |
'Found a reference which doesnt the join: <13,10>' & ConcatenateFields(Ref:Record, '<13,10>', TRUE, ': ') |
);
! DO SOMETHING
END
CLOSE(OpenInvoices);
UNBIND('accountId');
UNBIND('referenceType');
UNBIND('paidState');
UNBIND('Inv:Paid');
UNBIND('Inv:Number');
UNBIND('Inv:Date');
UNBIND('Inv:Time');
UNBIND('Ref:AccountId');
UNBIND('Ref:ReferenceType');
UNBIND('Ref:ReferenceValue');
If I change the view to the following I get all the expected records, but it feels weird and unnatural:
OpenInvoices VIEW(Invoices),ORDER('Inv:Data, Inv:Time'),FILTER('Inv:Paid = paidState AND Ref:AccountId = accountId AND Ref:ReferenceType = referenceType')
JOIN(ExternalReferences, 'Ref:ReferentieWaarde = Inv:Number'),INNER
END
END