Home » Developer & Programmer » Reports & Discoverer » report using join condition (report 6i,win XP)
report using join condition [message #496746] Wed, 02 March 2011 04:05 Go to next message
maddyd2k
Messages: 24
Registered: November 2010
Junior Member
Create a Report based on Join Conditions.
Eg.
1st Query : Select * from Emp
2nd Query : Select * from Dept
Join the Relation from 1st Query to Second Query

Apply following conditions:
a) if comm is null then hide the corresponding row
b) If sal is greater than average salary of there dept the display above average else display below average in the place holder column.

How can i do this???
Re: report using join condition [message #496751 is a reply to message #496746] Wed, 02 March 2011 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
a) write Format trigger on repeating frame (return FALSE if the condition is met)
b) create placeholder column; return datatype is CHAR. Write PL/SQL code that'll return message according to the requirement.
Re: report using join condition [message #496754 is a reply to message #496746] Wed, 02 March 2011 05:36 Go to previous messageGo to next message
maddyd2k
Messages: 24
Registered: November 2010
Junior Member
Hi
Actually my two queries are
first--- select * from emp
second--- select * from dept

i am joining first one with second queires

i have already tried what u said but i dnt have sal in dept group
So i cant do summary of salary

i am not gettin avg sal for each dept

Is there any other way??
Re: report using join condition [message #496772 is a reply to message #496754] Wed, 02 March 2011 06:45 Go to previous message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you think you have to have SAL in DEPT group? Salaries belong to employees, they are in EMP group. (By the way, note that "SELECT *" is not a good way to write code - always specify all columns you are selecting, one by one, such as "SELECT ename, sal, comm FROM ...").

As of "average salary" or "total salary" or "number of employees", you need to create a summary column, specify function you'd like to use, break group and - that's it. That summary column is to be located in EMP group as well.
Previous Topic: how can i develop this matrix report
Next Topic: placeholder problem
Goto Forum:
  


Current Time: Fri Apr 18 03:45:15 CDT 2014

Total time taken to generate the page: 0.27484 seconds