Hi,

i want to add Person Education into Role Assignment tab in Project module;
 

 

I have the sql query to get the latest education info of the personel;

SELECT PERSON_ID, EDUCATION_LEVEL_NAME, EDUCATION_FIELD_NAME,START_YEAR, END_YEAR FROM PERS_EDUCATION_PROFILE
WHERE EXTRACT(Year from END_YEAR) != 9999
    AND (PERSON_ID, END_YEAR) IN (
    SELECT PERSON_ID, MAX(END_YEAR)
    FROM PERS_EDUCATION_PROFILE
    WHERE EXTRACT(Year from END_YEAR) != 9999
    GROUP BY PERSON_ID)

i couldn’t figure out about how to connect SQL query with assigned role 

 

Which one i should choose;
 

 

 

Thanks.

I followed the IFS DOCS (Add/Edit Read Only Attributes (ifs.com)) and modified SQL Query like;
 

SELECT EDUCATION_LEVEL_NAME FROM PERS_EDUCATION_PROFILE WHERE EXTRACT(Year from END_YEAR) != 9999 AND (PERSON_ID, END_YEAR) IN (SELECT PERSON_ID, MAX(END_YEAR) FROM PERS_EDUCATION_PROFILE WHERE EXTRACT(Year from END_YEAR) != 9999 GROUP BY PERSON_ID) AND PERSON_ID=:PERSON_ID

but i got;

 

Field Type : Read Only

Implementation Type: Select 

@Noriro2 If you restrict the results from the sub query using the person_id (without doing it in the main query), will it work?

WHERE  extract(YEAR FROM end_year) != 9999 AND person_id = :person_id

Changed Query

SELECT education_level_name
FROM   pers_education_profile
WHERE  extract(YEAR FROM end_year) != 9999
AND    (person_id, end_year) IN (SELECT person_id,
                                        MAX(end_year)
                                 FROM   pers_education_profile
                                 WHERE  extract(YEAR FROM end_year) != 9999 AND person_id = :person_id
                                 GROUP  BY person_id)

WHERE  extract(YEAR FROM end_year) != 9999 AND person_id = :person_id

SELECT education_level_name
FROM   pers_education_profile
WHERE  extract(YEAR FROM end_year) != 9999
AND    (person_id, end_year) IN (SELECT person_id,
                                        MAX(end_year)
                                 FROM   pers_education_profile
                                 WHERE  extract(YEAR FROM end_year) != 9999 AND person_id = :person_id
                                 GROUP  BY person_id)

@Buddhika Kurera thank you for reply, i tried many combinations, they all work on “Oracle SQL Developer” App but when i try to implement on IFS i got this error.

I tried your query (which was one of my combinations, i tried in case i missed something) and no hope i still get;
 

@Noriro2 Actually I wanted to remove the GROUP BY also but it seems like I have copy pasted the code with GROUP BY. When the sub query is modified like this, BROUP BY is not required. It seems like some issue happens when the SQL query is parsed from the IFS Applications side.

This is not tested so not 100% sure if this works, just a suggestion :)

SELECT education_level_name
FROM   pers_education_profile
WHERE  extract(YEAR FROM end_year) != 9999
AND    (person_id, end_year) IN (SELECT person_id,
                                        MAX(end_year)
                                 FROM   pers_education_profile
                                 WHERE  extract(YEAR FROM end_year) != 9999 AND person_id = :person_id)

SELECT education_level_name
FROM   pers_education_profile
WHERE  extract(YEAR FROM end_year) != 9999
AND    (person_id, end_year) IN (SELECT person_id,
                                        MAX(end_year)
                                 FROM   pers_education_profile
                                 WHERE  extract(YEAR FROM end_year) != 9999 AND person_id = :person_id)

@Buddhika Kurera if group by is deleted, IFS doesn’t produce the “Not all bind variables have a corresponding argument” error, but query doesn’t work, if i run the query on “Oracle Sql Developer” i get “

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:

“

so “group by” is needed but IFS doesn't accept it, I'm stuck in this predicament. i am not expert on sql couldn’t make it run without “group by”.

SELECT education_level_name
FROM   pers_education_profile
WHERE  extract(YEAR FROM end_year) != 9999
AND    (person_id, end_year) IN (SELECT person_id,
                                        MAX(end_year)
                                 FROM   pers_education_profile
                                 WHERE  extract(YEAR FROM end_year) != 9999 AND person_id = :person_id)

@Buddhika Kurera I changed the query with the help of AI, it seems work now, i will do my test if anything go wrong i will write here or maybe open new topic, i will accept your post as best answer because you mentioned “group by” may cause issues, thank you for your help, here is the ne query;
 

WITH RankedEducation AS (SELECT PERSON_ID, EDUCATION_LEVEL_NAME, EDUCATION_FIELD_NAME, START_YEAR, END_YEAR, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY END_YEAR DESC) AS rn FROM pers_education_profile WHERE extract(YEAR FROM end_year) != 9999 ) SELECT EDUCATION_LEVEL_NAME FROM RankedEducation WHERE rn = 1 AND person_id = :person_id;

if you want to suggest modifications on query, please do it :)

Thanks.

@Noriro2 Thanks for the update, it is nice to see that the issue is sorted. We need to avoid using GROUP BY with query in custom objects.

What you got from AI works when there are more than 1 qualification with same end_year max value. It is good but complex so I simplified the query, check below.

If the person has more than one education qualifications with same date value in end_year column, then the highest education level is flittered.

SELECT education_level_name
FROM   (
    SELECT education_level_name
    FROM   pers_education_profile
    WHERE  person_id = :person_id
    AND    extract(YEAR FROM end_year) != 9999
    ORDER BY end_year DESC, education_level_no DESC
)
WHERE  ROWNUM = 1

I hope this works even though I didnt test it in the db.

SELECT education_level_name
FROM   (
    SELECT education_level_name
    FROM   pers_education_profile
    WHERE  person_id = :person_id
    AND    extract(YEAR FROM end_year) != 9999
    ORDER BY end_year DESC, education_level_no DESC
)
WHERE  ROWNUM = 1

@Buddhika Kurera Thank you for your help, i replaced my query with yours, it is working as expected.

 Hi, @Buddhika Kurera 

I'm having problems with users accessing data, IFSAPP user has no problm can see data but other users can not, they see the fields.

what is wrong or what am i missing, can you help? please.

Can anyone help me with this issue?