Token Examples

From Drillbridge Wiki
Jump to: navigation, search

Token for VALUES in SQL Server 2012

Microsoft SQL Server 2012 contains a new syntax for checking if a value is in a set of another values; this new syntax can give better performance than the normal IN (value1, value2) syntax.

SELECT *
FROM Transactions
WHERE BUSINESS_UNIT IN
(
    SELECT BUSINESS_UNIT FROM
    (VALUES ('BU001'), ('BU002'), ('BU003')) AS T(BUSINESS_UNIT)
)

There are a couple things that need to be set in the token in order to achieve this format. The following example token shows how to achieve this, using an example related to a time dimension.

{
    "name" : "Time",
    "expression" : "'(''' + #Time + ''')'",
    "drillToBottom" : "true",
    "sampleValue" : "Q1",
    "quoteMembers" : "false"
}

Note a couple of things: one is that the quoteMembers property is set to false. By default this is on and when it is on, it causes member names (such as the individual members that go inside the IN clause of your query) to be surrounded with single quotes. We need to turn this off because we are providing our own single quotes in the expression.

Secondly, note the contents of the expression. This expression can be thought of as having three parts: three strings being concatenated. The first string is '(</code, the second part is <code>#Time and the last part is )'. The first and third expressions are special. Because we turned off the quoteMembers option and our values are strings (meaning that they need to be surrounded by single quotes for our database), we are needing to supply the quotes ourselves. Additionally we are also supplying the opening and closing parentheses. Since Drillbridge expressions are SpEL expressions, we need to escape the single quote. This is done by having two consecutive single quotes. Therefore the contents of the first expression are really just

(''
since this is what is enclosed in the outer single quotes. The two consecutive quotes will be evaluated to a single quote and therefore the entire prefix for each member will be ('. Similarly, the suffix to each member will end up being '), and if Q1 resolves to members such as Jan, Feb, and Mar, the final result will be (('Jan'), ('Feb'), ('Mar')).