Hi, Stefan Osterburg,
You cannot create date value in dynamic range value in query object using
queryValue(dtDate). I have tried before and trying to solve by finding it's
pattern to generate date. The result is this formula:
static int date2SQLint(TransDate _dtDate)
{
return
(year(_dtDate) - 1900) +
((mthofyr(_dtDate) - 1) * 256) +
((dayofmth(_dtDate) - 1) * 65536);
}
So, the only thing you should do is change queryValue function with date2SQLint
function.
Try that.
Regards,
Sonny Wibawa Adi
Stefan Osterburg <[EMAIL PROTECTED]> wrote:
afaik this relation is automatically added by Axapta as a result of
specifying the
qBR2.relations(true);
but I have also tried manually adding this with addLink with no
change.
Stefan
Steeve Gilbert schrieb:
>
> The relation "emplTable.EmplId==projvalEmplProjSetup.EmplId" doesn't seem to
> be specified in the queryBuild form of the query. Is that supposed to be
> there or not?
>
> Steeve...
>
>
> -----Message d'origine-----
> De : Stefan Osterburg [mailto:[EMAIL PROTECTED]
> Envoy� : 10 janvier 2005 15:54
> � : [email protected]
> Objet : [development-axapta] problems creating a complex with querybuild
>
>
>
> 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
>
---------------------------------
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
[Non-text portions of this message have been removed]
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:
http://docs.yahoo.com/info/terms/
