Skip to main content

Hi, 

i am asked to create a quick report by converting from Excel, i have couple of questions.

  1.  4th column is Project didn’t work as expected, i want to fetch all data into one cell with a delimeter.
  2. I used APIs in 5th, 6th, 7th and 8th columns, which i am not sure, if this is a correct way of using APIs
  3. There are too many sub-queries and few joins

This query works under 3 second, I am NOT EXPERT but newbie (this is my first very long and complicated query, i wrote it all by myself by searching on internet (some part i took from my prev. questions here)).

can you help me to correct this query and make Project column work as expected?

SELECT DISTINCT
CPA.INTERNAL_DISPLAY_NAME as FULL_NAME,
COMPANY_ID as COMPANY,
COMPANY_ORG_API.Get_Org_Name(COMPANY_ID,ORG_CODE) as "Dept.",

--PROJECTS ARE POPULATED, DISTINCT Didn't work.
(SELECT DISTINCT LISTAGG(PAR1.PROJECT_ID, '; ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY PAR1.PROJECT_ID) FROM Project_access_result2 PAR1 WHERE PAR1.EMPLOYEE_ID = CPA.EMP_NO) as "PROJECT",

COMPANY_POSITION_API.Get_Position_Title(company_id,POS_CODE) as Position,
TRBRD_PERSONEL_BANKA_API.Get_Iban(company_id,emp_no,1,1) as IBAN,
Emp_Employed_Time_API.Get_Common_Emp_Start_Date(COMPANY_ID, EMP_NO) as "Emp. Start",
Emp_Employed_Time_API.Get_Common_Emp_End_Date(COMPANY_ID, EMP_NO, SYSDATE) as "Emp. End",
TRUNC(MONTHS_BETWEEN(sysdate, PER.DATE_OF_BIRTH) / 12,0) as AGE,
'Employment Duration will be calculated based on EXCEL FORMULA =((YEAR(M5)-YEAR(K5))*12+MONTH(M5)-MONTH(K5))/12' as "Duration of Employment",
PER.DATE_OF_BIRTH as Birth,
PER.INSURANCE_ID as "INS. ID",
PER.GENDER as Gender,
'Military Service status field will be added' as MSS,
PER.MARITAL_STATUS as "Mar. Status",
PER.BLOOD_TYPE as Blood,

(SELECT education_level_name
FROM (
SELECT education_level_name
FROM pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "EDU LEVEL",

(SELECT EDU_INSTITUTION_NAME
FROM (
SELECT EDU_INSTITUTION_NAME
FROM pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "University",

(SELECT education_field_name
FROM (
SELECT education_field_name
FROM pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "EDU FIELD",

(SELECT END_YEAR
FROM (
SELECT END_YEAR
FROM pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "GRAD. DATE",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'Şahsi' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Personal E-Mail",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'İş' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Corp. E-Mail",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'MOBILE' and DESCRIPTION = 'Şahsi' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Mobile",

(select RELATIVE_NAME from PERS_RELATED where PERSON_ID = EMP_NO AND ROWNUM = 1) as "Emergency 1st Person Name",

(select RELATIVE_PHONE from PERS_RELATED where PERSON_ID = EMP_NO AND RELATIVE_NAME = (select RELATIVE_NAME from PERS_RELATED where PERSON_ID = EMP_NO AND ROWNUM = 1) AND ROWNUM = 1 ) as "Emergency 1st Person Phone",

(select RELATIVE_NAME from (SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* FROM PERS_RELATED PR WHERE PERSON_ID = EMP_NO) where RN = 2) as "Emergency 2nd Person Name",

(select RELATIVE_PHONE from (SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* FROM PERS_RELATED PR WHERE PERSON_ID = EMP_NO
AND RELATIVE_NAME = (select RELATIVE_NAME
from (
SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.*
FROM PERS_RELATED PR
WHERE PERSON_ID = EMP_NO)
where RN = 2))
) as "Emergency 2nd Person Phone"

FROM COMPANY_PERSON_ALL CPA

--LEFT OUTER JOIN Project_access_result2 PAR
--ON CPA.EMP_NO = PAR.EMPLOYEE_ID

LEFT OUTER JOIN PERS PER
ON CPA.EMP_NO = PER.PERSON_ID

--WHERE CPA.EMP_NO = ''
ORDER BY CPA.INTERNAL_DISPLAY_NAME
--GROUP BY CPA.FNAME, CPA.LNAME

Need your help, Thanks.

kinds.

 

hi,

you can try this query, With this query, you can only pass unique PROJECT_ID values to the LISTAGG function, but in your query, repeated values are included in the LISTAGG function. Therefore, it works more efficiently with the query I sent.

(SELECT LISTAGG(PROJECT_ID, '; ') WITHIN GROUP (ORDER BY PROJECT_ID)
FROM (
    SELECT DISTINCT par1.PROJECT_ID
    FROM Project_access_result2 par1
    WHERE par1.EMPLOYEE_ID = cpa.emp_no
)) as "PROJECT",


Hi @KEVSER ,

Thank you for helping, it seems working, i didn’t noticed anything wrong so far.

Do you have any suggestions or corrections for other items i listed? i appreciated your effort.

Kinds. 


I created Quick report based on this query and gave permission to users via “Manage Grants” but user gets error, even though this user is allowed to access every data/window/page which are subject to this query. I mean he can access every data using/accessing their own page

This is what he gets;
 

Insufficient Permissions

 

i didn’t publish the report because when i publish i see all users gets permission on “Manage Grants” page.


I created Quick report based on this query and gave permission to users via “Manage Grants” but user gets error, even though this user is allowed to access every data/window/page which are subject to this query. I mean he can access every data using/accessing their own page

This is what he gets;
 

Insufficient Permissions

 

i didn’t publish the report because when i publish i see all users gets permission on “Manage Grants” page.

this problem is solved; Quick report permission issues | IFS Community


Reply