T-SQL: MAX vs. TOP 1 - Which is better??

By Steve Endow

If you need to get the largest value for a field, should you use MAX in your query?  Or should you use TOP 1 with ORDER BY?

Which is better?  Which is faster?  Is that always true?

Do you think you know the answer?

Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.

The results may surprise you!

Did I miss anything or make any mistakes in my testing?  Are there other considerations when choosing between MAX vs. TOP 1?

How to improve Dynamics GP with a little bit of VBA

My blog has moved!  Please visit the new blog at:  https://blog.steveendow.com/

I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com

Thanks!


By Steve Endow

I've had a few Dynamics GP customers that purchase software from me every few years, and a few of them have mailed checks to my old mailing address from 4 years ago. How can this happen?

Well, the Dynamics GP Payables Transaction Entry window does not display the vendor Remit To address, so verifying the vendor address is not an obvious natural step in the invoice entry process.  Yes, there is a link to open the Vendor Address Maintenance window, but what if internal controls prevents the user who enters vendor invoices from editing vendor addresses?  The user would need to go through a separate process to verify the vendor address...for every invoice.  Not ideal.

How can VBA help?

In just a few minutes, VBA can be added to the Payables Transaction Entry window to check if the vendor has not had a transaction in over 60 days, prompt the user to verify the vendor address, and even open the Vendor Inquiry window to review the current Remit To address in Dynamics GP.

It's really easy!

Here's a video discussing the background and walking through the entire process of adding the VBA to Dynamics GP.

First, within Dynamics GP, you add the desired windows to Visual Basic by clicking on Tools -> Customize -> Add Current Window to Visual Basic.

After the window is added, I click on Add Fields to Visual Basic and then click on the Vendor ID field.

Since I am assuming that the user entering invoices will not have access to edit vendors or vendor addresses, I'm going to add the Vendor Inquiry window to Visual Basic and add 3 fields on the Inquiry window to VB:  Vendor ID, Address ID, and the Address ID Next button.

Once I have those windows and fields added to Visual Basic, I press CTRL+F11 to open the Dynamics GP Visual Basic Editor.  If you don't have access to the VB Editor, you may not be licensed to use it, or you may not have permissions--in which case, talk with your GP administrator or GP partner.

In the VB Editor window, I'll select the PayablesTransactionEntry window on the left, then select the VendorID field and the AfterUserChanged event.

I wrote the simple VBA code below to demonstrate how you can quickly and easily add some VBA to add some valuable functionality to Dynamics GP to save users time and improve data entry.

The code finds the most document date for any vendor transaction in Dynamics GP, and if that date is over 60 days ago, it opens the Vendor Inquiry window and displays the vendor Remit To address for the user to review and verify.

Private Sub VendorID_AfterUserChanged()

       

    Dim strVendorID As String

    Dim strSQL As String

    Dim dtLastDocDate As Date

   

    strVendorID = VendorID.Value

   

    'Find the most recent document date for the vendor

    strSQL = "SELECT COALESCE(MAX(DOCDATE), '1900-01-01') AS DOCDATE FROM PM00400 WHERE VENDORID = '" & strVendorID & "'"

   

    Set oConn = UserInfoGet.CreateADOConnection

    oConn.DefaultDatabase = UserInfoGet.IntercompanyID

    Set rsResult = oConn.Execute(strSQL)

   

    strLastDocDate = Trim(rsResult.Fields("DOCDATE").Value)

   

    rsResult.Close

   

    'Get the Remit To Address ID for the vendor

    strSQL = "SELECT VADCDTRO FROM PM00200 WHERE VENDORID = '" & strVendorID & "'"

    Set rsResult = oConn.Execute(strSQL)

   

    strRemitID = Trim(rsResult.Fields("VADCDTRO").Value)

   

    rsResult.Close

    oConn.Close

       

    dtLastDocDate = CDate(strLastDocDate)

   

    'If Doc Date is 1/1/1900, vendor has no transactions

    If dtLastDocDate = CDate("1900-01-01") Then

        Exit Sub

    Else

       

        intDays = DateDiff("d", dtLastDocDate, DateTime.Date)

       

        'If the last doc date is > X days ago, display a dialog

        If intDays > 60 Then

       

            msgResult = MsgBox("This vendor has not had a transaction since " & strLastDocDate & " (" & intDays & " days ago)." & vbNewLine & vbNewLine & "Please review the current vendor Remit To address and compare to the invoice address", vbOKOnly, "Verify Vendor Address")

            VendorInquiry.Open

            VendorInquiry.VendorID.Value = strVendorID

            VendorInquiry.Activate

            VendorInquiry.Show

           

            While VendorInquiry.AddressID.Value <> strRemitID

                VendorInquiry.NextButtonWindowArea.Value = 1

            Wend

       

        End If

    End If

End Sub

In just a few minutes, you can have this customization running in Dynamics GP without any additional development tools.

If you have more complex requirements, you can easily add more advanced functionality using VBA.  If you prefer using a separate development tool, you could also develop this customization using .NET or Dexterity, but the appeal of VBA is its simplicity and ease of use.

So if you have some small problem or additional business requirement that you'd like to handle in Dynamics GP, VBA might come in handy.

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com

Convert DEX_ROW_TS to a local time zone using AT TIME ZONE

By Steve Endow

This week I attended another great webinar by Kendra Little of SQLWorkbooks.com.  (If you aren't familiar with Kendra, check out her free webinars and her excellent catalog of online courses.)

One neat thing that Kendra always seems to do in her webinars and courses is subtly use new(er) SQL Server features.  This week, she just happened to use the AT TIME ZONE statement in one of her queries.  As soon as I saw it, I knew I had to try it with Dynamics GP.

Dynamics GP doesn't have much time zone sensitive data, but one field that I am starting to rely on more frequently is the DEX_ROW_TS field, which is now present in several key GP tables.  This field stores a last updated date time stamp.

DEX_ROW_TS is a bit unique for GP for at least two reasons.  First, it's a rare time stamp field.  While GP has many date fields, those date fields normally have a time of 00:00:00.000--so it's just a date at midnight, with no timestamp.

The second unique thing about DEX_ROW_TS is that it stores the datetime with a UTC timezone offset.  So if you ever query the DEX_ROW_TS field you need to remember that it isn't local time.

I previously wrote a post about DEX_ROW_TS and how to use some SQL date functions to convert the value to your local time zone, but that approach felt a bit like duct tape and twine, and I would have to look up the syntax every time to use it.

Enter the very cool SQL Server 2016 AT TIME ZONE function.  This function makes it very easy to assign a time zone to a datetime value, and then convert it to another time zone.

(I'm calling AT TIME ZONE a function for now because I haven't found a better name for it. It doesn't read like a typical function, but it acts like one, so Function is the best name I have so far. If you know of the proper technical name for it, let me know in the comments below.)

My latest rookie SQL mistake...

By Steve Endow

I just discovered a fun mistake that I made in a SQL script.  It's a rookie mistake, but it's one of those somewhat novel mistakes that I think is easily missed in many projects.

I developed a Dynamics GP SOP Invoice import for a customer using .NET and eConnect.  It has been in use for over 3 years and working great, but recently they finally had a scenario where they uncovered the latent bug.

After reviewing my code and looking at the data that triggered the bug, I found that I had a design flaw in a SQL statement.  The flaw wasn't discovered during testing because I never anticipated a specific use case and boundary condition, so I never tested the scenario, and it took over 3 years for the customer to encounter it.

The customer is unique in that they will import an invoice, such as invoice number 123456, that relates to contract number 123456.  Then a few days later they will need to make an adjustment to the contract, so they will issue a related invoice to add services to the contract.  To help track the related transaction, the new invoice is imported into GP with a suffix on the invoice number, such as 123456-1.  A few days later, they will issue a credit memo to decrease the contract amount, and that CM will be imported as document number 123456-2, etc.  These numeric suffixes are added to the document number by the eConnect import.

Last week, the customer emailed me with a problem.  They were getting this eConnect error:

Are you "closing the loop" with your Dynamics GP system integrations?

By Steve Endow

I've been developing system integrations for so long that I sometimes forget that some parts of system integration design may not be obvious to customers I work with.

I have been working with a company that is integrating their document management solution into Dynamics GP.  Their software will capture a scan of a document, such as a vendor invoice, their workflow will route the document image, a user will verify the vendor and the expense accounts, and once the workflow is complete, the software will import the invoice data into Dynamics GP using eConnect.  Once the invoice is imported, a GP user can view the Payables Transaction invoice in Dynamics GP, and also open the scanned invoice image directly from GP.

Okay, that sounds pretty straightforward...

The initial version of the integration with Dynamics GP is asynchronous, using XML files.  The document management system exports an XML file containing the "metadata" for the vendor invoice, and the GP import is a scheduled task that regularly picks up those XML files and imports the data into Dynamics GP as transactions.  (Aside: This design was chosen for its simplicity, as the initial version of the integration was a prototype--a proof of concept to explore how the system could be integrated with Dynamics GP.)

Okay, so...your point is...?

Are SQL Server subqueries bad? Let's find out!

By Steve Endow

For the past several years, I've noticed that I have generally avoided using subqueries, based on a suspicion that they are probably less efficient than a JOIN.  I do still use them for one-time or ad-hoc queries where performance isn't a concern, but I have been avoiding them for any production queries that I release.  But I haven't done any research to support my suspicion.

This Saturday morning, while working on another SQL query optimization issue, I figured I would try a test to compare the performance of a simple subquery vs. a JOIN.

What do you think?  Do you think that subqueries are typically slower than JOINs?  If so, how much slower?

Here's a YouTube video where I test my sample queries and review the results.

Before doing my test, I searched the interwebs and I found a post (unfortunately the images aren't showing for me) that appears to definitively demonstrate the correlated subqueries perform much worse than similar JOIN queries.  The explanation made sense.

To qualify my test setup: These queries were run on SQL Server 2014 SP1 using Management Studio 17.4 against the GP 2016 Fabrikam/TWO database. And also notable is that I have 73,752 records in my SOP30300 table--which is probably quite a bit more than a vanilla TWO database.  I suspect this is important, as the results may be different for other SQL Server versions, and may vary based on the number of tables and records.

Dynamics GP Integrations: Eliminate the need for perfection

By Steve Endow

I had a call with a customer this morning to review an error they were seeing with their AP Invoice integration.  The custom integration is moderately complex, importing invoices from 90 retail locations into 30 different Dynamics GP companies, with Intercompany distributions.

There's an interesting twist to this integration.  The invoice data contains a retail store number, but it doesn't tell the import which GP company the invoice belongs to.  And there is nothing in the GP companies indicating which retail stores belong to which database.  Some retail stores have their own dedicated GP company database, while other retail stores are managed together in a GP company database.  The users just know which retail store belongs to which GP company.

So how does the integration figure out which company an invoice belongs to?

We could have created a mapping table, listing each retail store ID number and the corresponding GP company database.  But the problem with mapping tables is that they have to be maintained.  When a new retail store is opened, or a new GP database is created, users will invariably forget to update the custom mapping table.

So for this integration, I tried something new.  The one clue in the invoice data file is a GL account number.  The first segment of the GL account is a two digit number that uniquely identifies the Dynamics GP company.  Like this:

01 = Company A
03 = Company B
06 = Company C
25, 31, 49 = Company D

So, the integration reads the GL expense account assigned to the invoice, and uses that to determine which company the invoice belongs to.

When the integration launches, it queries all of the GP databases to determine which Segment 1 values are used in each database.

DECLARE @INTERID varchar(10) = ''

DECLARE @SQL varchar(MAX) = ''

DECLARE INTERID_cursor CURSOR FOR

SELECT INTERID FROM DYNAMICS..SY01500

OPEN INTERID_cursor

FETCH NEXT FROM INTERID_cursor INTO @INTERID

WHILE @@FETCH_STATUS = 0

BEGIN

       IF @SQL <> '' BEGIN SET @SQL += ' UNION '; END

       SET @SQL += ' SELECT ''' + @INTERID + ''' AS INTERID, (SELECT COUNT(DISTINCT ACTNUMBR_1) FROM ' + @INTERID + '..GL00100) AS Segment1Values, (SELECT TOP 1 ACTNUMBR_1 FROM ' + @INTERID + '..GL00100) AS CompanyID';

       FETCH NEXT FROM INTERID_cursor INTO @INTERID

END

CLOSE INTERID_cursor

DEALLOCATE INTERID_cursor

EXEC(@SQL)

It is then able to use this "mapping" to match invoices to databases based on the GL expense account.

But, this scheme is based on the critical assumption that in Company A, every single GL account will always have a first segment value of 01.  And Company B will always have a segment 1 value of 03.  Or Segment 1 value of 25, 31, and 49 will only ever exist in Company D.  For every account.  No exceptions.

I'll let you guess what happens next.

A user enters a "06" account in Company A.  And another user enters a "01" account in Company B.

Despite the customer's insistence that this would never happen, and that they always make sure that only one unique Segment 1 value is used in each company, someone ends up entering a Segment 1 value in the wrong company.

Am I surprised by this?  Not at all.  Whenever the word "never" is used during integration design discussions, that's always a clue.  I substitute it with "usually" or "mostly".  There are almost always exceptions, whether intentional or unintentional.

So now what?  If the program can't ensure that the Segment 1 values are unique to each company, what can it do?

Well, the second layer is that during the import process, the integration checks the destination company database to verify that the GL account exists.  If it queries Company A for a 06 GL account and doesn't find it, it logs an error and that invoice isn't imported.  This is the error that was logged this morning.

But then what?  The customer insists, again, that they only use the 06 accounts in Company C, so the import must be wrong.  So we run the above query again and find that someone accidentally entered a 06 account in Company A, which confused the import.  And the customer is shocked that such a mistake could happen.  For the third time.

But I'm not satisfied with this process.  Because 6 months from now, it's going to happen again.  And they'll blame the integration again.  And we'll have to manually run the query again and find which account was added to the wrong company.

So let's just assume that this mistake is going to continue to happen and deal with it.  I'm thinking that I need to modify the integration to have it review the results of the query above.  If it finds that 06 is present in more than one GP database, it needs to log an error and let the user know.

"Hey, I found account 06-5555-00 in Company A. That doesn't look right. Please look into it."

This will proactively identify that an issue exists, identify the specific account, identify the company, and give the user enough information to research and resolve the problem.

It assumes the mistake will happen. It eliminates the need for perfection in a complex process in a moderately complex environment, where employees have 90 other things on their minds.  And it should only take a few lines of code--a one time investment that will save time for years into the future.

So why not do this for other possible exceptions and issues?  If you can identify other potential mistakes or errors, why not just code for all of them?  Because there are endless possible exceptions, and it would cost a fortune to handle all of them, most of which will never occur.

I usually release an initial version of an integration, identify the exceptions, and quickly handle errors that do occur.  When a new exception comes up, handle it.  It's usually a surprisingly small number, like 3 or 5 different data issues that cause problems.

So that's my philosophy: Eliminate the need for perfection in integrations whenever possible or practical.

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com