Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 24, 2008 at 10:13 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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' )

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2008 at 05:35 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2008 at 07:20 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2008 at 09:31 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2008 at 11:40 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 24, 2008 at 04:36 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.