Our company has started using SQL Server Reporting Services 2008 R2 to handle most of our reporting against Netezza. This works pretty well over all using the Netezza 6.0 OLE DB driver. There are a few things to be aware of though.
I found a number of people were using expressions to construct a SQL statement to send for the data set. This works well, but it’s a little bit of a pain to both write and maintain. Luckily, we can use parameters (with just a few things to be aware of). That means we can write SQL that looks like this:
SELECT
date_key
,date
,good_day
,day_of_week
FROM
date_sample
WHERE
1=1
AND day_of_week IN (@day_of_week_multi)
AND date IN (@date)
AND date_key = @date_key
AND good_day = @good_day
ORDER BY
date_key
;
You can use parameters in the normal way with Netezza, but there are a few details to be aware of. You just imbed the parameter in the query like
and create a corresponding parameter in your data sets. The OLE DB driver handle surrounding surrounding the parameter with quotes when required (strings and date times), and it automatically escapes any single quotes in the string.
Parameters are ordinal, so you need to create a parameter (in order) for each value you want to send. There parameters in your query will be replaced in order based on the parameters you specified in the dataset. No big deal, but something to be aware of. You can either use a either a normal parameter (such as ‘@myparam’) or a question mark (‘?’).
Hears the rub… the OLE DB driver also automatically escapes single quotes so you don’t have to worry about that. This is great… but there is a little bit of an issue when you want to use multi-value parameters. Basically, the normal method of using SQL like
WHERE col_a IN (@multi_value_param)
and an expression like this for a parameter
=JOIN(@multi_param, "’,’”)
doesn’t work because the single ticks will always be escaped (so you’ll see a value like “’val1’’,’’val2’’,’’val3’” instead of “’val1’,’val2’,’val3’” in the resulting SQL). No problem… we just need a different approach. We’ll use something in the WHERE clause like
STRPOS(@multi_param, '|' || col_a|| '|') > 0
with an expression like
=”|” + JOIN(@multi_param, "|”) + “|”
This will bracket each value in a special “end of column” character such as “|”, and thus look for an exact match. You may need to use a different character, or handle escaping, depending on your data. You can always use multiple characters if you’d like (such as “~~~” as a delimiter). This is a little hacky, but not too bad. Remember to add the delimiter to the start and end of the string as well so you can match those values as well.
I tested this on a 1.5 billion row table against a similar query that uses an IN clause. I found the two methods were essentially equal in performance, though that might degrade for an extremely large number of parameter values. These both provide exact matches against the value, and they provide equivalent results.
But… what about using a multi-select on a date? Unfortunately, that get’s slightly more complicated, but not too bad. All you need to do is write a little code such as
''' <summary>
''' Convert an array of Dates into a string.
''' </summary>
''' <param name="inputArray">Array of dates to convert.</param>
''' <param name="delimiter">Delimiter to place between each item in the array.</param>
''' <param name="formatString">Format string for the date.</param>
''' <returns>String of formatted datetimes separated by a delimiter.</returns>
''' <remarks></remarks>
Public Function JoinDate(ByVal inputArray() As Object, ByVal delimiter As String, ByVal formatString As String) As String
Dim output As String = String.Empty
Dim i As Integer
For i = 0 To inputArray.Length - 1
output += (CDate(inputArray(i))).ToString(formatString) + delimiter
Next i
' Trim the trailing delimiter off the string
If output.Length > 0 Then
output = output.Substring(0, output.Length - delimiter.Length)
End If
Return output
End Function
And use an expression like
="|" + Code.JoinDate(Parameters!date.Value, "|", "yyyy-MM-dd") + "|"
And you’re good to go! This will convert a multi-select DateTime variable (which is an array of DateTime objects) into a string containing dates formatted as you request.
So for this example where we are constraining on a multi-value string parameter, a multi-value date parameter, plus a few other standard guys, we end up with
SELECT
date_key
,date
,good_day
,day_of_week
FROM
date_sample
WHERE
1=1
AND strpos(@day_of_week_multi, '|' ||day_of_week || '|') > 0
AND strpos(@date, '|' || date || '|') > 0
AND date_key = @date_key
AND good_day = @good_day
ORDER BY
date_key
;
Here’s a sample that shows how to use integer, datetime, and string values (with some examples of multi-value parameters thrown in). Just use the SQL script to build and populate the test table, set the data source appropriately, and play around with it.