Use of "in" in Stage Variables

[Dsnew]

Hello all,

Is there a way to replace "IN" statement without using "OR" multiple times while creating a derivation for a stage variable?

Eq: If I have to use a Job parameter "JobP" which has a value of ('AAA',BBB','CCC','DDD') which I use in my source query, I would like to move it to the Stage variable derivation - I currently use:

Code: Select all

ColumnName.Field = 'AAA' or ColumnName.Field = 'BBB' or ColumnName.Field = 'CCC' or ColumnName.Field = 'DDD'

In other works how do we implement "IN" in the Stage Variable.

Appreciate any help.

[samplify]

Well, DS does not support the use of IN statement (the SQL one)
however you could use regular expression and a decent text editor to convert your lengthy IN based statements to DS ORs.

[chulett]

One 'trick' is to build your delimited list in the Initial Value of a stage variable and leave the Derivation blank (so it is only evaluated once) and then use the Index function to check for your value in that list of values. For example:

Code: Select all

svValidValues: AAA,BBB,CCC,DDD

Or you could even use your job parameter value directly. Then your Index check, which returns the starting position of the substring within the string, can rely on the fact that while a 0 value equates to 'false', any non-zero value equates to 'true' in Server:

Code: Select all

If Index(svValidValues,ColumnName.Field,1) Then (true expression) Else (false expression)

This only gets a little tricky if there is any 'overlap' between field values or your incoming field could be a subset of a valid value, meaning where one value can be found inside another value. For example, an incoming value of 'AA' would be reported as a match to 'AAA'. For that you would need to extend the delimiters to the ends of the value list and then include the enclosing delimiters in the check:

Code: Select all

svValidValues: ,AAA,BBB,CCC,DDD,

If Index(svValidValues,",":ColumnName.Field:",",1) Then (true expression) Else (false expression)

To turn your current parameter value into such a list, you could do that with Convert:

Code: Select all

svValidValues:  Convert("()",",,",JobP)