Fixing Duplicate Bind Variables
In Reports Wand v4, it is possible to have multiple parameters in the SQL with the same name. This is also possible in v5 and v6, but it requires a change to the SQL.
First, import the report definition. Normally all report definitions would be imported, but this guide only shows one for simplicity.
Click the upgrade button to run the upgrade process.
As you can see, the upgrade has failed, as the EFFECTIVE_DATE bind variable appears more than once in the SQL.
SELECT aou.name organization
,COUNT(assignment_id) Employees
FROM per_people_f ppf
,per_assignments_f paf
,per_person_type_usages_f ptu
,per_person_types ppt
,hr_all_organization_units aou
WHERE ppf.person_id = paf.person_id
AND ppf.person_id = ptu.person_id
AND ppt.person_type_id = ptu.person_type_id
AND aou.organization_id = paf.organization_id
AND aou.name = NVL('[ORGANIZATION]' ,aou.name)
AND '[EFFECTIVE_DATE]' BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND '[EFFECTIVE_DATE]' BETWEEN paf.effective_start_date AND paf.effective_end_date
AND '[EFFECTIVE_DATE]' BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND ppf.current_employee_flag = 'Y'
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y'
AND ppt.system_person_type IN ('EMP','EMP_APL')
GROUP BY aou.name
ORDER BY aou.name
Looking at the code above, the EFFECTIVE_DATE bind variable ‘[EFFECTIVE_DATE]’ is repeated three times in the SQL. There is a second bind variable, ORGANIZATION_ID which appears once in the SQL.
The SQL is modified using the WITH clause. This is placed at the beginning of the SQL statement, and will contain all the parameters for the SQL statement.The structure is as follows:
WITH SOME_NAME AS (SELECT :A_PARAMETER AS A_PARAMETER, :B_PARAMETER AS B_PARAMETER FROM DUAL)
Where there can be any number of parameters in the WITH SELECT.
For the example above, the WITH clause will look like this:
WITH Parameter AS (SELECT :EFFECTIVE_DATE AS EFFECTIVE_DATE, :ORGANIZATION AS ORGANIZATION FROM DUAL)
Note that we use Parameter as the default name for this block.
This is then placed at the beginning of the SQL statement like this:
WITH Parameter AS
(SELECT :EFFECTIVE_DATE AS EFFECTIVE_DATE, :ORGANIZATION AS ORGANIZATION FROM DUAL)
SELECT aou.name organization
,COUNT(assignment_id) Employees
FROM per_people_f ppf
,per_assignments_f paf
,per_person_type_usages_f ptu
,per_person_types ppt
,hr_all_organization_units aou
WHERE ppf.person_id = paf.person_id
AND ppf.person_id = ptu.person_id
AND ppt.person_type_id = ptu.person_type_id
AND aou.organization_id = paf.organization_id
AND aou.name = NVL('[ORGANIZATION]' ,aou.name)
AND '[EFFECTIVE_DATE]' BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND '[EFFECTIVE_DATE]' BETWEEN paf.effective_start_date AND paf.effective_end_date
AND '[EFFECTIVE_DATE]' BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND ppf.current_employee_flag = 'Y'
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y'
AND ppt.system_person_type IN ('EMP','EMP_APL')
GROUP BY aou.name
ORDER BY aou.name
The next step is to add the WITH to the main FROM clause. This is done by add “, Parameter” to the FROM clause like this:
WITH Parameter AS
(SELECT :EFFECTIVE_DATE AS EFFECTIVE_DATE, :ORGANIZATION AS ORGANIZATION FROM DUAL)
SELECT aou.name organization
,COUNT(assignment_id) Employees
FROM per_people_f ppf
,per_assignments_f paf
,per_person_type_usages_f ptu
,per_person_types ppt
,hr_all_organization_units aou
,Parameter
WHERE ppf.person_id = paf.person_id
AND ppf.person_id = ptu.person_id
AND ppt.person_type_id = ptu.person_type_id
AND aou.organization_id = paf.organization_id
AND aou.name = NVL('[ORGANIZATION]' ,aou.name)
AND '[EFFECTIVE_DATE]' BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND '[EFFECTIVE_DATE]' BETWEEN paf.effective_start_date AND paf.effective_end_date
AND '[EFFECTIVE_DATE]' BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND ppf.current_employee_flag = 'Y'
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y'
AND ppt.system_person_type IN ('EMP','EMP_APL')
GROUP BY aou.name
ORDER BY aou.name
Finally, the v4 bind variables are replaced with the new bind variables from the WITH clause:
WITH Parameter AS
(SELECT :EFFECTIVE_DATE AS EFFECTIVE_DATE, :ORGANIZATION AS ORGANIZATION FROM DUAL)
SELECT aou.name organization
,COUNT(assignment_id) Employees
FROM per_people_f ppf
,per_assignments_f paf
,per_person_type_usages_f ptu
,per_person_types ppt
,hr_all_organization_units aou
,Parameter
WHERE ppf.person_id = paf.person_id
AND ppf.person_id = ptu.person_id
AND ppt.person_type_id = ptu.person_type_id
AND aou.organization_id = paf.organization_id
AND aou.name = NVL(Parameter.ORGANIZATION ,aou.name)
AND Parameter.EFFECTIVE_DATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND Parameter.EFFECTIVE_DATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND Parameter.EFFECTIVE_DATE BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND ppf.current_employee_flag = 'Y'
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y'
AND ppt.system_person_type IN ('EMP','EMP_APL')
GROUP BY aou.name
ORDER BY aou.name
Replace the SQL in the template with this new SQL, and then rerun the Upgrade process. The report should be marked in Orange as it already meets the v5 and v6 requirements.
The report is now ready to be loaded in to Oracle.