Records excluded when select statements are combined with "OR"

[Davest11]

I'm using Crystal v10 on a Progress database. Has anyone ever experienced a situation where including either side of an OR statement in a set of selection criteria returns the expected results but, when both sides are included, some records are excluded, seemingly at random?

Thanks,

-David

 

[Turkbear]

Hi,
Please post an example of what you mean..Especially, post a selection formula that results in the problem you are seeing..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

If the OR isn't working, then try using 2 different statements, as in.

(
{table.field} = "Huh?"
)
or
(
{table.field} = "Wuzzat?"
)

Or you may want to try:

(
{table.field} in ("Huh?","Wuzzat?")
)


-k

 

[Davest11]

When I use the following selection criteria:

(if {?Division Code} = "All Divisions" then {Fund.divcode} <> "" else {Fund.divcode} in {?Division Code}) and
(if {?Fund Rep Type} = "All Rep Types" then {Fund_Rep.Rep_Type} <> "" else {Fund_Rep.Rep_Type} in {?Fund Rep Type}) and
(if {?Profile ID Code} = "All Profiles" then {sql_Profile.IDCode} <> "" else {sql_Profile.IDCode} in {?Profile ID Code}) and
totext ({Fund.date_inactive}) = "" and
{Fund.established} < {@EndQDate} and
{Fund.fundid} = {?Fund ID}

I get the results that I'd expect. When I use these criteria:

(if {?Division Code} = "All Divisions" then {Fund.divcode} <> "" else {Fund.divcode} in {?Division Code}) and
(if {?Fund Rep Type} = "All Rep Types" then {Fund_Rep.Rep_Type} <> "" else {Fund_Rep.Rep_Type} in {?Fund Rep Type}) and
(if {?Profile ID Code} = "All Profiles" then {sql_Profile.IDCode} <> "" else {sql_Profile.IDCode} in {?Profile ID Code}) and
totext ({Fund.date_inactive}) = "" and
{Fund.established} < {@EndQDate} and
{Fund.master_fundid} = {?Fund ID}

I also get the expected results. However, when I combine the bolded lines as below:

(if {?Division Code} = "All Divisions" then {Fund.divcode} <> "" else {Fund.divcode} in {?Division Code}) and
(if {?Fund Rep Type} = "All Rep Types" then {Fund_Rep.Rep_Type} <> "" else {Fund_Rep.Rep_Type} in {?Fund Rep Type}) and
(if {?Profile ID Code} = "All Profiles" then {sql_Profile.IDCode} <> "" else {sql_Profile.IDCode} in {?Profile ID Code}) and
totext ({Fund.date_inactive}) = "" and
{Fund.established} < {@EndQDate} and
({Fund.master_fundid} = {?Fund ID} or {Fund.fundid} = {?Fund ID})

There are records missing. I've been over and over them, and can find no rhyme or reason as to why the missing records are missing.

-David

 

[lbass]

Can one or both of these fields be null? If only one can be null, place the one that can be null in the last clause. Then the first clause will evaluate regardless of nulls in the second.

-LB

 

[synapsevampire]

That is sort of odd.
Try:

(
if {?Division Code} = "All Divisions" then {Fund.divcode} <> "" else {Fund.divcode} in {?Division Code}) and
(if {?Fund Rep Type} = "All Rep Types" then {Fund_Rep.Rep_Type} <> "" else {Fund_Rep.Rep_Type} in {?Fund Rep Type}) and
(if {?Profile ID Code} = "All Profiles" then {sql_Profile.IDCode} <> "" else {sql_Profile.IDCode} in {?Profile ID Code}) and
totext ({Fund.date_inactive}) = "" and
{Fund.established} < {@EndQDate} and
{Fund.master_fundid} = {?Fund ID}
)

or

(
if {?Division Code} = "All Divisions" then {Fund.divcode} <> "" else {Fund.divcode} in {?Division Code}) and
(if {?Fund Rep Type} = "All Rep Types" then {Fund_Rep.Rep_Type} <> "" else {Fund_Rep.Rep_Type} in {?Fund Rep Type}) and
(if {?Profile ID Code} = "All Profiles" then {sql_Profile.IDCode} <> "" else {sql_Profile.IDCode} in {?Profile ID Code}) and
totext ({Fund.date_inactive}) = "" and
{Fund.established} < {@EndQDate} and
{Fund.fundid} = {?Fund ID})
)
If that works, you're functional.

The parsing for the record selection formula is tricky, and I didn't bother to test your record selection formula, nor analyze it closely, however there are numerous reasons why it may not pass correctly, but the real test is what is being passed to the database, so check the database->Show SQL Query.

-k

 

[Davest11]

Thanks much for all your help!! I had tried an approach like what you initially suggested, synapse, (the 'in' statement) but it hadn't worked because the parameter allowed for a range of values. This version of the report doesn't require that, however, so I'm going to try your approach with a discreet value parameter.

Lbass also brings up an excellent point, as it is not only possible, but likely, that one of these values could be null, and that is the one that I had first in the statement.

I'm going to try a combination of these two solutions, and I'll post when I get the results.

Thanks again,

-David

 

[Davest11]

Very cool...using the statement

{?Fund ID} in [{Fund.fundid}, {Fund.master_fundid}]

did the job. If I'd needed to use the range parameter, my guess is that I'd probably have had to use an if-then to see if one of the values was null and alter the selection accordingly, but I was able to avoid that by using the discreet parameter.

Thanks again!!

-David

 

[synapsevampire]

You could also just turn on the Options and set the convert nuill value to default to get rid of this. If you aren't null checking, than this is very useful for such things.

-k