Home » SQL & PL/SQL » SQL & PL/SQL » DUPLICATION OF RESULT (ORACLE 10g, Solaris)
DUPLICATION OF RESULT [message #292300] Tue, 08 January 2008 08:32 Go to next message
funmi
Messages: 2
Registered: January 2008
Location: nigeria
Junior Member

THE CODE BELOW GIVE REPETIVE OUTPUT WHEN RUN. PLS COULD SOME ONE HINT ME ON WHAT TO DO TO MAKE IT SELECT DAT DISTINCTIVELY.



XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SELECT PER.last_name||','||PER.first_name||PER.middle_names "Appraisee Name"
,PER.employee_number
,PER.original_date_of_hire "Hire Date"
,MAN.full_name "Appraiser Name"
,APP.appraisal_period_start_date
,APP.appraisal_period_end_date
,APP.appraisal_date
,APP.status
,HR_GENERAL.DECODE_LOOKUP('APPRAISAL_TYPE',APP.type) "Appraisal Type"
,APP.appraisal_system_status
,ORG.name name
--substr(ORG.name,instr(ORG.name,'- ')+1,length(ORG.name)-instr(ORG.name,'-')) name
,SUBSTR(JOB.name,1,INSTR(JOB.name,'.')-1) Job
FROM per_appraisals APP
,per_all_people_f PER
,per_all_people_f MAN
,per_all_assignments_f ASG
,hr_all_organization_units ORG --Employee Org
,pay_cost_allocation_keyflex CAF
,per_jobs JOB
WHERE APP.appraisee_person_id = PER.person_id
--AND APP.type = 'YA'
--AND APP.business_group_id = xxcbn_hr_general.get_bg_id()
AND APP.appraiser_person_id = MAN.person_id
AND PER.person_id = ASG.person_id
AND PER.business_group_id = :p_bg_id
AND ASG.assignment_type = 'E'
AND ASG.primary_flag = 'Y'
AND ASG.organization_id = ORG.organization_id
AND ORG.cost_allocation_keyflex_id = CAF.cost_allocation_keyflex_id
&p_where
AND ASG.job_id = JOB.job_id(+)
AND APP.appraisal_date BETWEEN PER.effective_start_date AND PER.effective_end_date
AND APP.appraisal_date BETWEEN MAN.effective_start_date AND MAN.effective_end_date
AND APP.appraisal_date BETWEEN ASG.effective_start_date AND ASG.effective_end_date
--AND :p_date between APP.appraisal_period_start_date AND APP.appraisal_period_end_date
and APP.appraisal_period_start_date between :p_para_start_date and :p_para_end_date
AND APP.type = :p_app_type
AND APP.appraisal_system_status not in ('COMPLETED','DELETED')
UNION ALL
SELECT PER.last_name||','||PER.first_name||PER.middle_names "Appraisee Name"
, PER.employee_number
, PER.original_date_of_hire
, null "Appraiser Name"
, null appraisal_period_start_date
, null appraisal_period_end_date
, null appraisal_date
, null status
, null --HR_GENERAL.DECODE_LOOKUP('APPRAISAL_TYPE',:p_app_type) "Appraisal Type"
, 'NOT INIT' appraisal_system_status
, ORG.name name
, SUBSTR(JOB.name,1,INSTR(JOB.name,'.')-1) Job
FROM per_all_people_f PER
, per_all_assignments_f ASG
, hr_all_organization_units ORG --Employee Org
, pay_cost_allocation_keyflex CAF
, per_jobs JOB
WHERE PER.person_id = ASG.person_id
AND PER.business_group_id = :p_bg_id
AND ASG.assignment_type = 'E'
AND ASG.primary_flag = 'Y'
AND ASG.organization_id = ORG.organization_id
AND ORG.cost_allocation_keyflex_id = CAF.cost_allocation_keyflex_id
&p_where
AND ASG.job_id = JOB.job_id(+)
--AND :p_date BETWEEN PER.effective_start_date AND PER.effective_end_date
--AND :p_date BETWEEN ASG.effective_start_date AND ASG.effective_end_date

AND ASG.assignment_status_type_id NOT IN
(SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN')
AND NOT EXISTS(
SELECT 'a'
FROM per_appraisals APP
WHERE APP.appraisee_person_id = PER.person_id
--AND APP.type = 'YA'
AND APP.type = :p_app_type
--AND :p_date between APP.appraisal_period_start_date AND APP.appraisal_period_end_date
and APP.appraisal_period_start_date between :p_para_start_date and :p_para_end_date
AND APP.appraisal_system_status <> 'DELETED'
)
&p_ORDER
Re: DUPLICATION OF RESULT [message #292301 is a reply to message #292300] Tue, 08 January 2008 08:37 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Change UNION ALL to UNION

However, before posting any new questions, please read and FOLLOW the posting guidelines, particularly as it pertains to formatting posts here
Re: DUPLICATION OF RESULT [message #292302 is a reply to message #292300] Tue, 08 January 2008 08:39 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Well, first of all commenting out the following:

--AND :p_date BETWEEN PER.effective_start_date AND PER.effective_end_date
--AND :p_date BETWEEN ASG.effective_start_date AND ASG.effective_end_date

will result in all people ever inserted into your EBS database and all their assignments ever...

Besides that, I would suggest you start investigating the EBS datamodel on Metalink (specifically try to comprehend the date-tracking mechanism in HR). This statement is a bit too complex to give a solution just like that on a forum...
Previous Topic: Help in pivot query
Next Topic: TO_DATE function returns undesirable format
Goto Forum:
  


Current Time: Sun Dec 04 04:55:36 CST 2016

Total time taken to generate the page: 0.11343 seconds