Oracle SQL - Generate rows based on quantity column

Question

We use pooled positions that have a max headcount assigned and I need to build a report that creates a line for each head, including the details of the incumbent if there is one or a NULL line where there is a vacancy.

Like this:

Position_Title	Headcount	Incumbent
Analyst	3	Employee1
Analyst	3	Employee2
Analyst	3

I can join the Person/Assignment tables with the Position table to generate a separate line where there is an incumbent but the part i'm struggling with, is generating a line where there is a vacancy.

I spotted another post on here that suggested using connect by but I can't get it to work.

It seems to work on its own like this:

SELECT

HAP.NAME POSITION_TITLE,
HAP.POSITION_CODE,
HAP.ACTIVE_STATUS,
HAP.POSITION_TYPE,
HAP.FTE,
LEVEL row_num

FROM

HR_ALL_POSITIONS_F_VL HAP


CONNECT BY LEVEL <= HAP.FTE
AND PRIOR HAP.POSITION_ID = HAP.POSITION_ID
AND PRIOR sys_guid() IS NOT NULL

But I'm not sure how to use it with the rest of my query (I've tried using the WHERE clause before and after the CONNECT BY but it times out either way)

SELECT 
HAP.NAME POSITION_TITLE,
HAP.POSITION_CODE,
PGF.NAME GRADE_NAME,
PGF.GRADE_CODE,
HAP.ACTIVE_STATUS,
HAP.POSITION_TYPE,
HAP.HEADCOUNT,
PAAM.ASSIGNMENT_NUMBER,
LEVEL row_num

FROM
HR_ALL_POSITIONS_F_VL HAP, 
PER_GRADES_F_VL PGF,
PER_ALL_ASSIGNMENTS_M PAAM

WHERE 

    HAP.ENTRY_GRADE_ID = PGF.GRADE_ID
AND PAAM.POSITION_ID(+) = HAP.POSITION_ID
AND      TRUNC(Sysdate) between HAP.effective_start_date AND HAP.effective_end_date
AND      TRUNC(Sysdate) between PGF.effective_start_date AND PGF.effective_end_date
AND      PAAM.effective_start_date(+) <= TRUNC(Sysdate)
AND      PAAM.effective_end_date(+) >= TRUNC(Sysdate)

CONNECT BY LEVEL <= HAP.HEADCOUNT
AND PRIOR HAP.POSITION_ID = HAP.POSITION_ID
AND PRIOR sys_guid() IS NOT NULL

score 0 · Accepted Answer · answered Jan 26 '21 at 15:25

You can use multiset to generate rows as per the headcount column as follows:

SELECT 
HAP.NAME POSITION_TITLE,
HAP.POSITION_CODE,
PGF.NAME GRADE_NAME,
PGF.GRADE_CODE,
HAP.ACTIVE_STATUS,
HAP.POSITION_TYPE,
HAP.HEADCOUNT,
PAAM.ASSIGNMENT_NUMBER,
Lvls.Column_value row_num

FROM
HR_ALL_POSITIONS_F_VL HAP, 
PER_GRADES_F_VL PGF,
PER_ALL_ASSIGNMENTS_M PAAM,
table(cast(multiset(select level from dual connect by  level <= hap.headcount) as sys.OdciNumberList)) lvls
WHERE 

    HAP.ENTRY_GRADE_ID = PGF.GRADE_ID
AND PAAM.POSITION_ID(+) = HAP.POSITION_ID
AND      TRUNC(Sysdate) between HAP.effective_start_date AND HAP.effective_end_date
AND      TRUNC(Sysdate) between PGF.effective_start_date AND PGF.effective_end_date
AND      PAAM.effective_start_date(+) <= TRUNC(Sysdate)
AND      PAAM.effective_end_date(+) >= TRUNC(Sysdate);

Note: add the condition as per your requirement and always use standard ANSI joins.

Hi Popeye,Thanks for your answer (I have rewritten it using ANSI Joins). For the position i'm testing it with, the headcount is 16 and there are 12 assignments. It's creating 16 rows for each assignment instead of 1 per assignment plus 4 blanks for the vacancies. Do I need to group it? — Slowalesce, Jan 27 '21 at 18:06
I figured it out, the multiset function gave me what I needed for 1 part of the query. Thanks — Slowalesce, Jan 29 '21 at 12:13

Oracle SQL - Generate rows based on quantity column

1 Answers1