Table JOIN 1

[ShortyA]

Hi,
I am using CR9 Professional against SQL Server 7.0 connecting via ODBC using the Microsoft SQL Server driver.

I have two tables (W03 and W01) that are being joined on a field called CRDATTIM. In Database Expert I have setup the join to be a left outer on CRDATTIM so that if there are no values in W01 then the W03 record is still retrieved.

The W01 table has too many values in so I have added a line into the record selection formula to check for a specific value ie {W01.MyField}='XYZ'

Now records seem to be retrieved only if there is a value in W01 and the left outer join seems to have been ignored. How can I set this join up but limit the records ?

Thanks for any help.
ShortyA

 

[Naith]

The left outer join got converted to an equi-join because you applied a condition to the table on the outer joined table.

You can't maintain a left outer join while using this table in your where clause. You'll have to either change your datasource to a view or stored proc, or split the dataset up via a subreport.

Naith

 

[ShortyA]

Naith,
thanks for clearing that up and pointing out some solutions! I will give the subreport option a go.

ShortyA

 

[lbass]

You could also leave the left join as is, remove the selection on the second table, and then create a formula:

if isnull({table.field}) or
{table.field} <> "XYZ" then "" else {table.field}
//whatever you want to display

-LB