| Can we use self joins in Oracle Discoverer Desktop edition [message #115701] |
Wed, 13 April 2005 13:35  |
AG777
Messages: 3
Registered: March 2005
|
Junior Member |
|
|
Hi,
Can we use self joins in Oracle Discoverer Desktop edition?
E.g. I need to find the difference between the salary of an employee and his manager (the tables are from user "scott" in demo user of oracle).
The EMP table contains the Empno, MGRno, Salary and other columns. So to do this we use self-join. Can we get a similar listing in Oracle Discoverer.
Thanks
AG777
|
|
|
|
| Re: Can we use self joins in Oracle Discoverer Desktop edition [message #115743 is a reply to message #115701] |
Thu, 14 April 2005 02:27  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
You can't define this in the end user edition. However, if you define another folder refering to the same table (emp, in this case) in the administrator edition, with a join from (in this case) emp_no to manager, than you can use that in the end user edition.
If your EUL is used by "true end users", then think about a way to make it clear what you mean with the different folders. You might even consider creating a custom folder 'manager' with a statement like select <columns> from emp where emp_no in (select manager from emp_no) -> this ensures that you only show the managers in your new folder.
Regards,
Sabine
|
|
|
|
|
|
| Re: Can we use self joins in Oracle Discoverer Desktop edition [message #115827 is a reply to message #115701] |
Thu, 14 April 2005 10:53 |
AG777
Messages: 3
Registered: March 2005
|
Junior Member |
|
|
Hi Sabine and Paul,
Thanks for your suggestions.
I tried Pauls suggestion to click two fields in same folder(table) and right click ans select New join. It worked till here but when you click OK it displays this message
"The detail folder must be different than the master folder."
Thus a self-join cannot be created in Admin.
As of Sabine's suggestion it is good if only few tables need to be self-joined(I tried it and it works). The users in my organization need to self-join lot of tables in a business area, usually we have 40 tables(folders) in an business area and they may need to self-join any of those tables. So then i have to create 40 more folders and create joins between them.
Is there any easier way or work around for this problem?
The example I gave you was just a general example, we don't use emp tables, we have different tables in each business area and the unique key is composed of multiple fields within each table, so we have self-join with those fields.
Thanks for your suggestions
AG
|
|
|
|
|
|
| Re: Can we use self joins in Oracle Discoverer Desktop edition [message #116744 is a reply to message #116018] |
Thu, 21 April 2005 15:28 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Good that Premier is having a good time. I have no clue why, but okay.
Anyway, about the 40+ self joins... I'm not sure, but did you try to make up a "fake intersection folder"? I mean something like:
Let say we a table with columns detail_id and master_id (where master_id can refer to detail_id in same column) and a load of other columns. Then in Discoverer create a folder with all columns of the table, another one with just the columns required for the selfjoin(s) and one folder with the same table again. Now join each of the "complete" table-folders with the appropriate columns of the fake intersection.
If you give a more detailed example, we can find out if this works!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
hh