Contact us today: (866) 4-WEBSAN (932726)                Get Instant Support

View our free webinarsRead more about Hosted Dynamics GPDownload a FREE Demo

When organizing your business’ data and sorting through multiple categories and headings, it’s easy to get overwhelmed with the time-consuming process. Structured Query Language (SQL) used in Microsoft Dynamics GP serves to retrieve information from databases through the use of basic statements and clauses. Here are some SQL features that can help you sort through data quickly in Microsoft Dynamics GP:

  • Select clause – Allows you to pull and read data from the database. Often using the Select * format, this statement enables the user to select data from columns and tables such as a list of customers.
  • Where clause – Used to filter your list based on a certain condition, such as to specify a customer’s country or location.
  • Alias syntax – Enables the user to rename a database table or a column in a table. This function becomes especially useful when there are multiple tables in a query or when column names are long and not easily readable.
  • Join clause – Helps to combine and add more columns in your data. The join clause is often used to combine fields from two tables by using values that are common to each. 
  • Group by clause – Allows you to compress, consolidate and summarize data. This clause is used together with aggregate functions to group a result set into one or more columns.

To learn more about SQL queries and common functions in Microsoft Dynamics GP, register for our online course! 

Register for SQL 100 course in WebSan University

 

Linz Tan, Web Marketing Assistant, WebSan Solutions Inc. a 2014 Ontario Business Achievement Award Winner for Service Excellence.

Published in Dynamics GP

Learn more about Dynamics GPView Dynamics GP PricingFree Dynamics GP Training

Incrementing an integer in SQL is easy; all you have to do is + 1 to the value. However, incrementing a number that has padded zeros is a little trickier, since it is probably a string value and not a number. Luckily with some type conversions, it becomes just as easy as incrementing an integer.

First thing you want to do is to get the length of the string, so you know how many padded zeros to add. You can do this by using the LEN function:

1. SELECT @Length = LEN(RTRIM(LTRIM(@Num)))

It is also a good idea to trim the value just in case there are extra spaces.

Next we can increment the number.  Now we can't just do a + 1 to the value since it’s a string and doing a + 1 to a string would just concatenate the 1 to the end of the string.  So let’s convert the string to an integer:

2. SET @tempNum = CONVERT(INT, @Num) + 1

Nice, now we have incremented the number. Now all we have to do is add those zeros back in and we're done.  To do this, we first convert the number back to a string.  Then we concatenate a bunch of zeros to the front of the number.

Last, using the RIGHT function we set the length of the string back to the original length, using the length we got earlier, so that we have the appropriate number of leading zeros.  Here's how it looks:

3. SET @nextNum = CONVERT(VARCHAR(21), @tempNum)

SET @nextNum = RIGHT('000000000000000000000' + @nextNum, @Length)

There you have your incremented number!

By: Dustin Yee, Application Developer, WebSan Solutions Inc., a Microsoft Dynamics Silver Partner and a CDN top 100 solution provider for 2012.

Published in Uncategorized
Monday, 11 June 2012 09:15

SQL Reporting Webinar

On May 29th WebSan held a SQL Reporting Services Webinar. We discussed the ability to export reports to any other formats like Word, Excel, PDF etc. and we toured of the most popular SQL Reports. To be kept up to date latest webinars, send us a message or e-mail us at This email address is being protected from spambots. You need JavaScript enabled to view it.

You can view the recorded webinar and other Microsoft Dynamics GP training videos at our YouTube Channel.

Join us for our upcoming June webinars!

-          Move from QuickBooks to Microsoft Dynamics GP on June 21 @ 2:00 p.m. EST

-          10 Tricks in 30 minutes – The Top 10 Features of Dynamics  GP You’re Not Using and Should Be! On June 19 @ 11:00 a.m. EST

Published in Uncategorized