Record Selection Criteria pulls data up to "OR", but not after

[rose4567]

Hi there,

I am using Crystal 10. I figured out WHY my record selection criteria is not working, but not sure how to fix. I know the "OR" statements I have in here are causing the statement to evaluation everything up to the "OR" statement and if it's able to return data, is ignoring everything AFTER the "OR" statement.

Can anyone suggest how I might structure the record selection criteria so ALL the statements are evaluated.

I am trying to pull records where there is a vendor number and if there is also address information, pick that up. Then if there is vendor_category_notes, pick that up as well and if there is system_user information, pick that up too. The criteria (as I've structured it below) works in SQL Query Analyzer, but I now realize that Crystal evaluates criteria differently than SQL. So where SQL will pick up the "OR" statements just fine, Crystal does not and that's where I am stuck now.

I've tried moving my parenthesis around, removing them, enclosing the individual pieces, enclosing the entire statement (in hopes it would evaluate the whole selection criteria as one statement), but that didn't work. Any suggestions are greatly appreciated. Thank you so much!


(({AUDIT_TRAIL_VENDOR.VENDOR_NBR} = {?vendorNbr}) OR

({AUDIT_TRAIL_VENDOR.ADDR_ID} = {ADDRESS.ADDR_ID})
AND ({AUDIT_TRAIL_VENDOR.TABLE_NAME} = "ADDRESS") OR

({AUDIT_TRAIL_VENDOR.TABLE_NAME} = "VENDOR_CATEGORY_NOTES") AND ({AUDIT_TRAIL_VENDOR.NOTE_ID} = {VENDOR_CATEGORY_NOTES.NOTE_ID}) OR

({AUDIT_TRAIL_VENDOR.TABLE_NAME} = 'SYSTEM_USERS')
AND ({AUDIT_TRAIL_VENDOR.SYSTEM_USER_ID} = {SYSTEM_USERS.SYSTEM_USER_ID}))

 

[lbass]

I'm not sure this will work, because you are setting these up to be conditional joins in a way. You should link FROM the Audit_Trail_Vendor table TO each of the other tables with a left outer join. Then change the parens to the following:

{AUDIT_TRAIL_VENDOR.VENDOR_NBR} = {?vendorNbr} OR
(
{AUDIT_TRAIL_VENDOR.TABLE_NAME} = "ADDRESS" AND
{AUDIT_TRAIL_VENDOR.ADDR_ID} = {ADDRESS.ADDR_ID}
) OR
(
{AUDIT_TRAIL_VENDOR.TABLE_NAME} = "VENDOR_CATEGORY_NOTES" AND
{AUDIT_TRAIL_VENDOR.NOTE_ID} = {VENDOR_CATEGORY_NOTES.NOTE_ID}
) OR
(
{AUDIT_TRAIL_VENDOR.TABLE_NAME} = 'SYSTEM_USERS' AND
{AUDIT_TRAIL_VENDOR.SYSTEM_USER_ID} = {SYSTEM_USERS.SYSTEM_USER_ID}
)
)

-LB

 

[rose4567]

Thanks for the reply, LB. You are always so helpful. Yes, each of the three tables are linked to Audit_Trail_Vendor using left outer joins. Unfortunately, the above did not work. That said, I played around with this some more after my post and finally got it to work by moving the {?vendorNbr} to the bottom of the statement and removed all the parens. In case anyone else is having a similar issue, I've pasted the code below that worked for me. It appears the {?vendorNbr} parameter must be at the bottom of this statement since that part drives the whole report and will always return results.


{AUDIT_TRAIL_VENDOR.TABLE_NAME} = "ADDRESS" and
{AUDIT_TRAIL_VENDOR.ADDR_ID} = {ADDRESS.ADDR_ID} or

{AUDIT_TRAIL_VENDOR.TABLE_NAME} = "VENDOR_CATEGORY_NOTES" AND
{AUDIT_TRAIL_VENDOR.NOTE_ID} = {VENDOR_CATEGORY_NOTES.NOTE_ID} OR

{AUDIT_TRAIL_VENDOR.TABLE_NAME} = "SYSTEM_USERS" AND
{AUDIT_TRAIL_VENDOR.SYSTEM_USER_ID} = {SYSTEM_USERS.SYSTEM_USER_ID} or

{AUDIT_TRAIL_VENDOR.VENDOR_NBR} = {?vendorNbr}

 

[lbass]

Removing the parens is NOT the answer--you are likely to have substantially incorrect data this way. It is probably true that you need that clause at the end, BUT you should also use parens as I showed in my first post.

-LB