Summary:

I am trying to produce an analytic that can show the number of "Vacant" positions. The analytic requires two subject areas and I believe I may need an outer join between the two.

Content (required):

At our organisation a Vacant position is;

Active

or;

It has either; never been assigned to a person or if it has, it has no current active assignments.

The analytic I first pulled together uses two subject areas

"Workforce Management - Position Real Time" and "Workforce Management - Worker Assignment Real Time"

The table view highlights the problems I am having; Were a position exists but there is no corresponding Assignment, the columns relating to assignment look blank and at first appear to be null. However, any formulae looking to these columns does not return a value. This makes me think that the are not null but simply nothing is being returned.

The formula looks like this;

When the analysis runs i get the following;

The highlighted column is the one I am referring to. Where there is no corresponding Assignment Data - there is no null value so the formula fails (the blank rows)

After researching further on this site I found reference to a similar issue indicating an outer join is required to bring back the null values. The page included an excel template that can accept the code from the two separate subject areas and create the logical SQL to create the analytic with an outer join.

That produced this analytic;

However, it does not run and I cannot figure out why. I get this error;

This is the SQL it contains

The question seems deceptively simple, however the answer is currently beyond my reach. Can anyone here assist please?

Thanks in anticipation.

Version (include the version you are using, if applicable):

22b

Code Snippet (add any code snippets that support your topic, if applicable):

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';

SELECT position_business_unit saw_0, position_department saw_1, position_job saw_2, position_job_set saw_3, position_location saw_4, position_directorate saw_5, position_service_area saw_6, position_operational_area saw_7, position_hiring_status saw_8, position_active_flag saw_9, position_position_code saw_10, position_budgeted_fte saw_11, position_ft_flag saw_12, position_budgeted_head_count saw_13, position_name saw_14, position_regular_flag saw_15, posotion_type saw_16, position_working_hours saw_17, position_bu_descriptor saw_18, position_job_descriptor saw_19, position_hiring_status_descriptor saw_20, position_active_flag_descriptor saw_21, position_full_time_flag_descriptor saw_22, position_name_descriptor saw_23, position_regular_flag_descriptor saw_24, position_type_descriptor saw_25, position_#_of_positions saw_26, position_fte saw_27, position_headcount saw_28, workforce_position_code saw_30, workforce_position_name saw_31, workforce_valid_grades saw_32, workforce_worker_assignment saw_33, workforce_person_type saw_34, workforce_assignment_number saw_35, workforce_assignment_status saw_36, workforce_assignment_type saw_37, workforce_employee_name saw_38, workforce_worker_category saw_39, workforce_position_name_descriptor saw_40, workforce_person_type_descriptor saw_41, workforce_assignment_status_descriptor saw_42, workforce_assignment_type_descriptor saw_43, workforce_worker_category_descriptor saw_44, workforce_assigment_count saw_45, workforce_fte saw_46, workforce_headcount saw_47

FROM (

select Positions.position_business_unit position_business_unit, Positions.position_department position_department, Positions.position_job position_job, Positions.position_job_set position_job_set, Positions.position_location position_location, Positions.position_directorate position_directorate, Positions.position_service_area position_service_area, Positions.position_operational_area position_operational_area, Positions.position_hiring_status position_hiring_status, Positions.position_active_flag position_active_flag, Positions.position_position_code position_position_code, Positions.position_budgeted_fte position_budgeted_fte, Positions.position_ft_flag position_ft_flag, Positions.position_budgeted_head_count position_budgeted_head_count, Positions.position_name position_name, Positions.position_regular_flag position_regular_flag, Positions.posotion_type posotion_type, Positions.position_working_hours position_working_hours, Positions.position_bu_descriptor position_bu_descriptor, Positions.position_job_descriptor position_job_descriptor, Positions.position_hiring_status_descriptor position_hiring_status_descriptor, Positions.position_active_flag_descriptor position_active_flag_descriptor, Positions.position_full_time_flag_descriptor position_full_time_flag_descriptor, Positions.position_name_descriptor position_name_descriptor, Positions.position_regular_flag_descriptor position_regular_flag_descriptor, Positions.position_type_descriptor position_type_descriptor, Positions.position_#_of_positions position_#_of_positions, Positions.position_fte position_fte, Positions.position_headcount position_headcount, Worker.workforce_position_code workforce_position_code, Worker.workforce_position_name workforce_position_name, Worker.workforce_valid_grades workforce_valid_grades, Worker.workforce_worker_assignment workforce_worker_assignment, Worker.workforce_person_type workforce_person_type, Worker.workforce_assignment_number workforce_assignment_number, Worker.workforce_assignment_status workforce_assignment_status, Worker.workforce_assignment_type workforce_assignment_type, Worker.workforce_employee_name workforce_employee_name, Worker.workforce_worker_category workforce_worker_category, Worker.workforce_position_name_descriptor workforce_position_name_descriptor, Worker.workforce_person_type_descriptor workforce_person_type_descriptor, Worker.workforce_assignment_status_descriptor workforce_assignment_status_descriptor, Worker.workforce_assignment_type_descriptor workforce_assignment_type_descriptor, Worker.workforce_worker_category_descriptor workforce_worker_category_descriptor, Worker.workforce_assigment_count workforce_assigment_count, Worker.workforce_fte workforce_fte, Worker.workforce_headcount workforce_headcount

from 

(

SELECT

"Workforce Management - Position Real Time"."Business Unit"."Business Unit Name" position_business_unit, "Workforce Management - Position Real Time"."Department"."Department Name" position_department, "Workforce Management - Position Real Time"."Job"."Job Name" position_job, "Workforce Management - Position Real Time"."Job"."Set Name" position_job_set, "Workforce Management - Position Real Time"."Location"."Worker Location Name" position_location, "Workforce Management - Position Real Time"."Organization Hierarchy"."Level 04 Organization Name" position_directorate, "Workforce Management - Position Real Time"."Organization Hierarchy"."Level 05 Organization Name" position_service_area, "Workforce Management - Position Real Time"."Organization Hierarchy"."Level 06 Organization Name" position_operational_area, "Workforce Management - Position Real Time"."Position Details"."Hiring Status Name" position_hiring_status, "Workforce Management - Position Real Time"."Position Details"."Position Active Flag" position_active_flag, "Workforce Management - Position Real Time"."Position Details"."Position Code" position_position_code, "Workforce Management - Position Real Time"."Position Details"."Position FTE" position_budgeted_fte, "Workforce Management - Position Real Time"."Position Details"."Position Full Time Flag" position_ft_flag, "Workforce Management - Position Real Time"."Position Details"."Position Head Count" position_budgeted_head_count, "Workforce Management - Position Real Time"."Position Details"."Position Name" position_name, "Workforce Management - Position Real Time"."Position Details"."Position Regular Flag" position_regular_flag, "Workforce Management - Position Real Time"."Position Details"."Position Type Name" posotion_type, "Workforce Management - Position Real Time"."Position Details"."Working Hours" position_working_hours, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Business Unit"."Business Unit Name") position_bu_descriptor, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Job"."Job Name") position_job_descriptor, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Position Details"."Hiring Status Name") position_hiring_status_descriptor, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Position Details"."Position Active Flag") position_active_flag_descriptor, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Position Details"."Position Full Time Flag") position_full_time_flag_descriptor, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Position Details"."Position Name") position_name_descriptor, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Position Details"."Position Regular Flag") position_regular_flag_descriptor, DESCRIPTOR_IDOF("Workforce Management - Position Real Time"."Position Details"."Position Type Name") position_type_descriptor, "Workforce Management - Position Real Time"."Position"."# Of Positions" position_#_of_positions, "Workforce Management - Position Real Time"."Position"."Position FTE" position_fte, "Workforce Management - Position Real Time"."Position"."Position Headcount" position_headcount

FROM "Workforce Management - Position Real Time"

)Positions

LEFT OUTER JOIN

(

SELECT

"Workforce Management - Worker Assignment Real Time"."Position"."Position Code" workforce_position_code, "Workforce Management - Worker Assignment Real Time"."Position"."Position Name" workforce_position_name, "Workforce Management - Worker Assignment Real Time"."Position"."Valid Grades" workforce_valid_grades, "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."Normal Hours" workforce_worker_assignment, "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."System Person Type" workforce_person_type, "Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Number" workforce_assignment_number, "Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status" workforce_assignment_status, "Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Type" workforce_assignment_type, "Workforce Management - Worker Assignment Real Time"."Worker"."Employee Display Name" workforce_employee_name, "Workforce Management - Worker Assignment Real Time"."Worker"."Worker Category" workforce_worker_category, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Position"."Position Name") workforce_position_name_descriptor, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."System Person Type") workforce_person_type_descriptor, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status") workforce_assignment_status_descriptor, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Type") workforce_assignment_type_descriptor, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Worker Category") workforce_worker_category_descriptor, "Workforce Management - Worker Assignment Real Time"."Worker Assignment"."Assignment Count" workforce_assigment_count, "Workforce Management - Worker Assignment Real Time"."Worker Assignment"."Full-Time Equivalent" workforce_fte, "Workforce Management - Worker Assignment Real Time"."Worker Assignment"."Head Count" workforce_headcount

FROM "Workforce Management - Worker Assignment Real Time"

)Worker

ON Positions.position_position_code = Worker.workforce_position_code

)outer_tab ORDER BY saw_0 DESC;