Home » SQL & PL/SQL » SQL & PL/SQL » create a function
create a function [message #307171] Tue, 18 March 2008 01:33 Go to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Hi all

I wish you can help me in this inquiry

I have to complete a report in Oracle discoverer as follows :

requirment : get the company employees counts distributed on there origin countries (nationalities) in one report
but I must exclude from this count any employee who is absconded

Details

1- the master table of employee is (emp_key) which contain those fields :

Emp_code Varchar2()
Emp_Name Varchar2()
Emp_position Varchar2()
Emp_Join_DT Date
...
...
...
Emp_Origin_contry Varchar2()

and my problem is here that the master table (emp_key) doesn't contain any field to indicate the employee abosconding status and this particular information is maintained in another table called (EVNT_EMP)(employees Events)
which contain this fields

EVNT_EMP_CODE - employee code
EVNT_DT - Event date
EVNT_TRX_CODE - Event transaction Code ( when
... EVNT_TRX_CODE ='ABSC' then I have to consider
... this Employee as absconded)

now, I cannot manipulate the structure of those two tables because they are masters and I have to produce a report by discoverer that will take the count from first table with excluding those employees existing in second table with TRX='ABSC'

I thought about it and I would prefer to create a custom ITEM in EUL attached to first table (employee master) in which I will name it EMP_STATUS as (case when EVNT_EMP.EVNT_TRX_CODE ='ABSC' then '1' else '2' ) but this again not working in EUL since EUL maintain Custom created ITEMs on table level and I cannot get any peice from another table.

I need to solve this in EUL level not on each report because this calculation will be used in creating more than 35 reports in the future and I don't need to duplicate the condition in each sheet.

any one can help me in creating a function that will check and return either 1 or 2 then I can create Custom ITEM based on this function results

please support.

Thanks
Re: create a function [message #307189 is a reply to message #307171] Tue, 18 March 2008 01:57 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What's the problem with join?
How is this an expert question?

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide

Regards
Michel
Previous Topic: more element in IN clause
Next Topic: DB user
Goto Forum:
  


Current Time: Wed Dec 07 10:18:10 CST 2016

Total time taken to generate the page: 0.12723 seconds