Assistance with Outer Join and Selection Formula 1

[TimothyP]

I am using Crystal Reports XI against SQL server tables.

I have 3 SQL tables.

Table A – stores all contract numbers
Table B – uses the data element contract number
Table C - uses the data element contract number

I want to identify all contract numbers in Table A that are not found in either Table B or C.

This is what I've tried.

In Crystal, I joined Table A to Table B linking contract number field. Also, I joined Table A to Table C linking contract number field. I changed the link proporties to Left Outer Join for both links.

My selection criteria is
Isnull({TableB.contract_num}) and
Isnull({TableC.contract_num})

For some reason, I'm getting contract numbers on my report that are found in tables B and C.

Any ideas on what I'm doing wrong?

Thanks in advance for the knowledge.

TimothyP

 

[BeeBear]

Hi,

Your current selection criteria should only bring back a contract number if its not in BOTH table B and Table C.

So if theres a contract number in Table A and Table B but not in Table C, or Table A and Table C but not Table B, then the report will bring back those numbers.

Try changing your selection criteria to:

(
Isnull({TableB.contract_num}) OR
Isnull({TableC.contract_num})
)

This will bring back all contract numbers in Table A where there is either non match in Table B, or no match in Table C.

Hope that helps.

Thanks

BB

 

[lbass]

I think you are correct to use "and" in your select statement. You should be linking FROM table A TO table B, and FROM table A TO Table C, with a left outer in both cases, and no other selection criteria on Tables B or C, since that would undo the left join(s).

-LB

 

[dgillz]

Lbass is correct, the selection criteria on tables B and C nullifies the left outer join.

Use conditional suppression instead of record selection in these cases.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[TimothyP]

Thank you for your replies. However, I am still experiencing the same issue.

How can I use a conditional suppression without undoing the left outer join?

 

[lbass]

What is your entire record selection formula? Why would you want to use conditional suppression?

-LB

 

[TimothyP]

Hi lbass - thank you for your time.

My entire record selection formula is

Isnull({TableB.contract_num}) and
Isnull({TableC.contract_num})

Based on dgillz's response, I thought conditional suppression was my only alternative that would not undo my left join(s).

In response to your first reply, I am linking FROM table A TO table B, and FROM table A TO Table C, with a left outer in both cases, and no other selection criteria on Tables B or C.

Unfortunately, I'm still having the same issue.

Do I have to change any other "Link Options" other than "Link Type" to "Left Outer Join"?

The other default Link Options in effect are
Enforce Join is "Not Enforced"
Link Type is "="

Thanks again for taking the time.

 

[lbass]

I think your current setup should work, so please go to file->report options and make sure you do NOT have "convert nulls to default values" checked.

I don't think the "enforced" piece is relevant because you are referencing fields from the two right tables in the selection formula.

-LB

 

[TimothyP]

I DO NOT have "convert nulls to default values" checked.

Some additional information...

It is possible that the same contract number can be in BOTH Tables B and C.

It appears my report is selecting the contract numbers that reside in ONLY Table B or Table C, but not in both. The report is excluding the contract numbers that reside in BOTH Tables B AND C.

Again, my objective is to report the contract numbers that are ONLY in Table A.

 

[lbass]

I just tested this with the Xtreme database and it worked perfectly. I linked Orders to Credit and Orders to Top Customers (view) using left joins on the customer ID fields. Then I used a record selection formula of:

isnull({Credit.Customer Credit ID}) and
isnull({Top_Customers.Customer ID})

Only those customers in the Orders table who do not appear in the Credit or Top Customers tables appeared in the report.

It sounds like you still have an "or" in your record selection formula, but I will believe you if you say you are using an "and". I think you then have to put the three fields in the detail section and comment out the record selection formula and verify which IDs should in fact be appearing in your report.

-LB

 

[TimothyP]

It definately has something to do with the join(s). As you suggested, I removed all selection criteria and picked a contract number that is showing on this report that should not be.

At first, I only (outer)joined Table A to Table B and displayed TableA.contract# and TableB.contract# confirming the contract number resides in Table A, and more importantly, Table B.

Then, I (outer)joined Table A to Table C and displayed TableA.contract#, TableB.contract#, and TableC.contract#. For some reason, the contract number was displaying for TableA.contract# but NOT TableB.contract# anymore. As expected, TableC.contract# was blank. However, TableB.contract# field should have a contract# value.

Perhaps Table A being on one server/database and
Tables B and C being on a seperate server/database may have something to do with this.

 

[TimothyP]

Maybe it's not the join after all.

I am linking FROM table A TO table B, and FROM table A TO Table C, with a left outer in both cases, and no selection criteria at all.

I create a report to display TableA.contract# and TableB.contract#. This displays a contract number value in both TableA.contract# and TableB.contract#.

When I add TableC.contract# to the report, the contract number value in TableB.contract# goes away. when I remove TableC.contract# from the report, TableB.contract# comes back. Any ideas?

 

[lbass]

And for each table the links are between the contract numbers, right?

-LB

 

[TimothyP]

Yes, for each table the links are between the contract numbers. I just double-checked.

Also, each contract number in the primary Table A can be in one, many, or no records in Tables B and C.

 

[TimothyP]

I keep getting the following database warning in Crystal XI because of the multiple data sources. I don't think this is a problem?

Database Warning:

"More than one datasource or stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed.

 

[lbass]

It sounds as though there is a logical inconsistency so that any contract number in Table B cannot be in Table C or vice versa. Try a different approach altogether. Use a command as your datasource and set it up like this (using the syntax/punctuation appropriate to your datasource):

Select 'AB' as type, tableA.`contractno` as Ano, tableB.`contractno` as BCno
From tableA left outer join
tableB on
tableA.`contractno` = tableB.`contractno`

union all

Select 'AC' as type, tableA.`contractno`, tableC.`contractno`
From tableA left outer join
tableC on
tableA.`contractno` = tableC.`contractno`

Place {command.type}, {command.Ano} and {command.BCno} in the detail section and observe the results. If this somehow works in an expected way, you could add in a where clause on each side of the union, like:

where tableB.`contractno` is null [tableC.`contractno` for the second half of the union]

Please report back with sample results.

-LB

 

[TimothyP]

Thanks again lbass for your efforts.

I'll give it a try. But how do I go about using a command as a datasource against SQL tables?

 

[TimothyP]

Nevermind lbass, I found the 'Add Command' option. Let me see what I can figure out and I'll post back my results.