Right outer Join issue

[morechocolate]

I have a report in which I am doing a right outer join to a table.


The set up is like so

TableA <-- TableB <-- TableC -- TableD

where <--- means right outer join and --- means equal join.

TableA and TableC are history tables, therefore, they have as of dates in each.

TableA also has a code that matches to TableB and TableB has a identifier that matches to Table C.

So you have:

from TableA
right outer join TableB on
TableA.code = TableB.code
right outer join TableC on
TableB.identifier = TableC.identifier
join TableD on
TableC.identifier = TableD.identifier

In the selection criteria I have set the as of date for TableA and TableC to equal a date parameter.

Thus, the where clause has

TableA.as_of_date = parameter and
TableC.as_of_date = parameter

The problem with this is that if TableC has an identifier that the link to TableA to TableB does not have, I am not getting that identifier I need from TableC.

Testing the SQL outside of Crystal if I do the following, I get what I need.

from TableA
right outer join TableB on
TableA.code = TableB.code
TableA.as_of_date = parameter
right outer join TableC on
TableB.identifier = TableC.identifier
join TableD on
TableC.identifier = TableD.identifier
where TableC.as_of_date = parameter

The problem is, from Crystal, if I put the TableA.as_of_date = parameter in the from clause, the parameter is actually not recognized. It will be saved as whatever was last entered by the user. It does not change with each new value given to the paramater. Is my only option to use a stored procedure or does someone know how I can get the information that I need via Crystal.

I am using Crystal 8.5

Thanks

 

[lbass]

The problem is that you have selection criteria on Table C, which is "undoing" your right outer join. Try removing the line:

TableC.as_of_date = parameter

Then, instead, use a formula in the report to look at records that meet the date criterion for Table C, as in:

if isnull({TableC.as_of_date}) or
{TableC.as_of_date} <> {?parameter} then 0 else {table.amount}

-LB

 

[morechocolate]

Thanks LB for your prompt response. If I did not have so many value fields in this report, I would take that route. I am going to suggest to our business unit that a TableB be changed to a history table. That is part of the problem. The data in TableB is changing, but there is not history, while the other two main tables do have history.

Thanks again

mc