I want to use Filter to show items in a list (Database column selection).
I want to use complex query that looks like
Variable ("UID_ParentOrg IN (
select BU =
case
when(
SELECT count(O.UID_ParentOrg)
FROM Person U /* Filter on the ParentOrg of the creator of the user */
JOIN Org O ON U.UID_Org = O.UID_Org
WHERE U.UID_Person = '%useruid%'
and UID_OrgRoot IN (
SELECT O.UID_OrgRoot
FROM Org O /* Filter on the Job Title */
join OrgRoot RO on RO.UID_OrgRoot = O.UID_OrgRoot
where Ident_OrgRoot = 'CCC-JobTitles'
)) < 1
/* If Title is assigned */
THEN
(
SELECT O.UID_Org
FROM Org O /* Filter on the ParentOrg of the creator of the user */
Where
O.Ident_Org in (
select distinct D1.DepartmentNAme
from Person P
join Department D on D.UID_Department=P.UID_Department
join Department D1 on D1.UID_Department=D.UID_PArentDepartment
where
P.UID_Person = '%useruid%'
)
and fullpath like 'CCC-JobTitles%'
)
ELSE
(
SELECT O.UID_ParentOrg
FROM Person U /* Filter on the ParentOrg of the creator of the user */
JOIN Org O ON U.UID_Org = O.UID_Org
WHERE U.UID_Person = '%useruid%'
and UID_OrgRoot IN (
SELECT O.UID_OrgRoot
FROM Org O /* Filter on the Job Title */
join OrgRoot RO on RO.UID_OrgRoot = O.UID_OrgRoot
where Ident_OrgRoot = 'CCC-JobTitles'
)
)
END
)")
When I open the page where selection should be visible There is just loading icon. After some time (few minutes) I get error
SyntaxError: Unexpected token < in JSON at position 2
In case I use simplified version of above SQL
Variable ("UID_ParentOrg IN (
select BU =
case
when( 1=1 )
THEN
(
SELECT BU = 'e39aed0d-c3a0-48a9-998b-1a77af2f85af'
)
ELSE
(
SELECT BU ='e39aed0d-c3a0-48a9-998b-1a77af2f85ae'
)
END
)")
Everything is working as expected.
Are there any limitations on writing query?
Many thanks for suggestions