Left outer join with selection criteria on both tables 5

[Chats]

Hi,

I have two tables, WORK and DOCUMENT. I need to show all records from WORK where WORK.WORKTYPE='CM' and show related records from DOCUMENT where DOCUMENT.DOCTYPE='1017'

I have a left outer join between the two tables, but because I am putting selection criteria onto both tables Crystal is treating this as an equal join - therefore I am only getting records where WORK.WORKTYPE='CM' AND DOCUMENT.DOCTYPE='1017'.

I am using Crystal 8.5, I have tried both ODBC and Oracle drivers, both return the same result.

Please help!

Ade

 

[Hattusas]

If I get it right you are entering a selection criteria on the right table and you are using left outer join.
You must add this criteria to below :
Instead of typing
WORK.WORKTYPE='CM' AND DOCUMENT.DOCTYPE='1017';
(By the way I assume that your left table is worktype)
type
WORK.WORKTYPE='CM' AND ( isnull({TABLENAME2.RECORD})
or (DOCUMENT.DOCTYPE='1017') );

it is sure to work.

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[Chats]

Salih,

Thanks - that worked a treat.

Ade

 

[Chats]

Back again - I thought this had solved my problem but further investigation reveals further issues

The data is structured in two tables. One table called "Work" and the second called "Documents". The link between the tables is where Work.Location = Documents.Location (with an outer left join). The Work table is on the left, Documents on the right.

The Work table contains a number of work types (Work.WorkType), the Documents table contains a number of types of document (Documnets.DocType). There can be none, one or many records in the Work table for each location, and none, one or more than one records in the Documents table.

The previous answer solves two problems scenarios:

1. Where for a location there is a record in the Work table where Work.Worktype='CM' AND for the same location there is a record in the Documents table where Documents.DocType='1017'

2. Where for a location there is a record in the Work table where Work.WorkType='CM' AND there are no records for that location in the Documents table (the null clause)

Where I now have problems is:

3. Where for a location there is a record in the Work table where Work.WorkType ='CM', there are records in the Documents table where Documents.DocType=<another type> e.g Documents.DocType='1036'

What I need in the 3rd scenario is for the record from the Work table to be shown, with a blank from the Documents table. What is happening presently is that no record for this location is returned (because Document.DocType is neither null or equal to 1017.

Any ideas would be welcome!

Ade

 

[Ngolem]

I was waiting for that...Hattusas's solution is excellent when that third situation doesn't come up (ie. records exist but not the one you wish)

His solution only works where the record exists or there are no records at all.

As a result...you cannot perform the outter join by straight linking.

The only solution is to create a subreport of the right hand table...link it to the main report using the links that you normally use.

You use shared variables to return the results you want...the subreport is placed in a subsection before you want the results and is made as thin as possible to minimize its presence (you cannot suppress or conditionally suppress this section)

The subreport has all sections suppressed.

In the Subreport record select you use statements like the following

(isnull({Table.value}) or {Table.value} = {Subreport link})


An initialization formula is placed in the Subreport report header

@Initialization

Shared Numbervar Value1 := 0;
Shared Stringvar Value2 := &quot;&quot;;


If you need to group data make a formula to group on

@group1

If Not Isnull({Table.field}} then
{Table.field}
else
&quot;NullData&quot;; (or a number if {Table.field} is numeric)

In the detail section gather your data with a formula like

@Calc
WhilePrintingRecords,
Shared Numbervar Value1 ;
Shared Stringvar Value2 ;

If not isnull({Table.numfield}) then
Value1 := {Table.numfield};
If not isnull({Table.Strfield}) then
Value2 := {Table.Strfield};

then display the values in the subreport...this is useful for debugging since if you unsupress the report footer you can see the result in the main report

@display_Value1
WhilePrintingRecords,
Shared Numbervar Value1 ;

Value1 ;

This will work for you I think

Jim

 

[Chats]

I have tried the sub-report and shared variables. I have two details sections. My sub-report is in details(1) and my main report in details(2) - i.e. below the sub-report. I am not sure if I have correctly followed the advice above 100%

The sub-report works for me - with just one problem, performance. Crystal seems to be refreshing the sub-report for each record in the main report individually - the main report currently has approx 8000 rows which is taking about 30 mins on a PIII 850.

Additionally I am looking to pass approximately 30 values out of the sub-report for each location - so end up with a lot of variables. The final destination for the data is an Excel spreadsheet and I need all the data for a location on a single row - therefore cannot just show the subreport details above the main details.

Do I have the report structured correctly, is there any way to have the subreport run once and then the correct values passed to the main report for each of the 8000 rows in the main report?

Ade

 

[kenhamady]

That is the nature of subreports. You can't use one subreport and then distribute th records to the main report. And as you have found, a selection on the outer table nullifies the outer join.

Another alternative is to link the tables as you did originally, with an outer join, but remove the selection criteria on the 'outer' table. This will include all of the DOCTYPE records in the report. You ignore the extra records by suppressing the details with a condition. You also must write formulas with conditions so that they are not included in any totals. This will still be somewhat slower than the original report, but much faster than subreports.

If (by chance) there is a doctype that ALWAYS exists, you could select records that have either 1107 or XXXX (getting a complete list) and then ignore the XXXX records. This will speed things up a bit. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

Yes...this is a problem and I have run into it myself. This slows the report to a crawl when there are more that 200 or so calls to a subreport.

I have also used the approach that Ken suggested and often it is the &quot;least of evils&quot; I even went to the extent of having to draw in 500,000 records to process 40,000 real records...it took 20 minutes for the report to run but that was far superior to hitting a database 14,000 times using the subreport method.

It looks like you have that situation. One approach I took on the Record selection critia was to give a range of values so that Crystal didn't just return everything.

For example: if I wanted stock issues for a particular year I would have the Record Selection Formula say

(isnull({table.issueDate}) or
{table.issueDate} > date( year(currentdate) - 5,01,01))

This way I was looking at only 5 years of the database rather than 25 years and I was still catching items that had never been issued

But I don't think you can apply this in your case.

So I concur with Ken's advice....go with you original linkage but remove the restriction on Document type in the record Selection and move it into the formulas that process the data and/or suppress data lines.

jim

 

[Chats]

This seems to work for me.

I have around 200 document types, my selection formula is along the lines of

{WORK.WORKTYPE} = &quot;CM&quot; and
(isnull({DOCUMENTS.DOCID}) or (not (({DOCUMENTS.DOCID}) in ['1008', '1009', etc... ]

Thanks for help on this one!

Ade

 

[Hattusas]

I see that your situation is far way from normal situations.In order to solve such unexpected positions,sub report may be a solution but sub reports always decreases the performance.

I don't know what is your db is but I think you'd better create views.If you are able to build a useful view than there is no need to worry in Crystal part.

Concantrate on creating views.Search your solution in database part. Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com