This content has been marked as final. Show 10 replies
Log on to Designer-> Open repository folder->Open mapping->
Check out the mapping for changes
you have go for version check out and check in for Mapping, session and Workflow.
If helps Pls mark correct or helpful
For pointing me towards edit. Why i need to edit really is
For my one of the failed task in DAC which is named "SDE_ORA_PersisterStage_WorkforceEvent_Salary" while checking under the sesslogs for that task, i found the below oracle error like
ORA-00907: missing right parenthesis, which implies like missing parenthesis
So for this error what i did is went to the designer and under the mappings of that task, i double clicked the source qualifier and in under the properties tab i seen the sql query of the task and that is below
When i tried to execute the above query in toad, over there also it shows that missing right parenthesis. From the query i identified that the error in the below line in select statement
SELECT assignment_id ,person_id ,pay_proposal_id ,proposal_reason_code ,CASE WHEN proposal_reason_code IS NOT NULL THEN 'PROPOSAL_REASON' END proposal_reason_type ,effective_start_date ,effective_end_date ,business_group_id ,salary ,annualization_factor ,salary_change_ind ,currency_code , NVL(last_sal_incr_dt, LAST_VALUE(last_sal_incr_dt IGNORE NULLS) OVER (PARTITION BY assignment_id order by assignment_id, person_id, effective_start_date) ) last_sal_incr_dt FROM ( select tab.assignment_id ,tab.person_id ,tab.pay_proposal_id ,tab.proposal_reason_code ,'PROPOSAL_REASON' proposal_reason_type ,tab.effective_start_date ,tab.effective_end_date ,tab.business_group_id ,tab.salary ,NVL(tab.pay_annualization_factor, NVL(tpt.number_per_fiscal_year, 1)) annualization_factor ,tab.salary_change_ind ,tab.currency_code ,tab.data_last_update_date ,CASE WHEN tab.salary_change_ind = 1 THEN tab.effective_start_date END last_sal_incr_dt from (select /*+ USE_HASH(asg pro pet piv ppb) */ pro.assignment_id ,asg.person_id ,pro.pay_proposal_id ,pro.proposal_reason proposal_reason_code ,pro.change_date effective_start_date ,NVL(LEAD(pro.change_date, 1) OVER (PARTITION BY pro.assignment_id ORDER BY pro.change_date) - 1 ,to_date('31-DEC-4712','DD-MON-YYYY')) effective_end_date ,asg.business_group_id ,pro.proposed_salary_n salary ,ppb.pay_annualization_factor ,CASE WHEN pro.proposed_salary_n = LAG(pro.proposed_salary_n, 1) OVER (PARTITION BY pro.assignment_id ORDER BY pro.change_date) THEN 0 ELSE 1 END salary_change_ind ,pet.input_currency_code currency_code ,pro.last_update_date data_last_update_date ,asg.payroll_id from per_pay_proposals pro ,per_all_assignments_f asg ,per_pay_bases ppb ,pay_input_values_f piv ,pay_element_types_f pet where pro.approved = 'Y' and asg.assignment_id = pro.assignment_id and pro.change_date between asg.effective_start_date and asg.effective_end_date and asg.pay_basis_id = ppb.pay_basis_id and ppb.input_value_id = piv.input_value_id and pro.change_date between piv.effective_start_date and piv.effective_end_date and piv.element_type_id = pet.element_type_id and pro.change_date between pet.effective_start_date and pet.effective_end_date ) tab ,per_time_period_types tpt ,pay_all_payrolls_f prl where tab.effective_end_date >= TO_DATE('$$INITIAL_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS') and tab.effective_start_date between prl.effective_start_date (+) and prl.effective_end_date (+) and tab.payroll_id = prl.payroll_id (+) and prl.period_type=tpt.period_type (+) ) ORDER BY assignment_id, person_id, effective_start_date
NVL(last_sal_incr_dt, LAST_VALUE(last_sal_incr_dt IGNORE NULLS) OVER (PARTITION BY assignment_id order by assignment_id, person_id, effective_start_date) ) last_sal_incr_dtsince due to the IGNORE NULLS only it is returning an error like missing parenthesis.
So, if i removed and run the query in the toad means then it is fetching values.
So, how i can rectify this error, whether can i edit it in the sql expression in designer directly and after that i can restart the ETL in DAC or else i need to follow other steps. Can you guide me towards the right direction svee for correcting the above issue.
Thanks for your help
I'm not sure what is missing if you takeout that.
I would suggest make a notes, for time being go ahead with changes. Later investigate on this.
can i correct sql expression in designer for missing right parenthesis error for the failed mapping and restart the ETL run in DAC??
Yes. Make sure you got backup of the mapping.
Backup in the sense u meant to export the objects of the mapping as a xml file. Okay once i exported it a xml file then how i can restore it.
Once you have xml, File Import the mapping.
U mean under designer in mappings we have an option of Import Mapping template over there we need to import this xml file inorder to restore it.
I was away from desk :)1 person found this helpful
Log on to Repository Manager->Open repository->double Click on folder->Expand Workflows->Select workflow
Menu Repository->Export Obejcts
Import Objects is for Importing.
The same for Other objects for Mapping,Session etc.
If you export Workflow that will take care of objects. I prefer Repository Manager to these activities.
If helps pls mark correct or helpful :)
Thanks for the info, svee