Wands (Oracle) 5.9 : 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, 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 requirements.

The report is now ready to be loaded in to Oracle.