Oct 01 2007

Universe Parameter: Eval Without Parenthesis

Categories: Universe Design Dave Rathbun @ 4:19 pm

The @Select() function adds a lot of flexibility as long as it is used properly. When to use it (and when not to use it) is probably a future blog post (or perhaps even more than one :lol: ). But one of the things that I don’t care for is the fact that when I do use it, the referenced code is embedded within extra parenthesis. It turns out there is a way to change that behavior.

The Way It Was

I have built universes with an “ALL” constant added to list of values queries (also a future blog post). Once the universe was delivered the user requested that the word “ALL” be changed to something different. That meant I had to search throughout the entire universe to find the original “ALL” and update it. That meant searching through prompt objects as well as a large number of LOV queries.

Instead of updating it to the newly requested string, I used the @Select() function.

My new object named “ALL String” looked like this:

' ALL '

The spaces are to try to encourage the ALL string to appear at the top. Here is what a prompt looked like:

( @Select(Item\Item) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained)
OR @Select(LOV\ALL String) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained) )

The SQL code behind that prompt looked like this:

( ( table.item ) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained)
OR ( ' All ' ) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained) )

The parenthesis around the ‘ ALL ‘ are completely unnecessary. They also exist around the table.item reference as I have used the @Select() function there as well. They don’t break anything, but it does – in my opinion – make the SQL harder to read and understand. I have been asked more than once by the DBA team why I have them there. :)

Losing My Parenthesis

In looking for a solution to something completely unrelated I noticed a new parameter that has appeared in my universe. (Note: This blog post pertains to XI R2. I have not checked to see if this feature is available in earlier versions.) The parameter name is EVAL_WITHOUT_PARENTHESIS and the default value is No. If you open the parameter screen for your universe and update this from “No” to “Yes” then the SQL code for the same condition posted above becomes the following:

( table.item IN @Prompt('Please select Item','A','LOV\Item',multi,constrained)
OR ' All ' IN @Prompt('Please select Item','A','LOV\Item',multi,constrained) )

I like this much better. I believe the default is set to “No” in order to preserve any complex expression logic from the referenced object. But for a simple constant reference they are certainly not needed. I would rather put the parenthesis in myself when I know they’re needed rather than having them there all the time. This parameter lets me do that.


6 Responses to “Universe Parameter: Eval Without Parenthesis”

  1. Comment by Marek Chladny

    Just FYI: The same parameter exists in BO 6.5.

  2. Comment by Dave Rathbun

    Good to know, thanks.

    Were you aware of it before? Had you used it?

  3. Comment by Marek Chladny

    No, I have not used it. After reading your blog I was interested whether the same parameter exists in BO 6.5. And yes, it is there :-)

  4. Comment by Rhonda

    Oh, I like that. I have to use @Select all the time, and the extra () always bothered me (and the DBAs!).

  5. Comment by Ottoman

    I tried this once in an established universe & came unstuck with complex oracle calculations

    a slight digression… I have a dummy ‘ALL’ object from th DUAL table in the universe and use this in a union query for the edited LOVs, better than hardcoding the SQL.

  6. Comment by Dave Rathbun

    I create / use a table-based (DUAL or otherwise, depending on the database) “ALL” object for my LOV queries as well. I had a nice “discusssion” :-D with some folks a few months ago about the relative cost of using hard-coded SQL versus taking the time to do things in a way that I would call correct. In my opinion, there is going to have to be a very pressing argument for hard-coding SQL, meaning absolutely no other way to accomplish the task, before I will consider doing it in the universe.

    Thanks for your comment, welcome to my blog. 8-)

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:


Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.