Left Outer Joins reporting off SQL Server

[vieja]

I am new to Crystal Reports. I have two tables which I want all items from Table A and matching items from Table B. I have used a left outer join for this purpose. However, if there are non existent values in Table B, what I end up with is an equal join (only those items in Table A which match Table B). After searching thru the knowledge base at Crystal Decisions, I found that Crystal arbitrarily ignores null values (even though SQL Server isn't designed this way). Does anyone know how I can get a true left outer join? Can I alter the query? If so, how? Thanks so much.

 

[synapsevampire]

It doesn't work that way for me, I'm guessing the real culprit is that you're canceling the Left Outer by adding some criteria to Table B, this is a no no, unless you qualify it further with something like OR isnull({tableB.Linkfield}) as part of the criteria.

Post your record selection criteria and the SQL Query.

-k

 

[vieja]

Obviously I am a real novice. Maybe if I explain what I am trying to do, you can help me. I have 2 tables. Table A has some employees (not all employees) with limits for various earning types. Table B has amounts (and dates) for earnings, deductions, and liabilities for all employees. I would like to get a report that shows me limits for all employees in Table A with a certain type of earning. I would like to get the corresponding amounts for these earnings from Table B. The problem is that if an employee does not yet have earnings listed in Table B for that type of earning in the date range requested, the employee in Table A does not show.

 

[synapsevampire]

Yeah, the Left Outer is probably nullified by virtue of saying that Table B must have some value.

Again, please post your SQL and the record selection formula, it's really the only way to assure that I understand what you have. Your text descriptions of the SQL are useful, but I need to know what's in the report too.

To get the SQL: Database->Show SQL Query

To get the record selection: Report->Edit Selection Formula->Record

-k

 

[lbass]

I would approach it this way:

1-Left join from TableA to TableB on {Employee ID}.

2-Create parameters for the earnings type and date range, if you want these to be user options.

3-Create a formula {@earnings} and place in details and suppress:

if isnull({TableB.earnings}) or
{TableB.earningstype} <> {?earningstype} or
not({TableB.date} in {?DateRange}) then 0 else {TableB.earnings}

4-Group on {TableA.EmployeeID}, insert a summary on {@earnings}, and drag it to the group header and then suppress the details section.

-LB

 

[vieja]

Ok - here is the SQL query:

SELECT
PRAE.EarnCode, PRAE.LimitOvrAmt,
PREA.PRCo, PREA.Mth, PREA.EDLType, PREA.EDLCode, PREA.Amount,
PREH.LastName, PREH.FirstName,
PREC.Description
FROM
{ oj ((Viewpoint.dbo.PRAE PRAE INNER JOIN Viewpoint.dbo.PREH PREH ON
PRAE.PRCo = PREH.PRCo AND
PRAE.Employee = PREH.Employee)
LEFT OUTER JOIN Viewpoint.dbo.PREA PREA ON
PRAE.PRCo = PREA.PRCo AND
PRAE.Employee = PREA.Employee AND
PRAE.EarnCode = PREA.EDLCode)
INNER JOIN Viewpoint.dbo.PREC PREC ON
PREA.PRCo = PREC.PRCo AND
PREA.EDLCode = PREC.EarnCode}
WHERE
PREA.PRCo = 1 AND
PREA.Mth >= {ts '2003-01-01 00:00:00.00'} AND
PREA.Mth < {ts '2003-05-01 00:00:01.00'} AND
PREA.EDLType = 'E' AND
PREA.EDLCode >= 70 AND
PREA.EDLCode <= 72 AND
PRAE.EarnCode >= 70 AND
PRAE.EarnCode <= 72

Here is the record selection:

{PREA.PRCo} = 1 and
{PREA.Mth} in DateTime (2003, 01, 01, 00, 00, 00) to DateTime (2003, 05, 01, 00, 00, 00) and
{PREA.EDLType} = &quot;E&quot; and
{PREA.EDLCode} in 70 to 72 and
{PRAE.EarnCode} in 70 to 72

PRAE is the table which has the limits. I want the report to show all employees in this table that have earnings codes of 70 - 72.

PREA is the table which has all of the amounts.

The other tables listed are just to clarify the report with actual employee names and earnings descriptions.

The report works except that it doesn't show employees from PRAE who don't yet have earnings with the codes 70 - 72 in PREA. I am trying to produce this report so I can tell an employee their balance for a particular earning.

The database I am reporting from comes from Bidtek (Viewpoint) - Microsoft SQL server.

Can you suggest a method that will accomplish what I need?

Thank you very much.