Skip to main content
Question

Help needed with a SQL statement (Challenging)

  • August 28, 2024
  • 4 replies
  • 158 views

Forum|alt.badge.img+10

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.

 

4 replies

Forum|alt.badge.img+6
  • Sidekick (Customer)
  • 38 replies
  • August 28, 2024

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",


Forum|alt.badge.img+10
  • Author
  • Hero
  • 112 replies
  • August 29, 2024

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. 


Forum|alt.badge.img+10
  • Author
  • Hero
  • 112 replies
  • September 11, 2024

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.


Forum|alt.badge.img+10
  • Author
  • Hero
  • 112 replies
  • September 17, 2024
Noriro2 wrote:

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings