Request Roles Query use case mentioned based on customproperty39

[Israr]

Hello Team,

Need help in preparing the query based on the customproperty39 if the customproperty39 is null then it should display all the enterprises roles.

Below sample query i have tried.

The query is working fine if the customproperty39 has some value but it is not working if customproperty39 is null please help in below query it should work if customproperty39 is null.

and rl.customproperty1 = ((select users.customproperty39 from Users users where ((users.id={currentUser} and users.customproperty39='XXXXXX') or (users.id={currentUser} and users.customproperty39='XXXXXX'))) or (select users.customproperty39 from Users users where users.id={currentUser} and users.customproperty39 is null))

Thanks!

Regards,

Israr Ahmed M

[rushikeshvartak]

AND (rl.customproperty1 = (SELECT users.customproperty39 FROM Users users WHERE users.id = {currentUser} AND users.customproperty39 IS NOT NULL) OR (SELECT users.customproperty39 FROM Users users WHERE users.id = {currentUser} AND users.customproperty39 IS NULL) IS NULL)

[Israr]

Hello Rishikesh,

The query is not working fine either if user have customproperty39 is null or customproperty39 has some value all enterprises roles are displaying now which is not correct.

Our requirement is if customproperty39 of users.id = {currentUser} is null then it should display all the enterprises roles which ever r1.customproperty1='' and if if customproperty39 of users.id = {currentUser} has some value then r1.customproperty1='Customproperty39 return value' it should disblay all the enterprises which ever has that values.

Is here possible to use case statement in this query?

Thanks!

Regards,

Israr Ahmed M

[rushikeshvartak]

You can use case condition

AND (
(
SELECT CASE
WHEN users.customproperty39 IS NULL THEN ''
ELSE users.customproperty39
END
FROM Users users
WHERE users.id = {currentUser}
) = rl.customproperty1
OR (
SELECT users.customproperty39
FROM Users users
WHERE users.id = {currentUser} AND users.customproperty39 IS NULL
) IS NULL
)

[Israr]

Hi Rushikesh,

The query is not work still, it is displaying all enterprises roles even though the users.customproperty39='Test' cases then RESULT SHOULD BE the r1.customproperty1='Test' and if users.customproperty39='' then the result should r1.customproerty1='' these cases we have like three please help us on this.

All should work based on users.id = {currentUser} this user selection.

Thanks!

Regards,

Israr Ahmed M

[rushikeshvartak]

can you share logs