다음을 통해 공유

SmartList Builder based on SQL View not returning data

  • 아티클

David Meego - Click for blog homepageI have yet another interesting case to discuss with you. The partner had created a set of views into the SQL data with the final view being displayed as a SmartList after being added using SmartList Builder.  This post is not going to cover how to use SmartList or SmartList Builder, but instead focus on the problem we were seeing.

When the SmartList was selected, the data in some of the columns was being incorrectly returned as zero.  If we looked at the same View using a SQL Query, the columns had values. 

Interestingly enough, looking at the same View using the SQL Execute window in the Support Debugging Tool for Microsoft Dynamics GP gave the same results as the SmartList. 

What this told me was that when the View was being used from inside Dynamics, the behaviour was different to outside Dynamics (ie. SQL Enterprise Manager).

To troubleshoot this issue further I started to look at the component Views (the Views that were being used to build the final View).  I took the SELECT statements that the Views were using and started breaking them down.

By executing them in both SQL Enterprise Manager and the Support Debugging Tool's SQL Execute window, I was able to see what works in SQL and fails inside Dynamics. What I found was that the code that was being used to place a transaction into aging buckets worked fine as a SQL Query, but was failing when executed from Dexterity.

Below is an example SELECT statement that will work under SQL Enterprise Manager but fail in SQL Execute:

Example Original Select Statement on which view was based.

SELECT SOPTYPE, SOPNUMBE, DOCDATE, SUM(DOCAMNT) AS TOTALAMNT,
  CASE WHEN DOCDATE BETWEEN DATEADD(DAY, -30, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1))) AND DATEADD(DAY, 0, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_1_30',
  CASE WHEN DOCDATE BETWEEN DATEADD(DAY, -60, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1))) AND DATEADD(DAY, -31, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_31_60',
  CASE WHEN DOCDATE BETWEEN DATEADD(DAY, -90, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1))) AND DATEADD(DAY, -61, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_61_90',
  CASE WHEN DOCDATE < DATEADD(DAY, -90, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_OVER_90'
FROM SOP30200
GROUP BY SOPTYPE, SOPNUMBE, DOCDATE, DOCAMNT

I was able to identify that the commands being used to trim the DATETIME result returned by the GETDATE() function to obtain just the date portion for today's date were failing.  Once they failed any data based on the failed column would return NULL. Dexterity does not understand NULLs and so just treats they as empty or zero values.

The example statements below show how the date can be converted to a string which does not include a time portion and then be converted back to a date.  The issue was that under Dexterity the date format MM/DD/YYYY fails to convert back to a datetime when the format YYYYMMDD works.

Example Date Conversion Commands

select CONVERT(VARCHAR,GETDATE(),1) -- returns in format MM/DD/YYYY
select CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1)) -- This fails under Dexterity
select DATEADD(DAY, -30, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1))) -- So this fails under Dexterity

select CONVERT(VARCHAR,GETDATE(),112) -- returns in format YYYYMMDD
select CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112)) -- This works for both
select DATEADD(DAY, -30, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112))) -- So this works for both

So, by changing the convert format code from 1 (MM/DD/YYYY) to 112 (YYYYMMDD) we were able to create a view that worked regardless of where it was executed from.

Below is the updated example SELECT statement: 

Example Updated Select Statement

SELECT SOPTYPE, SOPNUMBE, DOCDATE, SUM(DOCAMNT) AS TOTALAMNT,
  CASE WHEN DOCDATE BETWEEN DATEADD(DAY, -30, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112))) AND DATEADD(DAY, 0, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_1_30',
  CASE WHEN DOCDATE BETWEEN DATEADD(DAY, -60, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112))) AND DATEADD(DAY, -31, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_31_60',
  CASE WHEN DOCDATE BETWEEN DATEADD(DAY, -90, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112))) AND DATEADD(DAY, -61, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_61_90',
  CASE WHEN DOCDATE < DATEADD(DAY, -90, CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112)))
    THEN SUM(DOCAMNT)
    ELSE 0.0
  END AS 'DAYS_OVER_90'
FROM SOP30200
GROUP BY SOPTYPE, SOPNUMBE, DOCDATE, DOCAMNT

So... Problem solved.

The important point to highlight here is that the SQL environment from inside Dynamics is not exactly the same as from SQL Enterprise Manager.

One example of this is that from inside Dynamics GP ANSI_NULLS and ANSI_WARNINGS are set to OFF, when they are normally ON in SQL Enterprise Manager.  This can cause issues which I will discuss in an upcoming post.

Watch this space.

David

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)

Comments