Print Page | Close Window

Problem with Outer Join and Microsoft SQL Db's.

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1309
Printed Date: 05 May 2024 at 4:15am


Topic: Problem with Outer Join and Microsoft SQL Db's.
Posted By: Sensei
Subject: Problem with Outer Join and Microsoft SQL Db's.
Date Posted: 12 Sep 2007 at 12:27pm
Hello all, I'm hoping someone can help me.

Our ERP system runs on M$ SQL.  I try not to meddle with it because any changes are ruined when we upgrade to the latest version of the software. So, I created another small database on the same server with the additional information needed for my reports. 

 
Table 1 is the list of our outside sales reps and their e-mail addresses.

The 2nd set of tables are sales tables from our ERP database. 

Here is my problem.  I am writing a weekly sales report and using an outside program to automate things, I am e-mailing them to our sales reps.  However, if a rep doesn't have any sales, then nothing comes up in crystal when that rep number is entered as a parameter.  Knowing that I wanted a report of some kind to result, I tried to do a left outer join from the rep number field in table 1 (from my database) to the second set of tables I get no records.  If I get no records, then I cannot pull the e-mail address from my table to e-mail a blank report. 

 
I upgraded to XI Developer because I had read on the web that CR9 had problems with outer joins. 

 
What am I doing wrong? 

TIA. 



Replies:
Posted By: p&ccrystal
Date Posted: 12 Sep 2007 at 1:08pm
The problem is not the join but the fact the salesperson had no sales. If the sales total shows a null instead of a zero, the result you described is what you should get every time. If it is possible to set a default value in the design of the table that keeps the sales values to zero, then the email addresses for salespeople with no sales will appear.
 
Hope this works for you.


-------------
10 year user of Crystal Reports - recently implemented Crystal Server 11.5


Print Page | Close Window