The Or operator is not working

[Former Member]

I have Crystal Reports XI, and I can't get the Or operator to work in the record selection formula. I can get each individual line to work, but once I put an Or statement in, nothing shows up in my report.

{CP_ORDLIN.QTY_BO} > 0 Or

{CP_ORDHDR.CODE_STAT_ORD} <> "Q"

Each line works individually, but once I put the Or operator between them, I get nothing.

[Former Member]

I had to use a Right Outer join in the table linking.

[vitaly_izmaylov]

Steve,

One of the possible reasons could be Null values.

You may try two options:

1. Change report settings: File> Report Options> Convert Database Null values to default, or

2. Modify Records selection to add IsNull check:

(IsNull ({CP_ORDHDR.CODE_STAT_ORD}) and {CP_ORDLIN.QTY_BO} > 0 )

Or

(Isnull ({CP_ORDLIN.QTY_BO}) and {CP_ORDHDR.CODE_STAT_ORD} <> "Q")

[ted_ueda]

So once you set the Record Selection Formula, what does the SQL statement look like (Database -> Show SQL Query...)?

Sincerely,

Ted Ueda

[Former Member]

This is the whole selection I am trying to make:

SELECT "CP_ORDLIN"."ID_ORD", "CP_ORDLIN"."DATE_PROM", "CP_ORDLIN"."ID_LOC", "CP_ORDHDR"."CODE_STAT_ORD", "CP_ORDLIN"."ID_ITEM", "CP_ORDLIN"."DESCR_1", "CP_ORDLIN"."QTY_OPEN", "CP_ORDHDR"."NAME_CUST", "CP_ORDHDR"."ID_SLSREP_1", "CP_ORDHDR"."AMT_ORD_TOTAL", "CP_ORDHDR"."DESCR_SHIP_VIA", "CP_ORDLIN"."SEQ_LINE_ORD", "CP_ORDLIN"."ID_SO", "CP_ORDHDR"."ID_ST", "CP_ORDLIN"."QTY_BO"

FROM "PUBLIC"."CP_ORDHDR" "CP_ORDHDR" INNER JOIN "PUBLIC"."CP_ORDLIN" "CP_ORDLIN" ON "CP_ORDHDR"."ID_ORD"="CP_ORDLIN"."ID_ORD"

WHERE ("CP_ORDHDR"."CODE_STAT_ORD"<>'Q' AND "CP_ORDLIN"."SEQ_LINE_ORD"=10 OR "CP_ORDLIN"."QTY_BO">0)

ORDER BY "CP_ORDLIN"."ID_LOC"

[ted_ueda]

> ...I am trying to make...

Is the SQL that you want, or the SQL that the report is running, both, or neither?

I do see that the filter is A and B or C and not A or C.

Sincerely,

Ted Ueda

[Former Member]

Steve,

The WHERE clause is where you are having problems. You have to use parenthesis to determine how it will be evaluated.

("CP_ORDHDR"."CODE_STAT_ORD"'Q' AND "CP_ORDLIN"."SEQ_LINE_ORD"=10 OR "CP_ORDLIN"."QTY_BO">0 )

is different than

("CP_ORDHDR"."CODE_STAT_ORD"'Q' AND "CP_ORDLIN"."SEQ_LINE_ORD"=10 ) OR "CP_ORDLIN"."QTY_BO">0

is different than

"CP_ORDHDR"."CODE_STAT_ORD"'Q' AND ("CP_ORDLIN"."SEQ_LINE_ORD"=10 OR "CP_ORDLIN"."QTY_BO">0 )

All 3 will give different results. You have to decide which will work for you.

Jason

[Former Member]

Ted,

Please Point this out to me. I'm not seeing this.

Thanks,

Rob

[ted_ueda]

Hello Rob,

Steve's original question concerned the selection filter:

{CP_ORDLIN.QTY_BO} > 0 OR {CP_ORDHDR.CODE_STAT_ORD} = "Q"

However, Steve's subsequent post has a Where clause:

"CP_ORDHDR"."CODE_STAT_ORD"'Q' AND "CP_ORDLIN"."SEQ_LINE_ORD"=10 OR "CP_ORDLIN"."QTY_BO">0

The latter statement is more restrictive than the former, since the AND operator takes precedence over OR. For example:

False AND True OR False == (False AND True) OR False == False OR False == False

Boolean algebra is distributive, so

a AND b OR c == (a AND b) Or c == (a OR c) AND (b OR c)

so the second expression above becomes

("CP_ORDHDR"."CODE_STAT_ORD"'Q' OR "CP_ORDLIN"."QTY_BO">0) AND ("CP_ORDLIN"."SEQ_LINE_ORD"=10 OR "CP_ORDLIN"."QTY_BO">0)

(so I used to do this for a living once...)

Since the expression Steve originally asked about was different from the SQL, I wanted to know where the additional AND clause had come from.

Sincerely,

Ted Ueda

ps. Graham's referring to [Short-Circuit Evaluation|http://en.wikipedia.org/wiki/Short-circuit_evaluation] that Crystal supports. This is useful if you want to evaluate a condition such as:

(1 / {?x} > 10)

which is problematic if there's a possibility that {?x} can be zero, since that would raise a divide by zero exception. So you'd write:

({?x} = 0) OR (1/{?x} > 10)

and that's safe, since if {?x} is zero, the right-hand size of the OR would be True and the left-hand size won't have to be evaluated.

This is equivalent to:

If {?x} = 0 Then
    True
Else If (1/{?x} > 0) Then
    True
Else
    False

[Former Member]

Oh, it just didn't copy over. It is actually a not equals "Q". But like I said, I can get each statement to work individually, just not when I put the Or operator in.

Edited by: Steve Jackson on Aug 1, 2008 10:05 PM

Edited by: Steve Jackson on Aug 1, 2008 10:05 PM

[amrsalem1983]

the "OR" operator or any other operator, should come with a condition in the formula, at least "i think so"

you can do it like this

if {CP_ORDLIN.QTY_BO} > 0 Or {CP_ORDHDR.CODE_STAT_ORD} = "Q" then 0 else 1

that means if your condition is true, then it will return 0, otherwise it will return 1

you can use any other conditions

good luck

Amr

[Former Member]

Is there more to the select criteria than just the OR?

If so throw the OR statement in parantheses:

( < 0 or

<> "Q")

Otherwise I don't see a reason for this not working.

[Former Member]

I tried a few different ways, and the syntax never shows any errors, but I don't get any results in the report. Although I get records to come through for each individual selection.

[Former Member]

Check your table linking, that's the only other thing I can see as a possiblity.

[former_member260594]

Crystal formulas will stop evaluating an OR statement when a condition is true.

So in the example

{field 1} <> 0

OR

{field 2} = 'Q'

The second condition will only process when {field 1} = 0.

I would suggest hard coding the SQL within a command object or stored proc as SQL will evaluate all or conditions

[Former Member]

I think you may be on to something. The Or operator seems to work if there is only one table, but on the linked tables it doesn't.

[Former Member]

G-

Not in an OR statement. I think you got mixed up with the other post that contains the IF.

Rob

[Former Member]

I think the table linking is the issue. I can get the Or operator to work properly if I'm only using one table. However, the information I need is in two tables through an ODBC connection to my ERP software.

[Former Member]

Steve,

It looks like you are missing an "=" sign

Try this:

{CP_ORDLIN.QTY_BO} > 0 Or
{CP_ORDHDR.CODE_STAT_ORD} = "Q"

Jason