35
I'm writing an AOT query (to be used in a view) wherein I need to set a range based on the current system date.
SQL Equivalent code -
SalesLine.ShippingDateRequested = GETDATE()
I have tried to use (CurrentDate()) but when i check the SQL code for the view, it gets translated to the literal date when the view was synchronized.
*This post is locked for comments
Of course you couldn't expect us to take into account requirements you didn't mention. Nevertheless I still don't see why you can't filter ToDate with the current date sent from AX.
My suggestion, using (currentDate()), does add a range to the query to database. If you're fetching all data and filtering them in the application layer, you're doing something completely different then I told you (and I agree that it's a complete waste of resources).
Computed columns are very powerful and I personally use them a lot, but they they're not necessary for simple filtering.
Background info:
The structure is like this:
The final view (SalesLineCosts_View) is for determining the costs of sales lines delayed in shipment. The requirement also needs to convert the sales prices to the accounting currency (CurrXR_View).
The currentDate comparison is required in two places - one is simply to filter the sales lines for delayed shipment.
The other is used to find the correct exchange rate for sales price conversion (given exchange rates are re-evaluated often enough)
For the first comparison, you are right is can quite easily be done in X++ code.
For the second, the cross join between SalesLine and CurrXR can become needlessly huge, bogging down the performance of the entire query (if not filtered at query/view stage itself).
Filtering this via X++ leads the report to take ~ 4 min (240 sec) to run.
Adding the Computed Column for getdate() and putting a range when joining with CurrXR_View : ~ 10 sec.
----------
To be honest, I didn't want to add an extra column. which is why i asked if there was any way to create the query/view in a way so that GetDate() will appear in the SQL definition of the view....
There is a class "SysQueryRangeUtil" and method currentDate().
Using today() in query range value worked for me. Its filtering the data as per current date in view.
I wanted to suggest computed columns if I fail to convince you to use the easier way in AX. Also, adding a column containing the current date isn't the same as filtering values of another field, so there would be additional work to implement your original requirement. When you will add a range anyway, why don't you simply filter by (currentDate()), without having to develop any computed columns?
When you need it inside a view, we're not talking about "an equivalent of SQL GetDate" anymore - we're talking about SQL GetDate itself. That's exactly what your computed column does.
Hi Martin...
Yeah I know...
So there is no way to create an AOT view with the GetDate() range (when synced).
Found an easier workaround - I added GetDate() as a computed field to another view used in the query. Now I just use this new field in the range.
In normal queries, you can use functions defined in SysQueryRangeUtil class, such as setting a query range value to (currentDate()).
But it wouldn't help you with a view. A view is basically a piece of T-SQL code and can't call X++ methods.
I suggest you don't try to filter inside the view. Filter the results of the view, possibly with (currentDate()).
You can use today() or SystemDateget() methods.
Share
Report
All responses (
Answers (
