Or Operator and records not printing

[Lenna930]

Hi all,
I'm converting a crystal report from a pervasive database use to SQL database use. The below formula works fine in the pervasive report, but in SQL the report appears to have selected all the relevant records but only prints those that satisfy the first half of the query (everything before the OR operator). Any thoughts?

{PROJECT.lookup5}={?Area}
and {PROJECT2.date5}in{?Start Date}to{?End Date}
or {PROJECT.lookup9} = "l. Tender Lost" and
{PROJECT2.date6}in{?Start Date}to{?End Date}
and{PROJECT.lookup5}={?Area}

 

[TheBlondOne]

Hi there

Think the problem might be with the lack of brackets around the statements.

Try

({PROJECT.lookup5}={?Area}
and {PROJECT2.date5}in{?Start Date}to{?End Date})
or ({PROJECT.lookup5}={?Area} and
{PROJECT.lookup9} = "l. Tender Lost" and
{PROJECT2.date6}in{?Start Date}to{?End Date})

Let me know how this goes.

HTH


-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[Lenna930]

Hey there.

Sorry - should have mentioned that I had tried that already. Since my original query, however, I rearranged the formula within the SQL report and it seems to work now though I don't know why! If you can puzzle out why, I'd be grateful!

New formula: {PROJECT.lookup5}={?Area}and{PROJECT.lookup9}="l. Tender Lost"and {PROJECT2.date6}in{?Start Date}to{?End Date}or
{PROJECT.lookup5}={?Area}and {PROJECT2.date5}in{?Start Date}to{?End Date}

Thanks for your reply!

 

[TheBlondOne]

I think the reason that it wasn't working was because if the first part of the statement i.e {PROJECT.lookup5}={?Area}and{PROJECT.lookup9}="l. Tender Lost"and {PROJECT2.date6}in{?Start Date}to{?End Date} finds a match then in effect it will not continue to the next part of the statement (after the OR), if the first part of the statements doesn't meet the conditions required then it will go to the second part of the statement to obtain the results.

Funny things OR statements are as you can get into a bit of a pickle with them

)

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[lbass]

We can't tell from your SQL what belongs together. Even your last statement could be interpreted multiple ways:

(
{PROJECT.lookup5}={?Area}and{PROJECT.lookup9}="l. Tender Lost" and
{PROJECT2.date6}in{?Start Date}to{?End Date}
) or
(
{PROJECT.lookup5}={?Area} and
{PROJECT2.date5}in{?Start Date}to{?End Date}
)

Or, maybe it is:

{PROJECT.lookup5}={?Area}and{PROJECT.lookup9}="l. Tender Lost" and
(
{PROJECT2.date6}in{?Start Date}to{?End Date}
or
(
{PROJECT.lookup5}={?Area} and
{PROJECT2.date5}in{?Start Date}to{?End Date}
)
)

Or, maybe:

{PROJECT.lookup5}={?Area}and{PROJECT.lookup9}="l. Tender Lost" and
(
{PROJECT2.date6}in{?Start Date}to{?End Date}
or
{PROJECT.lookup5}={?Area}
) and
{PROJECT2.date5}in{?Start Date}to{?End Date}

Only you know what is intended. I think you would be better off adding the appropriate parens.

-LB

 

[Lenna930]

Thank you both. My colleague has also insisted that I use the parens and will from now on! Due to some testing I've done during the day I think TheBlondOne's statement in this case is very valid.