WHERE clause in a SMARTFORM loop, problems with parenthesis

[matteo_montalto]

Hi all gurus,

a simple question: I'm coding a LOOP in the form builder for a smartform ... this simple loop should bind text elements to every position of a purchase order. The WHERE clause should be as follows:

...

WHERE GUID = WA_ITEM-GUID

AND ( TDID EQ 'ZDES' OR TDID EQ 'ZDST' ).

Anyway, in the Form Builder I didn't find a way to add parenthesis; I workarounded the problem with the following query:

(conditions in lines are in AND conjunction)

GUID EQ WA_ITEM-GUID

TDID EQ 'ZDES'

OR

GUID EQ WA_ITEM-GUID

TDID EQ 'ZDST'.

It's quite a 'bad' way, but it works. I wonder if there's a way to express "complex" WHERE clauses (with addiction of parenthesis) in Form Builder.

Thanks in advance

[Former Member · Accepted solution]

Hi,

On the WHERE Condiiton Block you can find the (OR) Button inbetween the Delete record and Syntax Check. Try that button.

[matteo_montalto · Accepted solution]

Hi Avinash and thanks,

The OR button is what I used to formulate my WHERE statement.

The problem is that I'd like to create a complex clause after the OR operator. The task is: how to write a where statement done as follows:

A = X AND (B = Y OR B = Z)

with the form builder?

I don't have any alternative to a classic OR operator. My workaround was to expand that statement in:

A =X AND B =Y OR A =X AND B =Z.

However, I was wondering if there's a better way (eg. using parenthesis) to write such a WHERE clause.

[Former Member · Accepted solution]

Hi,

If the 'ZDES' and 'ZDST' values are fixed then in the Initialization tab of the Global Definition you can delete all the entries from the table whose values are not ZDES or ZDST.

or you can create a Program Line before the loop/table command and delete the entries whose values are not ZDES or ZDST.

[Former Member]

In this case, your solution is the one that works. However in more complex cases with more variables, this can proove difficult to make or mantain.

What I usually do is use the first part of the condition in the where and then add a condition to the printing area with the rest of the formula. Just remember to document this so that you'll find it easy and it's way more amintainable.

In this case the loop would contain WHERE GUID = WA_ITEM-GUID and the inside condition TDID EQ 'ZDES' OR TDID EQ 'ZDST. This creates the and between the two blocks.