Home » Developer & Programmer » Reports & Discoverer » Query in Reports (Oracle 10g, Windows XP)
Query in Reports [message #438532] Sun, 10 January 2010 11:48 Go to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi All,

I have a group above report which works perfectly fine and looks like this:

empnum empname leavetype ..................
100 abc 1
101 def 6
102 hij 12

Now, I have another table where:

Leave.code Leave.name
1 annual leave
..
6 sick leave

etc etc....

I need my report to display the leave.name under leavetype rather than displaying the code. I cannot take the leave.name directly from the table, so need to write a function where based on each leave.code, the corresponding leave.name is displayed.

Any help with the query for this or pointers would be highly appreciated.

Regards,
Sreejith
Re: Query in Reports [message #438533 is a reply to message #438532] Sun, 10 January 2010 12:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I cannot take the leave.name directly from the table
Why not? It is the most straightforward way to do that; something like
select e.empnum, e.empname, l.name leave_name
from employees e, 
     leave l
where l.code = e.leave_type



If you insist on a function, you can do that using several options. One of them is to create a formula column which would look like
  retval leave.name%type;
begin
  select l.name 
    into retval
    from leave l
    where l.code = :leave_type;   -- :leave_type is a column fetched
                                  --  in the main report query
  return (retval);
end;



Another option is to create a database function and call it from the report:
create or replace function fun_leave_type (par_leave_type in number)
  return char
is
  retval leave.name%type;
begin
  select l.name 
    into retval
    from leave l
    where l.code = par_leave_type;   -- par_leave_type is argument passed from the report
  return (retval);
end;

In the report's query:
select e.empnum, e.empname, fun_leave_type(e.leave_type) leave_name
from employees e



There might be other options, but I guess that those three are the most obvious. However, the first one is the most trivial. Why can't you use it, again?
Re: Query in Reports [message #438534 is a reply to message #438533] Sun, 10 January 2010 12:25 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Littlefoot,

Thanks a ton. One of this has to work when I hit the office desk tomorrow. Appreciate the response.

Regards,
Sreejith
Re: Query in Reports [message #438677 is a reply to message #438534] Mon, 11 January 2010 13:43 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Littlefoot,

I did try out the second method today. But there seems to be some confusion i guess. Maybe the table's I mentioned dint really give out the right information.

The reason I cant use the leave.name directly is because there is no way to link it to any of the other three tables that i've used in the query for my report whereas the leave.id column is part of one of those tables and hence it is returned in the report.

So here we go again...the report actually looks like this:

empnum empname absencetype
1 abc 21
2 def 12
3 hij 6

and the table that cant be linked to the ones i use in my query looks like this: (absence_types_f)

absencetype absencename
21 annual leave
12 sick leave
6 death leave

etc...

Again, in the main report I need the absencename to be displayed under absencetype. Also, I'm using the format trigger from the property inspector to do this. Hope that's the right way to go about it.

Regards,
Sreejith
Re: Query in Reports [message #438682 is a reply to message #438677] Mon, 11 January 2010 14:38 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid I don't understand what you are saying.

Your words are contradicting what you are displaying.

Quote:
The reason I cant use the leave.name directly is because there is no way to link it to any of the other three tables that i've used in the query for my report

How can that be true? Report results:
empnum empname absencetype
1      abc     21
2      def     12
3      hij      6
A table that contains absence names:
absencetype absencename
21          annual leave
12          sick leave
 6          death leave

So how can you NOT connect the "upper absencetype" (from the report results) with the "below absencetype" (from the table that contains absence names)?

Perhaps I'm missing something, but - looking at it - it is obvious that those two "absencetype" columns CAN be joined. You'd have to add that table into the FROM clause (so you'd then have 4 tables instead of 3, but - who cares?).

What is wrong with my thoughts?
Re: Query in Reports [message #438791 is a reply to message #438682] Tue, 12 January 2010 04:59 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Littlefoot,

Apologise for the lack of clarity.

The reason absence_type is being displayed in the report is because it is part of two different tables. The absence_type that is displayed in the report is not queried from the table that I have mentioned above.

Just to make myself more clear, I have a table hrp_people which has absence_type as one of the columns and from where the report queries the data.

I have another table absence_types_f which again has absence_type and absence_name as columns and the data as shown above.

Aprreciate your response.

Regards
Sreejith
Re: Query in Reports [message #438806 is a reply to message #438791] Tue, 12 January 2010 06:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, so where is the problem?
  • Report displays HRP_PEOPLE.ABSENCE_TYPE value.
  • Do any of the options described above, doesn't matter which one, but adding the ABSENCE_TYPES_F table into the FROM clause is the easiest:
    select empnum,
           empname,
           f.absence_name                    --> newly added column
    from table_1,
         table_2,
         hrp_people h
         absence_types_f f                   --> newly added table
    where t1.some_column = t2.some_column
      and t2.some_other_column = h.some_column
      and h.absence_type = f.absence_type    --> newly added condition

I must be quite stupid for nut understanding what you are saying ...
Re: Query in Reports [message #438826 is a reply to message #438806] Tue, 12 January 2010 07:52 Go to previous messageGo to next message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Littlefoot,

The report is fine finally. Used your steps and it worked fine. Really appreciate all your responses.

Regards,
Sreejith
Re: Query in Reports [message #438827 is a reply to message #438826] Tue, 12 January 2010 07:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it fine?

If not, say so; there are other people here who might help (if I'm unable to).
Re: Query in Reports [message #439497 is a reply to message #438827] Sun, 17 January 2010 22:39 Go to previous message
sreejith.s
Messages: 26
Registered: November 2009
Junior Member

Hi Littlefoot,

I mentioned above. The report works totally fine.

Regards,
Sreejith
Previous Topic: creating a parameter to pull ALL records or one in reports
Next Topic: Could not start report server
Goto Forum:
  


Current Time: Sun Dec 11 02:19:01 CST 2016

Total time taken to generate the page: 0.04886 seconds