Selection Formula no longer passing to SQL in Crystal 9

[lyanch]

I have quite a number of reports that when updating to Crystal 9 from Crystal 8.5 the selection formula no longer passes to the database. The main factor seems to be if there is an "or" in part of the selection formula.

Here is an example:

{sold.getTime} in datediff("s", Datetime(1970,01,01,0,0,0), datetime(currentdate(),time(0,0,0))) to datediff("s", Datetime(1970,01,01,0,0,0), datetime(currentdate(),time(23,59,59)) )

and

(isnull({order.contract_id}) or {order.contract_id} <> {?RestrictedCos}) and not ({order_item.order_item_id} in 38073 to 38096)


This worked perfectly fine in Crystal 8.5 but now the second half (contract and order item selection) does not pass to the SQL at all!

I have tried rearranging it many different ways but no luck. Either of the two or's individually will pass, it is only when I make it an or that it fails.

Any ideas? One note, I do not have the Add Command option with the sybase drivers.

Lisa

 

[synapsevampire]

I'm surprised, I would think that the first part would not have passed with 8.5 because of the datediff function if anything.

Also, most databases support a datediff function, so you might create a SQL Expression to calculate this and forego the Crystal passing altogether.

Since you didn't post the database I can't state assuredly that this will work, but it's one way to handle it

Try:
(
isnull({order.contract_id}) or {order.contract_id} <> {?RestrictedCos}
)
and
(
not ({order_item.order_item_id} in [38073 to 38096])
)
and
(
{sold.getTime} in datediff(&quot;s&quot;, Datetime(1970,01,01,0,0,0), datetime(currentdate(),time(0,0,0))) to datediff(&quot;s&quot;, Datetime(1970,01,01,0,0,0), datetime(currentdate(),time(23,59,59)) )
)

Placing each section up to the AND in one at a time (or remark out those sections - remember that Alt-M now toggles remarks for blocks of code), and then check the Database->Show SQL Query

-k

 

[lyanch]

If you note, the datediff converts to a constant in Crystal before the sql is created. It will always pass.

Actually I probably picked a bad example. Here is another reports selection formula (I do lots of different cuts of the same data)

{sold.getTime} in datediff(&quot;s&quot;,datetime(1970,01,01,00,00,00),{?getTimeStart}) to
datediff(&quot;s&quot;,datetime(1970,01,01,00,00,00),{?getTimeEnd})
and
({order.contract_id} = 164 or ({order_item.order_item_id} >= 38073 and {order_item.order_item_id} <= 38096))

It all passes if I change the &quot;or&quot; to &quot;and&quot;. I can remove either side of the or (eliminating the or) and it passes. I can leave both parts on and change it to &quot;And&quot; and it passed. The odd part is that in Crystal 8.5 this passed every time.

Lisa

 

[synapsevampire]

Datediffs won't always pass, but I'll leave that for another discussion.

In my example I tried to reinforce the use of parentheticals to help Crystal with passing SQL, but your example doesn't show them.

This DOES make a difference.

I see that you also tried changing the IN to >= <=, I would have too...

Try creating a startdate and enddate formula for the datediff portions, and referencing those in the record selection, this seems to help 8.5 a great deal.

Also try adding in parnthesis around each distinct area.

(
{sold.getTime} >= @startdate
and
{sold.getTime} <= @enddate
)
and
(
{order.contract_id} = 164
or
(
{order_item.order_item_id} >= 38073 and {order_item.order_item_id} <= 38096
)
)

This sort of design generally works in CR 8.5

-k

 

[lyanch]

The error appears to be within the native driver. It does not support using an &quot;OR&quot; between fields in the select statment.

ie.

table.field1 = 1 or table.field1 = 2 // does go to SQL

table.field1 = 1 or table.field2 = 1 // does NOT pass to the SQL.

This occurs with the Sybase native driver and Crystal 9

Lisa