Finding Your SQL Server Version

On two occasions now I have written somewhat elaborate queries only to have them fail on the client side because they were running under the Microsoft SQL Server Desktop Edition (MSDE) product that originally shipped with Shelby v.5 many years ago. That edition of SQL Server emulated SQL Server 2000, and it lacks some key support features that were released in SQL Server 2005, let alone any further enhancements in even more recent editions.

Here is a SQL script to reveal which version and edition of SQL Server you are running:

select top 1 Version = @@Version, Edition = serverproperty('edition') from NANames

If the version information is not self-evident, compare the version number against the list at http://www.sqlteam.com/article/sql-server-versions in order to determine which version you are running.

After you know your current version, check out the Hardware Requirements page from Shelby v.5 Support. If you are not meeting at least the minimum recommended requirement for SQL Server, seriously consider an upgrade.

By the way, the reference to NANames in the query is only there to "trick" ShelbyQUERY into running the query. Without some kind of FROM clause, some releases of ShelbyQUERY will refuse to run the query, since it is not always recognized as a standard "SELECT" query without one. There is nothing particular about the NANames table; any table could be used in its place, and the same results would appear.

Relating AP tables to GL tables in Shelby v.5 data

I've been working on a query to correlate General Ledger detail lines to the corresponding Accounts Payable invoice detail lines. This sounds pretty straightforward; however, it is not. The only foreign key relationship available is at the top level: AP invoice header to GL entry header. This means that tying the next level down (detail-to-detail) involves matching up multiple detail values in addition to the header foreign key value. Unfortunately, because the customer I am working on this for has data entry practices that allow for multiple identical detail lines even on the same invoice, I have no truly unique key to work with. If I resolve this, I will post the finished join relationship.

On a related note, however, I have run across an important detail regarding the use of the GLEntriesCounter column, which appears in both the APInv table and the APInvDetail table. I have learned that APInv.GLEntriesCounter is a reference to the GLEntries.HeaderCounter for the rows created when the invoice is posted. APInvDetail.GLEntriesCounter is a reference to the GLEntries.HeaderCounter for the rows created when the invoice is paid. For reporting on accural-based companies, this distinction is critical.

If the invoice has not been paid, the APInvDetail.GLEntriesCounter value will be zero (0). If a company is on a cash basis, I would assume that the APInv.GLEntriesCounter value would be zero, though I have not tested this assumption. (If someone who is on a cash AP basis would check this out and post a confirmation or correction, I'll edit this post to give the definitive answer.)

Checking for NULL in SQL Server Reporting Services

Today I was tasked with replacing blank values on a report output with a double-dash. This was on a SQL Server Reporting Services (SSRS) report design I had made.

I knew this would involve an IIF() conditional. But I was not sure how to phrase the condition to check for the NULL value. I tried two approaches off the top of my head.

First, I tried the SQL syntax of Fields!FieldName.Value IS NULL. It was a long shot, and it didn't work. No real surprise, there, so I moved on.

Second, I tried the VBScript approach of IsNull(Fields!FieldName.Value). I was pretty sure this would work, because I have used some VBScript commands successfully with conditional formatting in SSRS before. However, this didn't work either.

What became apparent was that I needed to understand how to do this kind of condition in .NET, which is the actual programming language supported inside of SSRS for formula expressions. I did some searches on the Internet and eventually found the right syntax. It turns out that IsNothing() is the .NET equivalent to the VBScript function IsNull(). The working syntax for my report requirement is:

= IIf(Not IsNothing(Fields!FieldName.Value), Fields!FieldName.Value, "--")

Pivoting Rows to Columns - Part 3

In this last installment of this series on pivoting rows into columns, I will show you how to use GROUP BY and CASE together to achieve the desired result. The specific example will take Profile codes from the NAProfiles table of the Shelby v.5 database and pivot them into columns.

Here is the basic query without pivoting, showing each name in the database along with Profile codes:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Profiles.Profile

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

This generates a list of names and Profile codes, but a person with three codes will be listed three times. What many people want is to list the person once, with a column for each Profile code of interest. The first step in that direction for the approach today is to add a GROUP BY clause and an aggregate function for the Profile code:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Profile = max(Profiles.Profile)

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

group by

Names.NameCounter,

Names.FirstMiddle,

Names.LastName

Now we are back to one line per person, but with only one Profile in the results. Because of the MAX() function, we have the Profile that comes last alphabetically for each person. To gain control over which Profile shows up, add a CASE statement that returns only the desired value inside of the MAX() function:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Profile = max(case Profiles.Profile when 'LASTCN' then Profiles.Profile end)

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

group by

Names.NameCounter,

Names.FirstMiddle,

Names.LastName

This returns only the LASTCN Profile code in the Profile column of the results. Now that you have a method of getting the info from NAProfiles on just row(s) you want, you can use a variety of NAProfile columns and column aliases to get the desired results. You can even substitute your own marking as the output of the CASE statement, such as printing an "X" to indicate a match.

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

LastCNDate = max(case Profiles.Profile when 'LASTCN' then convert(varchar, Profiles.Start, 101) end),

AllergyInfo = max(case Profiles.Profile when 'ALLERG' then Profiles.Comment end),

OnReachingTeam = max(case Profiles.Profile when 'ZRREACH' then 'X' end)

from

Shelby.NANames as Names left join

Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

group by

Names.NameCounter,

Names.FirstMiddle,

Names.LastName

This approach works best when you have only one join that creates a one-to-many relationship, such as NANames to NAProfiles. If you have several joins of that type, then one of the first two approaches (discussed in the prior two blog posts) would probably work better. But when you do have just one such join, this is perhaps the easiest way to move rows to columns.

Pivoting Rows to Columns - Part 2

The last post looked at how to pivot rows to columns using multiple joins to the same table. In this post I will explain how to pivot using multiple subqueries in the SELECT clause of the query.

I will also be using a special "view" object in the example solutions. Views are essentially the results of queries that have been saved into the database in such a way that you can use the sets of results just as you would use a table. The view I'm going to use is called Shelby.VIEW_SHELBY_CUSTINFO_ALL. This is a view of the custom information collected on the Custom Tabs of the GlobaFILE module in Shelby v.5. Each row of this view represents one custom value for one name entry in the NANames module.

Here is an example of results from the Shelby.VIEW_SHELBY_CUSTINFO_ALL view:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL

Now, let's limit this down to just the Spiritual Gifts tab and the Teacher check box option on that tab:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher'

So far, so good. Bear with me just a moment as we follow this train of thought two steps further. Let's further limit this down to the value of the Teacher field for just one person. For this example, I will limit it down to the person with a NameCounter value of 8:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = 8




Now, one last refinement before we talk about how to use this info in a subquery. Let's just get the one column of information we really want out of this view, the actual "true/false" value of the field.

select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = 8

This does not look like much at all, but it is precisely what we need to know about the custom value of the "Teacher" field for one person. (The negative one represents "true," meaning that this person has the check box checked on his or her record.)

Now we can look at how to meld this information as a subquery into a main query that pulls the main name information out of the database.

In case you have not used subqueries in the SELECT clause of a query before, here is a run-down of the basic principles:

• A subquery uses the same syntax as a regular query.
• A subquery can have its own SELECT, FROM, WHERE, GROUP BY, and HAVING clauses, just like a regular query.
• A subquery that is returning a value to a SELECT column must return exactly one value each time it executes: one column and one row only.
• A subquery may be correlated to the primary query by using one or more columns from tables in the primary query as part of conditions in the WHERE clause of the subquery.

What we need to do it correlate the query we wrote above with another query that pull name information out of the database. Note the bold text in the following query, which shows how the correlation is happening:

select

n.NameCounter,

n.FirstMiddle,

n.LastName,

SpiritualGift_Teacher = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = n.NameCounter)

from

Shelby.NANames as n

What is happening is that for each row of results the subquery executes, substituting the value of the NameCounter from NANames on that row for the WHERE condition in the subquery. Once we have one subquery added, it is quite easy to add more to pull additional custom values into the results:

select

n.NameCounter,

n.FirstMiddle,

n.LastName,

SpiritualGift_Teacher = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = n.NameCounter),

SpiritualGift_Encouragement = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Encouragement' and CustomInfo.NameCounter = n.NameCounter),

SpiritualGift_Service = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where

CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Service' and CustomInfo.NameCounter = n.NameCounter)

from

Shelby.NANames as n

In case you would like a comparison, here is the phone number example from the previous post done with this subquery technique:

select

n.NameCounter,

n.FirstMiddle,

n.LastName,

MainPhone = (select p.PhoneNu from Shelby.NAPhones as p where p.PhoneCounter = 1 and p.NameCounter = n.NameCounter),

BusinessPhone = (select p.PhoneNu from Shelby.NAPhones as p where p.PhoneCounter = 2 and p.NameCounter = n.NameCounter)

from

Shelby.NANames as n

Pivoting Rows to Columns - Part 1

Shortly after learning how to query the tables of a database, the first major hurdle many people find is in taking rows of results and "pivoting" them into columns. In the Shelby v.5 world, some of the most common results that people want this way include reporting one column per:

• phone number type
• custom field value
• Profile code

In this post and the next two, I will show three different ways to pivot rows into columns. Each of the three methods has its strengths and weaknesses, so understanding each technique will help you pick the best one for the query you are writing at the time.

The first method is joining to the same table multiple times, once for each separate column you want. This is very similar to the method of joining to the same table more than once that I described in the earlier post on combining husband and wife together on the same row of results. The only difference here is that, instead of just one additional join, there will need to be as many joins as there are columns of data types.

As a starting point of explanation, let me share with you a query that returns a list of names and phone numbers. This list has one row for each person, and an additional row for every phone type after the first one. If the person has three phone numbers in the database, the person will be listed three times.

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Phones.PhoneNu

from

Shelby.NANames as Names left join

Shelby.NAPhones as Phones on Names.NameCounter = Phones.NameCounter

This would yield the following snippet of results:

Notice how many people are listed multiple times.

In Transact-SQL, the flavor of SQL that runs on SQL Server, we can add an additional restriction onto the join condition between NANames and NAPhones. This additional condition can "pre-filter" the NAPhones table so that it just returns, say, the Main/Home phone type. With such a filtering condition in place, we can once again get just one row per name, since no one has more than on Main/Home phone. I will do this in the query by limiting the NAPhones table to only the rows with a PhoneCounter of 1, which I know represents the Main/Home phone type. Here is the query:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

Phones.PhoneNu

from

Shelby.NANames as Names left join

Shelby.NAPhones as Phones on Names.NameCounter = Phones.NameCounter and Phones.PhoneCounter = 1

and here are the results:

Now that I can control exactly which phone type is returned by the NAPhones table in any given join to it, I can simply add more joins and filter each one to a specific type of phone number. In the next query I get both the Main/Home phone number and the Business phone number, which always has a PhoneCounter value of 2:

select

Names.NameCounter,

Names.FirstMiddle,

Names.LastName,

MainPhones.PhoneNu as MainPhone,

BusinessPhones.PhoneNu as BusinessPhone

from

Shelby.NANames as Names left join

Shelby.NAPhones as MainPhones on Names.NameCounter = MainPhones.NameCounter and MainPhones.PhoneCounter = 1 left join

Shelby.NAPhones as BusinessPhones on Names.NameCounter = BusinessPhones.NameCounter and BusinessPhones.PhoneCounter = 2

And here are the results:

Notice in the query that I changed the table alias to reflect the type of phone number I was after, and I added aliases to the phone number columns to also reflect the type of number.

At this point, it is a simple matter of repeating the join for any number of phone types, always being sure to change the table alias and the PhoneCounter value to match the phone type. By the way, if you want to know which PhoneCounter values you have in your database, run the following query to find out:

select * from Shelby.NAPhoneTypes

These examples are all about phone numbers, but this same principle can apply to NAProfiles, MBTextPicks, SGMstOrg, and any other table that has a one-to-many relationship with NANames.

Stay tuned for other ways to take a set of rows and pivot them into columns.

GLAcct Table Gotcha

Assumptions are the bane of any query project, and one assumption I recently made bit me today. I had assumed that only Income and Expense type accounts would have any value stored for the ClosingAcctNu column in the GLAcct table. After all, only those two types of accounts use a closing account. In the software they are the only kinds of accounts that allow you to see or to enter any value for closing account information.

I was wrong. Somehow the ClosingAcctNu column in a customer's data was populated with account values for header accounts, total line accounts, and possibly others as well. This was not a conversion issue; these were brand new accounts in a brand new chart of accounts. I had to adjust my query for this customer in order to specifically restrict the search for closing accounts to the detail account type, in order to avoid picking up erroneous closing account information from the Header and Total Line rows in the GLAcct table.

This was particularly frustrating because it only became apparent in the "real world" environment; in my test database there are no extraneous closing account values in the GLAcct table.

Whenever you get unexpected results, looking for the culprit can be tricky. Just be prepared for the unexpected to sometimes be lurking even in a query of familiar tables.