Analytics Software

Just a question here. I am developing a workbook in Discoverer Plus. I am getting an ORA-01417 message - a table may be outer joined to at most one other table. Just trying to see if there is any way to eliminate this error message. What I am doing is this. I am joining table PA_EXPENDITURE_ITEMS_ALL to PA_EXPENDITURES_ALL. That is a regular join. Detail exists in master. Next I am joining PA_EXPENDITURES_ALL to PER_ALL_EMPLOYEES_F. Since I have plenty of project expenditures that are NOT payroll/labor related, my person id in the expenditures all table is often null. So that particular join has to be an outer join, where I want to return both detail that matches to a master record, and detail that does not have a match. So far, nothing unusual there. The catch is that an employee can have multiple records in the employees file. That would give me duplicate rows of detail data when I do have a match, which would be bad. So what I am doing is adding a condition to test for expenditure item date between employee effective start date and employee effective end date. So this is a condition I am adding in the workbook. There is NO join defined between PA_EXPENDITURE_ITEMS_ALL and PER_ALL_EMPLOYEES_F. But what Discoverer does is when it builds its SQL, it is putting the outer join symbol on this condition. Which does seem to make sense why it would do that. After all I will have detail expenditures with no employee records for them. So I will have to have a way to handle null situations. Anyway, when I added this condition and tried to refresh the workbook that is when I get the ORA-01417 message. So I have been doing some playing around. I figured out that if I build a condtion like this - AND ei.expenditure_item_date BETWEEN nvl(per.effective_start_date,ei.expenditure_item_date-1) AND nvl(per.effective_end_date,ei.expenditure_item_date+1)
then manually running an SQL statement, I get the right results. When I create this condition in Discoverer, Discoverer still ends up adding the left outer join symbol and I still get my ORA-01417 message. Does anyone know of a sneaky way to keep Discoverer from adding this outer join symbol on my condition when Discoverer builds its SQL statement? Hopefully I have explained the situation pretty well. Always a challenge to accurately describe an issue.

John Dickey