You’re offline. This is a read only version of the page.
Announcements
Hi everyone,
I am creating an AOT query. And in this query i drag some datasources.
I have a field StartDateTime on one of those datasources which should have a fixed datetime value. It's of type utcDateTime.
I tried using the SysQueryRangeUtil class by adding a custom method. The method returns a string and accepts a utcDateTime parameter. In the method I return SysQuery::value(parameter). I also tried without a parameter, simply returning the fixed datetime.
Next I fill the value property of the range with (customMethod(2016-10-19T00:00:00)) but it doesn't work. Invalid range... Infolog message. I also tried the US time notation 10/19/2016 12:00:00 pm, but that doesn't work either.
I also tried adding these datetime values without the custom method. And then the US notation is working. So no problem here, but if I export the query to an AOS which is running on a system with 24h notation regional settings the range value is replaced to double quotes "". If I manually re-enter the same but in 24h notation on that server, it also doesn't complain. But I don't choose what the regional settings are, it should work on all systems independent of those settings.
So what is the right approach here? I didn't find any standard AX queries which have queries with such ranges.
I know I can also create a query in x++ and use the extended range query. But i need this query inside a view as a datasource. Then again I could use a computed column, but I would think that if all these things are possible through coding that they are also possible by creating the AOT query.
And looking on Google it provides me all information about the extended range notation within x++, but not the AOT counterpart.
Hopefully you guys can help me out.
Kind regards,
Vincent Verweij
*This post is locked for comments
I wasn't sure if you didn't meant your other range, (ValidUntilDateTime==2016-07-24T16:15:55).
If "I got the query when saving or compile the query" means that you got the error when saving the query and you tried to use it anyway, then it's indeed not surprising it didn't magically start working.
Well the query where this discussion was about.
Where I added a range with the value of (getMaxDateTimeValue()).
When outputting the query, the getMaxDateTimeValue() method was put into place correctly and as you said it did not convert yet.
When I save this query (and gives me the infolog error) and add it to a view, it gave me a synchronisation error. Which seems quite logical because I got the query when saving or compile the query itself.
Which query are you talking about it in your last reply?
Martin,
Although the pseudo-sql query is being shown correctly, when I add this query to a view, it gives me a synchronisation error: View1 - Synchronize database Query extended range failure: Syntax error near 0.
So that doesn't seem to work as we would like.
I tried something that works, well sort of.
This doesn't give me an error, but there's one little sneaky thing about this.
I looked into the AX table browser of the table, and it has a value with that time.
But in the database it's stored in GMT so I should specify the GMT date in the range to make it work as I would like it to work.
But since I need to use the max datetime, which is stored in the database as this: 2154-12-31 23:59:59.000, I can simply put the hard coded value of maxDate.
It's actually weird that I can't specify the value as 2154-12-31T23:59:59, because I retested this and it replaces it with "" as said in the beginning of this post.
Well at least I have found a workaround to this weird thing.
If someone can explain why this acts like it does, feel free to share the explanation.
Regards,
Vincent
It's correct to see getMaxDateTimeValue() there. If it was already interpreted, it would be an equivalent of a static value. The point of these methods is that they're interpreted again on every run, so they can refer to the current date, current user and other things that can change from run to run.
It provides me with this output:
...
AND (((getMaxDateTimeValue())))
...
Is that correct or should it translate the DateTimeUtil::maxValue() into 2154-12-31T23:59:59 and fill that in on the place where now is getMaxDateTimeValue()?
You can look at the resulting pseudo-SQL query string (using QueryBuildDataSource.toString()), but I don't think you'll see anything interesting there. Then it's indeed interpreted by AX kernel.
Hi Martin,
I get the error when filling in the value property and pressing enter.
But also when I save the query and when I compile the query.
So indeed I wouldn't see anything in SQL.
Because of the error, I also didn't attach it to a view yet.
I compiled and forward-compiled the SysQueryRangeUtil class, and that didn't help.
This error is probably given from kernel code, so I wouldn't be able to debug, would I?
Regards,
Vincent
It works all right for me in AX 2012 R3 (but I don't have CU9 there).
Do you get the error immediately when setting the value, or when running the query? Note that you'll get exactly the same error message if the method doesn't exist.
It definitely fails before sending anything to SQL, so that wouldn't see anything there.
#1
André Arnaud de Cal...
293,325
Super User 2025 Season 1
#2
Martin Dráb
232,195
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (