Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 310 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Problem with some views in AX 2012 (PRODCALCTRANSEXPANDED)
icemokka
Member Posts: 7
Hi,
I'm installing my first AX 2012 (CU1) @ a customer site and stumbled on problem processing the OLAP cubes.
I've followed the error to it's source and appearantly it's a view that won't work ( Data convertion error from nvarchar to string)
In the view PRODCALCTRANSEXPANDED, there is fixed date (N'24/10/2011') that should be converted to DateTime.
First of all, because I'm on a European server with DD/MM/YYYY settings, it writes the date the same in T-SQL. But in T-SQL, it's always MM/DD/YYYY. This is a bug I presume. Setting my server to US dateformat,synschronising the view and the error is gone.
But then I tried to understand the logic of this view, the date is updated every time you do a synchronise of that view. Meaning if you don't synchronise very often and Prodstatus is not 5 or 7, you production-order is not delayed ( DELAYED = 0 ). Why doesn't they use GETDATE() or am I missing something here?
The same for view PRODROUTETRANSEXPANDED, same bug and some illogical use of a fixed date.
This is the piece of code that holds that fixed date in the first view.
CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < N'24/10/2011')
THEN 1 WHEN ((T2.PRODSTATUS = 5) OR
(T2.PRODSTATUS = 7)) AND (T2.DLVDATE < T2.FINISHEDDATE) THEN 1 WHEN ((T2.PRODSTATUS = 5) OR
(T2.PRODSTATUS = 7)) AND (T2.DLVDATE >= T2.FINISHEDDATE) THEN 0 ELSE NULL END) AS INT) AS DELAYED
T2 is PRODTABLE in this piece of T-SQL.
Any idea's?
Regards,
Sven Peeters
BELGIUM
I'm installing my first AX 2012 (CU1) @ a customer site and stumbled on problem processing the OLAP cubes.
I've followed the error to it's source and appearantly it's a view that won't work ( Data convertion error from nvarchar to string)
In the view PRODCALCTRANSEXPANDED, there is fixed date (N'24/10/2011') that should be converted to DateTime.
First of all, because I'm on a European server with DD/MM/YYYY settings, it writes the date the same in T-SQL. But in T-SQL, it's always MM/DD/YYYY. This is a bug I presume. Setting my server to US dateformat,synschronising the view and the error is gone.
But then I tried to understand the logic of this view, the date is updated every time you do a synchronise of that view. Meaning if you don't synchronise very often and Prodstatus is not 5 or 7, you production-order is not delayed ( DELAYED = 0 ). Why doesn't they use GETDATE() or am I missing something here?
The same for view PRODROUTETRANSEXPANDED, same bug and some illogical use of a fixed date.
This is the piece of code that holds that fixed date in the first view.
CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < N'24/10/2011')
THEN 1 WHEN ((T2.PRODSTATUS = 5) OR
(T2.PRODSTATUS = 7)) AND (T2.DLVDATE < T2.FINISHEDDATE) THEN 1 WHEN ((T2.PRODSTATUS = 5) OR
(T2.PRODSTATUS = 7)) AND (T2.DLVDATE >= T2.FINISHEDDATE) THEN 0 ELSE NULL END) AS INT) AS DELAYED
T2 is PRODTABLE in this piece of T-SQL.
Any idea's?
Regards,
Sven Peeters
BELGIUM
0