• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

using AND and OR

dsaad

Member
When specifying data selection criteria for a batch version, is there a way to structure the criteria so that you get:

where (((fieldA = value1 or fieldB = value2) and
(fieldC > value3 and fieldD < value4)) or
((fieldA = value5 or fieldB = value6) and
(fieldC > value7 and fieldD < value8)))

simply using
where fieldA = value1 or
fieldB = value2 and
fieldC > value3 and
fieldD < value4 or
fieldA = value5 or
fieldB = value6 and
fieldC > value7 and
fieldD < value8

for the data selection won't give me the correct results.

Is there a way to specify data selection in a way that effects the order per the parentheses outlined above?




XE RELEASE B7333 SP14.2
 
Sorry, the only one way to get parens is by specifying data selection and
data selection append simultaneously in the INIT of the same section. That
results in

(data selection) and/or data append. that is, the system puts parens
around the data selection part.

But you could rephrase your data selection as follows to do it without
parens:

FA = V1 and FC > V3 and FD < V4 or FB =V2 and FC > V3 and FD < V4 or
FA = V5 and FC > V7 and FD < V8 or FB = V6 and FC > V7 and FD < V8

Good luck,

Harry
 

Zoltan_Gyimesi

Legendary Poster
David,
Great pain, AND operator always take precedence over OR operator and you can not use parentheses.
The only way that you can do is to reconstruct your boolean expression to an AND/OR normal form keeping these rules in view.
The result will be a long boolean expression.
Sorry,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Zoltan_Gyimesi

Legendary Poster
Re: RE: using AND and OR

Harry,
Is it really true that setting the selection using system functions in the INIT event of the section will place parentheses around each appended selection criteria indepently that you append them with AND or OR?
If yes, then it is a very useful information. Thanks.
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
RE: RE: using AND and OR

No Zoltan,

The parens are placed only around the entire DATA SELECTION, then the
appends are appended to that, eg:

if data selection is:

FA = V1 or FB = V2

and data selection append is:

And FC = V3

Then the resulting sql will be:

(FA = V1 or FB = V2) AND FC = V3

Sorry for any misstatement I might have made.

Harry
 

Zoltan_Gyimesi

Legendary Poster
Re: RE: RE: using AND and OR

Harry,
Thanks for your update.
Am I write that if you append more criteria then it will cause nested parentheses?
Continuing your example:
======original example==============
if data selection is:

FA = V1 or FB = V2

and data selection append is:

And FC = V3

Then the resulting sql will be:

(FA = V1 or FB = V2) AND FC = V3
===========continuation of the example===============
....and the next data selection append is:

OR FD = V4

Then the resulting sql will be:

((FA = V1 or FB = V2) AND FC = V3) OR FD = V4

....and the next data selection append is:

AND FE = V5

Then the resulting sql will be:

(((FA = V1 or FB = V2) AND FC = V3) OR FD = V4) AND FE = V5
===========end of example===========================
Is it correct?

Regards,
Zoltán


B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

pwplus

Member
David,

I am new to JDE but I understand a little Boolean logic. Assuming Zoltan is
correct and AND takes precedence over OR, you could rework your origninal
expression:

"where (((fieldA = value1 or fieldB = value2) and
(fieldC > value3 and fieldD < value4)) or
((fieldA = value5 or fieldB = value6) and
(fieldC > value7 and fieldD < value8)))"



Let P = "fieldA = value1"
Q = "fieldB = value2"
R = "fieldC > value3"
S = "fieldD < value4"
T = "fieldA = value5"
U = "fieldB = value6"
V = "fieldC > value7"
W = "fieldD < value8"

Then your expression can be rewritten as:
where (((P or Q) and (R and S)) OR ((T or U) and (V and W)))
Now (P or Q) and (R and S) can be rewritten (P and R and S) or (Q and R and
S).
Likwise (T or U) and (V and W) can be rewritten (T and V and W) or (U and V
and W).
Now (((P or Q) and (R and S)) or ((T or U) and (V and W))) = ((P and R and
S) or (Q and R and S)) or ((T and V and W) or (U and V and W))
= (P and R and S) or (Q and R and S) or (T and V and W) or (U and V and W).

Now we substitute back:
(P and R and S) or
(Q and R and S) or
(T and V and W) or
(U and V and W) =
(fieldA = value1 and fieldC > value3 and fieldD < value4) or
(fieldB = value2 and fieldC > value3 and fieldD < value4) or
(fieldA = value5 and fieldC > value7 and fieldD < value8) or
(fieldB = value6 and fieldC > value7 and fieldD < value8).

Assuming that the AND takes precedence over the OR, the parens can be
removed to yield:
fieldA = value1 and fieldC > value3 and fieldD < value4 or
fieldB = value2 and fieldC > value3 and fieldD < value4 or
fieldA = value5 and fieldC > value7 and fieldD < value8 or
fieldB = value6 and fieldC > value7 and fieldD < value8.

Assuming AND takes precedence over the OR, now we have an equivalent
expression to your initial expression with no parens.

I think this is correct but please set up a truth table to check the
validity of my results. Hope this help.

Regards,
Pat

----- Original Message -----
From: Zoltan_Gyimesi <zoltan.gyimesi@synergon.hu>
To: <jdeowdevml@jdelist.com>
Sent: Thursday, May 03, 2001 10:18 AM
Subject: Re: using AND and OR


can not use parentheses.
an AND/OR normal form keeping these rules in view.
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Numbe
r=10178



Patrick Plusnick
OneWorld/XE SP 14.2, ES AS/400, DS NT 4, JAS NT 4
 

Zoltan_Gyimesi

Legendary Poster
Re: RE: RE: using AND and OR

Harry,
Thanks your update again.

Does it mean that it will be evaluated as I marked it with parentheses below?

((FA = V1 or FB = V2) and FC = V3) or (FD = V4 and FE = V5)

Although the last AND will take precedence over the preceding OR.

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
RE: RE: RE: using AND and OR

Yes I believe the effect would be so Zoltan. However, of course,
evaluating it sans most of those parentheses! Do you own a majority
interest in a Parentheses Factory? Or What?

((((( (())) () ((()) () () ()
)) (( )) () ()) (( )) ()) ()
(( (( )) () (() ((())) () ()()
))))) (())) (())) ))( (( )) () (((

Harry - without any burdensome parentheses.
 
Top