Code Tips

Creating a Comma Separated List From a Sql Grouping

A few times recently when I’ve been creating a view for full text indexing on Sql Server, I’ve had need to return the results of a one to many relationship in one row. I didn’t need dynamic columns or anything, what would suit me best would be to have all of the values just in one comma separated list. After searching the net, I found an ingenious use of FOR XML PATH to solve this problem.

Here is the standard query to return the list of employees and their sales territories from the Northwind database.

USE Northwind
go

SELECT e.EmployeeId, e.FirstName, e.LastName, t.TerritoryDescription
FROM Employees e
INNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID
INNER JOIN Territories t ON et.TerritoryID = t.TerritoryID

The resulting set looks like the following:
Result set with no grouping or concatenation.

What might be best is some kind of built in function that did the following (kind of like C#’s string.Join()):

USE Northwind
go

-- This Code is fictitious, CONCATENATE() is not a real aggregate function
SELECT e.EmployeeId, e.FirstName, e.LastName, CONCATENATE(t.TerritoryDescription, ',') as TerritoryList
FROM Employees e
INNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID
INNER JOIN Territories t ON et.TerritoryID = t.TerritoryID
GROUP BY e.EmployeeId, e.FirstName, e.LastName

However, nothing like that was to be found. What I did find was someone who used FOR XML PATH in an interesting way. Normally, FOR XML PATH would return some normal looking xml, however if you pass in the ” as the argument, you just end up with all of the values that would be in an xml string, but with no resulting tag soup.

Running the following code:

USE Northwind
go

SELECT e.EmployeeId, e.FirstName, e.LastName,
STUFF((SELECT ',' + COALESCE(LTRIM(RTRIM(t.TerritoryDescription)), '') 
FROM EmployeeTerritories et
INNER JOIN Territories t on et.TerritoryID = t.TerritoryID
WHERE et.EmployeeID = e.EmployeeId
FOR XML PATH('') ), 1, 1, '') as TerritoryList
FROM Employees e

Gives the following result:
Result set with grouping and concatenation.

This does do a subselect, but I’ve found that it doesn’t hurt performance too badly. The only other trick here is the STUFF() function, which replaces part of a string with another. In this case, I’m replacing only the first character (the 1, 1) arguments with just an empty string, effectively removing our extra comma.

I hope that someone may have found this useful and if you have any other techniques for solving this kind of problem, I’d love to hear them.

Code Tips

Fun With Blanket Purchase Orders in Dynamics GP

Dynamics GP
Microsoft Dynamics GP (formerly Great Plains) is a bit of a bear to do integration with. At first, you might think that it should be straightforward. Basically, it is a windows application with a SQL Server backend whose tables (albeit cryptically named) are updated by stored procedures. The problem begins to get deeper when you find out that all of the stored procedures are passed XML documents as parameters.

Microsoft tries to remedy this by offering eConnect (.Net wrapper over COM components that turn objects into the XML files that the procedures need) and the GP Web Services (add a layer). The issue comes, however, when you need some sort of functionality that either doesn’t provide you. Unsurprisingly, you don’t get very far until you exceed the out of the box functionality provided by those products.

The most recent problem that I’ve encountered at work surrounds purchase orders. We create two different kinds of purchase orders. The first is the straight forward purchase order where it is for one or more items and we expect to be invoiced from the vendor for the full amount of the purchase order immediately.

The second kind of purchase order that we create is called a Blanket PO. We create this kind of PO when we contract with a vendor for a rental. We know that a vendor will be billing us X amount of dollars every time period for a certain duration. For instance, if we are renting a widget for $10.00 a month for 6 months, we would create a PO for $60.00 with 6 line items of $10.00 each (each line representing a month). However, we only want to release the first line of the purchase order, since that is all that we are expecting to be invoiced for immediately. In the future, we will release each line as the month that they represent comes up.

We used eConnect to try to automatically create these POs. The problem, however, is that eConnect automatically releases all lines of the blanket PO upon creation. Our second problem is that the update does not expose this property for us to go back and update the lines to unreleased. Since Microsoft encrypts the contents of the stored procedures, we could not inspect or alter what was happening.

After troubleshooting on our end and working with our Microsoft Partner, we eventually had to call Microsoft. The support engineer on the phone confirmed that the procedure in question sets the Release column of the POP10110 table to 1 no matter what the input.

We knew that we could just issue a SQL statement to update the column back to 0, but were unsure what else may need to be affected when that column is updated. GP is notorious for many things needing to be interconnected and we didn’t want to find our POs in some weird state several months down the line. The Microsoft engineers were unsure if anything was affected, so they ran some tests and found that this column operates in isolation and we were free to issue our update statement.

So, after we call eConnect to create the blanket PO, we issue this SQL statement. The POP10110 table holds PO line items (POP10100 holds the PO Header), and in our case, we want to set the Release column all of the lines greater than 1 back to 0 and the Released_Date to ‘1/1/1900’ (the default for “no date given” in GP).

DECLARE @PONumber varchar(10)
-- This is set here as an example, but you can put it in a proc
-- or however you want to get the value in.
SET @PONumber = 'PO-1234567'

UPDATE dbo.POP10110
SET Release = 0, Released_Date = '1/1/1900'
WHERE PONumber = @PONumber AND LineNumber > 1

Simple answer to a complex problem.

Code Tips

Web Services Software Factory – Complete Code

I’ve received several requests to publish the code to the WSSF tutorial I did last year in its entirety. That series on my blog has by far generated the most traffic, so I wanted to get it up here. Unfortunately, I had long since deleted the code, so I had to work through my own tutorial to get the project coded.

You have to create the right virtual directory for the service for the console application to run (as described in part 5 of the tutorial), but other than that, this should be all you need… binaries are even included.

Enjoy.

WSSF Tutorial Solution

Ruby

The Greed Ruby Koan

This post is an extension of my last post on the Ruby Koans. Today, I tackled the next Ruby Koan on the block, which was to score dice throws on a simplified version of the game Greed. The first thing I noticed was that the Koans that I got from EdgeCase had a typo in it. It suggested that the following was true:

def test_score_of_mixed_is_sum
    assert_equal 50, score([2,5,2,2,3])
    assert_equal 550, score([5,5,5,5])
  end

That first assert should actually total 250, as the 3 2’s are worth 200 and the 5 is worth 50. I searched the web to make sure that I wasn’t crazy and the version of this file checked in to Google Code by Tim Wingfield agreed with me, so I forged on.

The hardest part of this one for me was figuring out the best way to even work this out in English. I play the Farkle game on Facebook quite a bit, so I was really comfortable with scoring the dice, but I just couldn’t figure out a good step-by-step way to do this. I eventually decided on a Hash to tally up the number of each dice present. This choice was made even easier when I found that Hash had an each_pair method that enumerated over the collection.

After that, I tried my best to implement the logic (this code passes the tests, I don’t promise it is bug-free) and have now put this up for criticism. Last time, I learned some great lessons from Nathan Kelley who shared his code in the comments of my last post. If you have any questions, comments, criticisms, or rebukes of this code, let me know and I’d love to hear them.

I definitely am guilty of writing C# in Ruby and had to undo a lot of code where I started to go down that path, so I welcome comments from Ruby enthusiasts on my code.

require 'edgecase'

# Greed is a dice game where you roll up to five dice to accumulate
# points.  The following "score" function will be used calculate the
# score of a single roll of the dice.
#
# A greed roll is scored as follows:
#
# * A set of three ones is 1000 points
#   
# * A set of three numbers (other than ones) is worth 100 times the
#   number. (e.g. three fives is 500 points).
#
# * A one (that is not part of a set of three) is worth 100 points.
#
# * A five (that is not part of a set of three) is worth 50 points.
#
# * Everything else is worth 0 points.
#
#
# Examples:
#
# score([1,1,1,5,1]) => 1150 points
# score([2,3,4,6,2]) => 0 points
# score([3,4,5,3,3]) => 350 points
# score([1,5,1,2,4]) => 250 points
#
# More scoing examples are given in the tests below:
#
# Your goal is to write the score method.

STANDARD_TRIPLET_MULTIPLIER = 100
ONES_TRIPLET_MULTIPLIER = 1000
ONES_VALUE = 100
FIVES_VALUE = 50

def score(dice)
  # You need to write this method
  result = 0  
  return result if invalid_dice?(dice)
  
  dice_sort = {1=>0, 2=>0, 3=>0, 4=>0, 5=>0, 6=>0}
  
  # Increment the tally, using the die face as the hash key
  # by iterating over the passed in array of dice
  dice.each do | die |
      dice_sort[die] += 1 
    end
  
  # Iterate over the pairs and score them up  
  dice_sort.each_pair do |key, value|
      if non_single_score?(key)
        result += key*STANDARD_TRIPLET_MULTIPLIER if value>= 3
      else
        # I LOVE Multiple assignments
        groups_of_three, remainder = value/3, value%3
        
        result += (ONES_TRIPLET_MULTIPLIER * groups_of_three) + (ONES_VALUE * remainder) if key == 1
        result += (key * STANDARD_TRIPLET_MULTIPLIER * groups_of_three) + (FIVES_VALUE * remainder) if key == 5
      end      
  end 
      
  result      
end
    
def non_single_score?(die)
  return true if die != 1 and die != 5
end

def invalid_dice?(dice)
  return true if dice.length == 0
end

class AboutScoringAssignment < EdgeCase::Koan
  def test_score_of_an_empty_list_is_zero
    assert_equal 0, score([])
  end

  def test_score_of_a_single_roll_of_5_is_50
    assert_equal 50, score([5])
  end

  def test_score_of_a_single_roll_of_1_is_100
    assert_equal 100, score([1])
  end

  def test_score_of_mulitple_1s_and_5s_is_the_sum
    assert_equal 300, score([1,5,5,1])
  end

  def test_score_of_single_2s_3s_4s_and_6s_are_zero
    assert_equal 0, score([2,3,4,6])
  end

  def test_score_of_a_triple_1_is_1000
    assert_equal 1000, score([1,1,1])
  end

  def test_score_of_other_triples_is_100x
    assert_equal 200, score([2,2,2])
    assert_equal 300, score([3,3,3])
    assert_equal 400, score([4,4,4])
    assert_equal 500, score([5,5,5])
    assert_equal 600, score([6,6,6])
  end

  def test_score_of_mixed_is_sum
    assert_equal 250, score([2,5,2,2,3])
    assert_equal 550, score([5,5,5,5])
  end
end
Ruby

Ruby Koans

KoansI decided to tackle a little Ruby language learning this weekend. So yesterday, I downloaded the Ruby Koans and went to work. The Koans were created by EdgeCase to help people learn Ruby. Basically, they are a set of unit tests that exercise aspects of Ruby. To begin, all of the tests fail. You have to write code or jump back and forth to the Interactive Ruby Shell (IRB) and get the results of the statements to make the asserts pass.

I went through and did 105 unit tests yesterday, each of them having several parts to them. Having messed around a little with Rails, I was familiar with using IRB and Rake and a little of the syntax. In my opinion, the way this is set up and builds upon itself is really helpful and is a great way to teach Ruby to n00bs. I really salute Jim Weirich and Joe O’Brien for their work in getting these together.

One of the exercises is to create a triangle method that takes three parameters (the sizes of the sides). You then have to decide what kind of triangle it is. After those tests pass, your next assignment is to prevent 0 length or negative sides as well as making sure your triangle validates.

I wrote all the code and passed all the tests except for the triangle validation test. I was having a serious brain cramp and could not remember how you ensured that you’ve gotten the correct sides for any triangle (and not just a right triangle), so I looked for someone else’s example of this code. I found this code:

# Triangle Project Code.

# Triangle analyzes the lengths of the sides of a triangle
# (represented by a, b and c) and returns the type of triangle.
#
# It returns:
#   :equilateral  if all sides are equal
#   :isosceles    if exactly 2 sides are equal
#   :scalene      if no sides are equal
#
# The tests for this method can be found in
#   about_triangle_project.rb
# and
#   about_triangle_project_2.rb
#
def triangle(a, b, c)
  if a<=0 or b<=0 or c<=0 
    fail TriangleError, "Sides must all have length greater than 0."
  end

  myArray = [a,b,c]
  maxSide = myArray.max
  total = 0
  myArray.each { |myEle| total += myEle }
  if total - maxSide <= maxSide
    fail TriangleError, "Sum of shortest two sides must be greater than length of longest side."
  end

  if a==b and a==c
     :equilateral 
  elsif a!=b and b!=c and a!=c
    :scalene
  else
     :isosceles
  end
end

# Error class used in part 2.  No need to change this code.
class TriangleError < StandardError
end

Okay, I see. The smallest two sides added together need to be greater than the third side. However, I thought that the author of this code greatly over-complicated this process. We are always dealing with a fixed number of sides and the trick is to put them in order and then add away. I figured Ruby had a simple array sort (and they do), so I would place the sides into an array, sort it, and then add the 0 index and 1 index spot and make sure that it was greater than the 2 index spot. Case Closed and (in my opinion) a bit easier to read. Forgiving of course, that I didn’t pass messages in my exceptions, I personally like my code better (of course 😉 ). I also appreciate that Ruby doesn’t mind me putting ( and ) around my if conditions. I just feel safer for them to be contained 😉

# Triangle Project Code.

# Triangle analyzes the lengths of the sides of a triangle
# (represented by a, b and c) and returns the type of triangle.
#
# It returns:
#   :equilateral  if all sides are equal
#   :isosceles    if exactly 2 sides are equal
#   :scalene      if no sides are equal
#
# The tests for this method can be found in
#   about_triangle_project.rb
# and
#   about_triangle_project_2.rb
#
def triangle(a, b, c)
  # WRITE THIS CODE
  if (a <= 0 or b <= 0 or c<=0)
    raise TriangleError
  end
  
  sides = [a, b, c].sort
  
  if (sides[0] + sides[1] <= sides[2])
    raise TriangleError
  end
  
  if (a==b and a==c)
    value = :equilateral
  else
    if (a==b or a==c or b==c)
      value = :isosceles
    else
      value = :scalene
    end    
  end  
end

# Error class used in part 2.  No need to change this code.
class TriangleError < StandardError
end

As I do these Koans, I’ve been making note of things I’d like to include in future blog entries about things I’ve enjoyed about the language and will be posting them in the near term.