Need filter on left outer join to be in join not where clause

[Former Member]

Hi

We are using Crystal Reports 11, (11.0.0.1282) using odbc connection to a SQL Server 2005 database. When I link one table to another using a left outer join, it uses the left outer join syntax successfully. When I need to put a filter on the outer joined table, it puts that filter in the where clause instead of the join clause. So when it runs the sql statement it in effect creates an inner join.

I can't imagine it is supposed to operate this way. Does anyone know of a fix to this other than having to take the sql out and put it in as a command in order to get the syntax correct? Having to do this prevents the average user who doesn't know sql from doing quite a few reports because many need the left outer join.

crystal reports creates this example:

select e.name, e.employee_id, a.street

from employee e

left outer join address a on e.person_id = a.person_id

WHERE a.state like 'NE'

I need something like this example:

select e.name, e.employee_id, a.street

from employee e

left outer join address a on e.person_id = a.person_id

AND a.state like 'NE'

-- The difference is that my result set on the second one pulls all employees and then only the addresses for those whose state is NE. The first select only pulls employees who have state NE. It in effect turns it into an inner join by putting the filter in the where statement.

I am wondering if it is our odbc driver?

Our driver is odbc sql server 2000.85.1117.00

SQLSRV32.DLL

If you can help me out I would really appreaciate it!!!!

Julie

[Former Member · Accepted solution]

Thanks for everyone's responses, I know how to work around the problem with sql, I am wanting to see if there is a way to make the outer joins filter go in the join clause instead of the where clause with Crystal Reports standard functionality.

We have some Crystal Reports users that are not sql users, i.e. benefit specialists, payroll specialists and compensation analysts who have Crystal Reports. I was hoping this functionality was available for them. I just made my example a simple one, but often reports have multiple outer joins with maybe 2 or three of the outer joins needing a filter on them that won't make them into an inner join.

Such as

Select person information

outer join address record

outer join email record

outer join tax record (filter for active state record & filter for code = STATE )

outer join pay rates record

outer join phone#s (filter for home phone#)

I thought maybe the functionality may be available, that I just don't know how or where to use it. Maybe it is just not available.

If it is not available, I will probably need to setup some standard views for them to query, rather than expecting them to pull the tables together themselves.

[Former Member · Accepted solution]

I found the difference in using the parenthesis on the join few years back while I was experiencing the problem with different record numbers.

Try the following Statement.

Select e.name, e.employee_id, a.street

from employee e

left outer join address a on (e.person_id = a.person_id

AND a.state like 'NE' )

[Former Member]

Hi Julie,

Ok, I have to play "you know what" advocate on this one.

I see nothing wrong with either statement.

The join statement is a filter. Say, in Oracle, the join might be

something like "where a.id = b.id", whereas in SQL the

innerjoin is used. Even in SQL, you can replace the innerjoin with

"where a.id = b.id" and it's all the same.

The filter for both examples looks just right to me and it appears

that both would bring in the same data.

You could group all the 'NE' records , and then a separate not 'NE' group,

in your query.

Or,

You can bring in the intial records that you want with the query

and then filter them further in Crystal.

Or,

Bring in the records you want on the main report and use a sub report

to filter on 'NE'.

Best to do it all in the query if possible.

I hope this helps,

The Panda

[Former Member]

I tried Dell's solution of adding an is null to the where clause on the a.state, using the same syntax with an or between the null and state, but the query continues to run as an inner join. I also tried putting the is null on the person id column and it ran the same.

I even cut and pasted the sql into Sql Server Management Studio and it runs the same, as an inner join.

Thanks for the suggestion! Any other ideas?

Julie

[Former Member]

Try using union

select e.name, e.employee_id, a.street

from employee e

left outer join address a on e.person_id = a.person_id

WHERE a.state like 'NE'

union

select e.name, e.employee_id, a.street

from employee e

left outer join address a on e.person_id = a.person_id

WHERE a.state is null

Raghavendra

[Former Member]

Julie,

Is there a particular reason that you are opposed to using Shawn's advise and just putting the SQL code into a Command object?

Just wandering,

Jason

[Former Member]

Julie,

When you said:

Having to do this prevents the average user who doesn't know sql from doing quite a few reports

You said a mouth full. No WYSIWIG editor will ever replace hand coding. Doesn't matter weather it's SQL, HTML or any other language.

Those who have to rely purely on computer generated code will ALWAYS be at a disadvantage compared to those who can hand code, or at least hand edit computer generated code.

It would be nice, however, if CR would allow user to edit it's code without having to cut and paste it into a command.

Jason

[Former Member]

Another option is to do something like this in the selection criteria to keep the outer join working correctly:

(IsNull({a.State}) or {a.State} like 'NE')

-Dell

[Former Member]

A quick solution would be to write the SQL it should be using and have the report use it as a Command Object. This will make Crystal do the SQL you know is correct