If else condition is not checking properly in Sav to Sav DB connection

[Siva]

Hi Experts, 
we have requirement where we are assigning Manager role to Users who is a manager.
We are storing Manger employeeid in CP4 of user profile and we are triggering the rule when cp42=yes then Manager assign rule wiil get trigger. But if any chance user is not manager in future it should make cp42 as null and removal of manager will be happen.  here cp42 is not updating as null.
we are using below query: -
SELECT
m.username,m.employeeid,
case when m.employeetype like '%permanent%' then 'YES'
else ' '
end as customproperty42
FROM users u
JOIN users m
ON u.customproperty4 = m.employeeid and m.employeetype like '%permanent%'
GROUP BY m.username

Can you please help me in this , where I am missing.

Thanks & Best Regards
Siva Avula

[DixshantValecha]

Thank you for contacting the Saviynt forums.

The query you provided, it seems that you are using a CASE statement to set the value of customproperty42 to 'YES' when the employee type is 'permanent' and to an empty string otherwise. However, you haven't accounted for the case where the employee is no longer a manager and the value of customproperty42 should be set to NULL.

One approach to handle this would be to update the query to set customproperty42 to NULL when the employee is not a manager. Here's an updated query that should work:

query:-

SELECT
m.username, m.employeeid,
CASE
WHEN m.employeetype LIKE '%permanent%' AND u.customproperty4 IS NOT NULL THEN 'YES'
ELSE NULL
END AS customproperty42
FROM users u
JOIN users m ON u.customproperty4 = m.employeeid
GROUP BY m.username, m.employeeid

In this query, we use a CASE statement to set the value of customproperty42 to 'YES' only when both conditions are met - the employee type is 'permanent' and the customproperty4 field is not NULL. If either of these conditions is not met, then the value of customproperty42 will be set to NULL.

Please let me know if further information is needed on this.

[Siva]

Hi @DixshantValecha 

Thank you so much  for the precise info. I have tried with user query , its showing only user who are not managers .

we are assigning manager role , only when u.customproperty4 = m.employeeid and m.employeeid should be permanent employee and we are using cp42=yes just to tirgger the rule to assign the manager role.

can you pls check this once to accomodate both conditions , because in future User will not be a manager , at that we need to remove the manager role as well.

Thanks & best regards
Siva

[Siva]

I am trying with below query

SELECT
m.username,m.employeeid,m.employeetype,
case when u.customproperty4 = m.employeeid then 'YES' else ''
end as customproperty42
FROM users u
JOIN users m
ON m.employeetype like '%permanent%' AND m.employeeid !=0
group by m.username

It is on showing only the users who are not managers and it has to show the users who are managers as well

Could you pls help me her, if i miss anything here

Thanks
Siva 

[DixshantValecha]

It sounds like you want to include both users who are not managers and users who are managers in your query results. However, your current query only includes users who are not managers.

To include users who are managers, you need to modify your query to join to the users table twice: once for managers and once for non-managers. Here's an example of how you could modify your query to achieve this:

query:-

SELECT
u.username,
u.employeeid,
u.employeetype,
CASE
WHEN m.username IS NOT NULL THEN 'YES'
ELSE ''
END AS customproperty42
FROM users u
LEFT JOIN users m -- join to get manager information
ON m.employeeid = u.managerid
WHERE
u.employeetype LIKE '%permanent%' AND
u.employeeid != 0
GROUP BY u.username;

In this modified query, we use a left join to join to the users table again to get information about the manager of each user. We also include a WHERE clause to filter for only permanent employees who have a non-zero employeeid.

Let me know if you have any further questions.

[Siva]

Hi @DixshantValecha 
Thank you so much for the query

We need assign role for Manager , 

SELECT
m.username,m.employeeid,m.employeetype,
CASE
WHEN m.username IS NOT NULL THEN 'YES'
ELSE ''
END AS customproperty42
FROM users u
LEFT JOIN users m
ON m.employeeid = u.customproperty4
WHERE
m.employeetype LIKE '%permanent%' AND
m.employeeid != 0
GROUP BY u.username;

I have taken reference from your query and modified as mentioned above but its not taking the user who is manager,

We need to change the approach  , i have tried with below Query ,but unable to all the records (we need to get both users who is manager and who is also not a manager and cp42=yes and cp42=null)

SELECT distinct
m.username as "Manager",m.employeeid,m.employeetype,
CASE WHEN u.manager = m.userkey THEN 'YES'
ELSE null
END AS customproperty42
FROM users u
LEFT JOIN users m
ON u.customproperty4 = m.employeeid
WHERE
m.employeetype LIKE '%permanent%'

Can you pls provide any inputs here?

Thanks
Siva Avula

[DixshantValecha · Accepted solution]

One approach you can try is to use a subquery to find the manager for each user, and then use a case statement to assign the custom property based on whether the user is a manager or not. Here's an example query that should achieve what you're looking for:

SELECT
  u.username,
  u.employeeid,
  u.employeetype,
  CASE
    WHEN m.username IS NOT NULL THEN 'YES'
    ELSE ''
  END AS customproperty42
FROM
  users u
  LEFT JOIN (
    SELECT
      username,
      employeeid
    FROM
      users
    WHERE
      employeetype LIKE '%Manager%'
  ) m ON u.customproperty4 = m.employeeid
WHERE
  u.employeetype NOT LIKE '%Manager%';

This query first finds all managers using a subquery and joins it to the users table using a left join. Then it uses a case statement to assign the custom property based on whether the user is a manager or not. The WHERE clause at the end of the query filters out any users that are managers, so only non-manager users will be included in the result set.