Hi,
I am trying to create a somewhat more complex query as a form datasource
I want to get all records from EmplTable, that have
neither have a corresponding record in projvalEmplProjSetup
for a certain project ("someprojid") nor records for other
projects whose daterange includes a certain date ("somedate").
in "Axapta-SQL" the following _expression_ does what I need:
while select emplTable
notexists join projvalEmplProjSetup where
emplTable.EmplId==projvalEmplProjSetup.EmplId &&
(projvalEmplProjSetup.Projid==someprojid ||
(projvalEmplProjSetup.myStartDate<=somedate &&
projvalEmplProjSetup.myEndDate>=somedate))
I have not yet been able to get this working with the
a querybuild object. The biggest problem is getting
the OR/AND-conditions in the where _expression_ to work.
If have tried the following, but it does not give the correct
results:
q = new Query();
qBR1 = q.addDataSource(tablenum(EmplTable));
qBR2 = qBR1.addDataSource(tablenum(ProjValEmplProjSetup));
qBR2.relations(true);
qBR2.joinMode(JoinMode::NotExistsJoin);
range=qBR2.addRange(fieldnum(ProjValEmplProjSetup, projid));
range.value(strfmt(' %1) || ((%2.%3 <= %5) && (%2.%4 >= %5)',
someprojId,
q.datasourceno(1).name(),
fieldstr(ProjValEmplProjSetup, myStartDate),
fieldstr(ProjValEmplProjSetup, myEndDate),
queryvalue(somedate),
fieldstr(ProjValEmplProjSetup, ProjId)));
Can anyone help?
Stefan
Yahoo! Groups Links
- To visit your group on the web, go to:
http://groups.yahoo.com/group/development-axapta/
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
