Hi,

I have a table called EMPLOYEES(term, emplid, title). Terms are 2001, 2002, 2003, 2004, etc. My objective is to see if the title has changed for an employee in Term 2004 compared against Term 2003. It also means that any employee in Term 2004, who does not exist in Term 2003, should also be included in the resultset (which means term, emplid, title would be displayed as NULL for this employee).

I am doing a Left Outer Join on Emplid in Crystal Reports but it is not giving me the desired results. This is the 'Show SQL Query...' (I reformatted for easier understanding).
SELECT
    E_2004.TERM, E_2004.EMPLID, E_2004.TITLE,
    E_2003.TERM, E_2003.EMPLID, E_2003.TITLE
FROM
    EMPLOYEES E_2004,
    EMPLOYEES E_2003
WHERE
    E_2004.EMPLID = E_2003.EMPLID (+) AND
    E_2004.TERM = '2004' AND
    E_2003.TERM = '2003'

I know the reason why it is not giving the desired results. It is effectively doing an Equal Join because "E_2003.TERM = '2003'" condition. This equality condition nullifies the effect of left outer join.

I got one solution to get desired results...is do a left outer join on Term too, and then edit the custom SQL, and remove "E_2004.TERM = E_2003.TERM (+)", and modify the abovementioned condition to E_2003.TERM (+)= '2003. It works BUT then I lose the control over other wizards like Select Expert, and then have to do everything using custom SQL.

Another thing I tried was using a formula for writing the condition in this way.
(E_2003.TERM IS NULL OR E_2003.TERM = '2003')
But this solution is not working and still giving me Equal Join results.

I want to avoid editing the custom SQL and would like some other solutions to attain the objective. Also I do not want to create a view on database side to do the outer join, and use view to create the reports because if I want to compare against another term , i will have to create another view.

I have spent enough time and energy on it. I believe there is some way out to this problem which I am not able to figure out in Crystal. Please help me and provide any solutions if you have. Thanks in advance!
Navin