asked on
How can i achieve Outerjoin type of results usong oracle Reports Datalink option
Hi,
i am developing a Oracle Report. i am using Datalink to maintain parent-child relationship between 2 queries. If no matching value found then i want to return 0 Records. how to achieve this.
Ex: I have 2 tables like Dept & EMP
DEPT has 2 rows HR and FINANCE
I have only one employee in HR and no employee in FINANCE.
I am using the below datalink properties:
SQL clause : WHERE
CONDITION ; =
PARENT GROUP: DEPT
PARENT Column : DEPTID
CHILD QUERY : EMP
CHILD Column : DEPTID.
In the report it is showing matching columns without any issues> i want non matching columns also with Zero employess like
HR 1
FINANCE 0
I need OUTER JOIN kind of thing. How can i achieve this. Just i have written one example. I have very complex query to implement this and datalink is absolutely necessry for me. Any help please.
i am developing a Oracle Report. i am using Datalink to maintain parent-child relationship between 2 queries. If no matching value found then i want to return 0 Records. how to achieve this.
Ex: I have 2 tables like Dept & EMP
DEPT has 2 rows HR and FINANCE
I have only one employee in HR and no employee in FINANCE.
I am using the below datalink properties:
SQL clause : WHERE
CONDITION ; =
PARENT GROUP: DEPT
PARENT Column : DEPTID
CHILD QUERY : EMP
CHILD Column : DEPTID.
In the report it is showing matching columns without any issues> i want non matching columns also with Zero employess like
HR 1
FINANCE 0
I need OUTER JOIN kind of thing. How can i achieve this. Just i have written one example. I have very complex query to implement this and datalink is absolutely necessry for me. Any help please.
Oracle DatabaseJava App Servers
Last Comment
Milleniumaire
ASKER
I can't because it is really very big query. i want to split this query into small queries for maintenance.
I have modified the queries to display the data always (atleast null values). But now i have one issues.
here is the actual situation.
My master query is returning 2 rows
ID Desc
1 HR
2 FINANCE
I have 2 child queries.
First Child query is returning the below values.
ID RegEmpCount
1 50
2 20
First Child query is returning the below values.
ID UnRegEmpCount
1 10
2 40
In my report I want to display like this
ID DEPT RegEmpCount UnRegEmpCount TargetMet?
1 HR 50 40 Yes ( RegEmpCount - UnRegEmpCount >0)
ID DEPT RegEmpCount UnRegEmpCount TargetMet?
2 FINANCE 20 40 NO ( RegEmpCount - UnRegEmpCount >0)
How to write TargetMet? formula? RegEmpCount is from one group and UnRegEmpCount is another group. is ther any way? Please suggest me.
I have modified the queries to display the data always (atleast null values). But now i have one issues.
here is the actual situation.
My master query is returning 2 rows
ID Desc
1 HR
2 FINANCE
I have 2 child queries.
First Child query is returning the below values.
ID RegEmpCount
1 50
2 20
First Child query is returning the below values.
ID UnRegEmpCount
1 10
2 40
In my report I want to display like this
ID DEPT RegEmpCount UnRegEmpCount TargetMet?
1 HR 50 40 Yes ( RegEmpCount - UnRegEmpCount >0)
ID DEPT RegEmpCount UnRegEmpCount TargetMet?
2 FINANCE 20 40 NO ( RegEmpCount - UnRegEmpCount >0)
How to write TargetMet? formula? RegEmpCount is from one group and UnRegEmpCount is another group. is ther any way? Please suggest me.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
In the data model you would create two groups from the single query i.e. a parent and a child. The report layout will be exactly the same as if you had used two queries as it is based on two groups.