Title: Using SQL in OneStream Finance Business Rules, Part 2: 1 or 0 or Nothing at All – Looping Data Table Collections in OneStream

Collecting one’s thoughts

In my last exciting (ahem) post on SQL in Finance Business Rules, I noted (ranted) that looping a zero-based Data Table using its one-based Count property made me sad. It does because it is – at best – inelegant. Why Microsoft, why did you take an object and give it two different item counts? The world may never know, but no matter, as my ex-coworker (Mustn’t there be a better way of wording that? If so, what is it? More literate readers, comment please.) Cosimo Palmisano pointed out, using VB.Net’s For Each…Next is a simpler way to loop a collection because it ignores the concept of a counter and instead iterates its way through a Collection. He’s right.

You can call it anything, really anything. Really.

In the context of using SQL as metadata to dynamically drive Finance Business Rule code, the Data Table object’s (which is a Collection) rows must be looped and the row’s field values concatenated to code strings. To iterate the rows, an object reference must be defined. It can be called anything, i.e., the name assigned to the object that is being looped is arbitrary.

Probably don’t do this, but you can.

Ignoring for the moment that that code clarity is a Good Thing, let’s loop the Data Table using the word “pickle”.

Yes, this works.

But “pickle” within the context of a For Each…Next loop that addresses a row within a data table is…obscure albeit syntactically and logically correct. Although any string is valid, please use one that makes sense, such as “row”, because after all…

…this is Pickle the cat.

And this is the code as it should be written:

The end, so far

No matter your take on cats (and I have four), use For Each…Next and object names that have meaning.

The next post in this series will, I pinky promise, illustrate concatenating strings into code and then writing outside of the Data Unit for fun and profit.

Be seeing you.