Record Selection Formula - ORs and ANDs

[nettman]

I am using CR9 with an Oracle 9i database.

I have a sub-report that displays comments from an Oracle view. Below is the code I have in the Record Selection Formula


(
{DCLS_REPORT_COMMENTS.TESTGROUPNAME} = {?Pm-DCLS_CULTRPT_MAIN_VW.TESTGROUPNAME}
)
or
(
{DCLS_REPORT_COMMENTS.FOLDERNO} = {?Pm-DCLS_CULTRPT_MAIN_VW.FOLDERNO} and
{DCLS_REPORT_COMMENTS.REPORTABLE} = 'Y' and
isNull({DCLS_REPORT_COMMENTS.CONTACTID}) and
isNull({DCLS_REPORT_COMMENTS.REPORTSECTION})
)
or
(
{DCLS_REPORT_COMMENTS.CONTACTID} = {?Pm-DCLS_CULTRPT_MAIN_VW.CONTACTID} and
{DCLS_REPORT_COMMENTS.REPORTABLE} = 'Y' and
isNull({DCLS_REPORT_COMMENTS.FOLDERNO})
)


The TESTGROUPNAME, FOLDERNO, and CONTACTID are passed from the main report.

When I run the report, no data shows up for the comments. If I run the sub-report by itself and show the SQL query, I see that Crystal has removed the parentheses around each of my "OR" conditions and just put an open paren at the beginning of the entire formula and a close paren at the end of the entire formula. This, obviously changes the entire meaning of the formula.

Is this normal Crystal functionality? I am fairly new to Crystal, but have a lot of experience with other report writers. How do I get Crystal to leave my selection formula alone?

Thanks for any help

 

[synapsevampire]

Try placing the isnull checks first in each area.

(
isNull({DCLS_REPORT_COMMENTS.FOLDERNO})
and
{DCLS_REPORT_COMMENTS.CONTACTID} = {?Pm-DCLS_CULTRPT_MAIN_VW.CONTACTID}
and
{DCLS_REPORT_COMMENTS.REPORTABLE} = 'Y'
)
or
(
isNull({DCLS_REPORT_COMMENTS.CONTACTID})
and
isNull({DCLS_REPORT_COMMENTS.REPORTSECTION})
and
{DCLS_REPORT_COMMENTS.FOLDERNO} = {?Pm-DCLS_CULTRPT_MAIN_VW.FOLDERNO}
and
{DCLS_REPORT_COMMENTS.REPORTABLE} = 'Y' and
)
or
(
{DCLS_REPORT_COMMENTS.TESTGROUPNAME} = {?Pm-DCLS_CULTRPT_MAIN_VW.TESTGROUPNAME}
)

Getting the record selection to pass can be part voodoo at times, you may have to spin things around and get creative, let's hear if the above doesn't work.

-k

 

[nettman]

Thanks for the reply!!

Putting the ISNULL components first does not solve the problem. The report did bring back some data, but only because the comments that were there to be displayed matched the first criteria (before the first OR).

Crystal still removes my parens and places one at the beginning and end of the entire where clause.

I guess what your telling me is that I have to design a where clause that returns what I want without using any parens since Crystal just removes them. True????

I am actually trying to do this to overcome another "problem" I have with Crystal and this report. Previously I had 3 subreports - one for each of the "OR" pieces in the where clause. These comments are at the end of the report and were in 3 separate sections. Each section set to suppress if null. The design of the report is such that these comments often print near the bottom of the first physical page. What occassionally happens (IMHO) is that Crystal looks at the next section and determines that it needs to go to the next page. So it page breaks. But then the section is suppressed because there are no comments to print. The result being that a blank page is printed (except for the page header).

So I created an Oracle View unioning the comment data together and am trying to set the where clause to pull the needed comments in a single subreport in a single report section.

Sorry to be so wordy, but I am hoping that maybe you might have some insight into my "root" problem.

Thanks again for replying.

Dennis

 

[lbass]

In the subreport linking screen, try unchecking "choose data based on the field" for the subreport for each of the main report fields you want to conditionally link on. Keep the main report fields, so that you still see {?pm-table.field} in the bottom left, so that this is passed to the subreport for the conditional record selection formula. Not sure this will help, but it might.

-LB

 

[nettman]

It got the same result.

Thanks for replying, though.

 

[synapsevampire]

Sorry, I wasn't thinking, it should be AND, not or, test this:

(
isNull({DCLS_REPORT_COMMENTS.FOLDERNO})
and
{DCLS_REPORT_COMMENTS.CONTACTID} = {?Pm-DCLS_CULTRPT_MAIN_VW.CONTACTID}
and
{DCLS_REPORT_COMMENTS.REPORTABLE} = 'Y'
)
and
(
isNull({DCLS_REPORT_COMMENTS.CONTACTID})
and
isNull({DCLS_REPORT_COMMENTS.REPORTSECTION})
and
{DCLS_REPORT_COMMENTS.FOLDERNO} = {?Pm-DCLS_CULTRPT_MAIN_VW.FOLDERNO}
and
{DCLS_REPORT_COMMENTS.REPORTABLE} = 'Y' and
)
and
(
{DCLS_REPORT_COMMENTS.TESTGROUPNAME} = {?Pm-DCLS_CULTRPT_MAIN_VW.TESTGROUPNAME}
)

-k

 

[lbass]

SV, I think you were right the first time with the "or's", with the exception that I don't think this record selection statement will work after all, because each of the first two sets of elements (set off by or's) contain an isnull check that is replaced by a specific value in the other set. In other words, regardless of which set is first the second won't work accurately. Once a field is checked for a specific value, the isnull check doesn't work in a later clause. You might have to revert to two different subreports.

-LB

 

[nettman]

LB, That's what I was starting to think. I'll just leave it the way it is (3 subreports) and see if I can come up with a resolution to my blank page problem.

Thanks again for taking the time to help.

 

[synapsevampire]

If you use ANDs, then you'll get all of the conditions, I don't understand why that doesn't do what you want.

-k

 

[nettman]

ANDs can't work because the same field (folderno and contactid) is then being checked for two different values.

Folderno cannot be null AND equal to {?Pm-DCLS_CULTRPT_MAIN_VW.FOLDERNO}

This will never bring any data back if the parameter field has a value, which it does.