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!
Linz Tan, Web Marketing Assistant, WebSan Solutions Inc. a 2014 Ontario Business Achievement Award Winner for Service Excellence.
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!
You can view the recorded webinar and other Microsoft Dynamics GP training videos at our YouTube Channel.
Join us for our upcoming June webinars!