I need some help with an interactive report:
Here is the use case:
I have a table form
create table form (FORMID, COLUMN1 varchar2(50), MID NUMBER,MANID NUMBER);
MID and MANID are foreign keys to the table MANAGER
create table manager (MID number, COLUMN1 varchar2(30));
The manager can have different roles in the form.
Depending on the role the id of the manager will be set in different columns of the table.
However, all forms for which the manager has been entered,regardless of the role in the form, should still be displayed.
Here is the table with some sample data:
In the interactive report the manager with the id 15, should see both entries like this:
I used the following sql query for the interactive report:
SELECT * FROM FORM f LEFT OUTER JOIN MANAGER m ON m.MID = f.MID LEFT OUTER JOIN USERS u ON u.ID = m.ID LEFT OUTER JOIN MANAGER m2 ON m2.MID = f.MANID LEFT OUTER JOIN USERS u2 ON u2.ID = c.ID WHERE lower(u.username) = lower(:session_user_name) OR lower(u2.username) = lower(:session_user_name);and I also tried to use a union
FROM FORM f
LEFT OUTER JOIN MANAGER m ON m.MID = f.MID
LEFT OUTER JOIN USERS u ON u.ID = m.ID
FROM FORM f2
LEFT OUTER JOIN MANAGER m2 ON m2.MID = f2.MANID
LEFT OUTER JOIN USERS u2 ON u2.ID = m2.ID
However when testing the query in the sql command I get the two rows for the manager.
When using the same SQL Query for the interactive report, I only see one line for which the has been entered as MID in the form.
Can you help here?