Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/15/2024 06:32 AM
Hi,
Using Dynamic attributes in endpoint, we are fetching some data from entitlement table based on user's costcenter value.
Usecase:
The below query works for 1 & 2. but it throws an error for 3
Any suggestions how to implement 3?
working query:
select case when ${user.costcenter} is not null then (select ev.CUSTOMPROPERTY19 as id from entitlement_values ev where ev.entitlement_value=${user.costcenter} and ev.customproperty3=${user.customproperty5} limit 1) when ${user.customproperty5} is not null then (select c.customproperty19 from customer c where c.customproperty3 = ${user.customproperty5} limit 1) else '' end as id from users u
Tried the below queries but didnt work:
select case when EXISTS (SELECT entitlement_value FROM entitlement_values WHERE entitlement_value=${user.costcenter}) and ${user.costcenter} is not null then (select ev.CUSTOMPROPERTY19 as id from entitlement_values ev where ev.entitlement_value=${user.costcenter} and ev.customproperty3=${user.customproperty5} limit 1) when ${user.customproperty5} is not null then (select c.customproperty19 from customer c where c.customproperty3 = ${user.customproperty5} limit 1) else '' end as id from users u
Error in logs:
org.codehaus.groovy.grails.web.pages.exceptions.GroovyPagesException: Error processing GroovyPageView: Error executing tag <g:render>: Error executing tag <g:render>: DAError###Configuration error found with attribute : DA_costcenterdata
Best regards
Harish
10/15/2024 06:38 AM
SELECT CASE WHEN ${user.costcenter} IS NOT NULL AND EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter} AND ev.customproperty3 = ${user.customproperty5}) THEN ( SELECT ev.CUSTOMPROPERTY19 AS id FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter} AND ev.customproperty3 = ${user.customproperty5} LIMIT 1 ) WHEN ${user.customproperty5} IS NOT NULL THEN ( SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = ${user.customproperty5} LIMIT 1 ) ELSE '' END AS id FROM users u;
10/15/2024 06:44 AM
@HarishG
Try one of this.
SELECT
COALESCE(
(SELECT ev.CUSTOMPROPERTY19
FROM entitlement_values ev
WHERE ev.entitlement_value = ${user.costcenter}
AND ev.customproperty3 = ${user.customproperty5}
LIMIT 1),
(SELECT c.customproperty19
FROM customer c
WHERE c.customproperty3 = ${user.customproperty5}
LIMIT 1),
''
) AS id
FROM users u
or
SELECT
CASE
WHEN ${user.costcenter} IS NOT NULL
AND EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter} AND ev.customproperty3 = ${user.customproperty5})
THEN (SELECT ev.CUSTOMPROPERTY19 FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter} AND ev.customproperty3 = ${user.customproperty5} LIMIT 1)
WHEN ${user.costcenter} IS NULL
THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = ${user.customproperty5} LIMIT 1)
WHEN ${user.costcenter} IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter})
THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = ${user.customproperty5} LIMIT 1)
ELSE ''
END AS id
FROM users u
10/15/2024 07:13 AM
@rushikeshvartak and @stalluri ,
i tried the suggested queries, getting the below error in logs..
WARNING [http-nio-8080-exec-201] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: SELECT CASE WHEN asdads IS NOT NULL AND EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = asdads AND ev.customproperty3 = 1) THEN (SELECT ev.CUSTOMPROPERTY19 FROM entitlement_values ev WHERE ev.entitlement_value = asdads AND ev.customproperty3 = 1 LIMIT 1) WHEN asdads IS NULL THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = 1 LIMIT 1) WHEN asdads IS NOT NULL AND NOT EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = asdads) THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = 1 LIMIT 1) ELSE '' END AS id FROM users u because: Unknown column 'asdads' in 'field list'
asdads - is the costcenter value of the user. but that entitlement with same name doesnt exist. so it should check the value in the customer table(which is the remaining part of the query). But its failing at this check
10/15/2024 07:22 AM
SELECT
CASE
WHEN ${user.costcenter} IS NOT NULL
AND EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter} AND ev.customproperty3 = ${user.customproperty5})
THEN (SELECT ev.CUSTOMPROPERTY19 FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter} AND ev.customproperty3 = ${user.customproperty5} LIMIT 1)
WHEN ${user.costcenter} IS NULL
THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = ${user.customproperty5} LIMIT 1)
WHEN ${user.costcenter} IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = ${user.costcenter})
THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = ${user.customproperty5} LIMIT 1)
ELSE ''
END AS id
FROM users u
10/15/2024 07:33 AM
still getting the similar error
WARNING [http-nio-8080-exec-185] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: SELECT CASE WHEN asdads IS NOT NULL AND EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = asdads AND ev.customproperty3 = 1) THEN (SELECT ev.CUSTOMPROPERTY19 FROM entitlement_values ev WHERE ev.entitlement_value = asdads AND ev.customproperty3 = 1 LIMIT 1) WHEN asdads IS NULL THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = 1 LIMIT 1) WHEN asdads IS NOT NULL AND NOT EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = asdads) THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = 1 LIMIT 1) ELSE '' END AS id FROM users u because: Unknown column 'asdads' in 'field list'
10/15/2024 08:16 AM
SELECT CASE WHEN u.costcenter IS NOT NULL AND EXISTS (SELECT 1 FROM entitlement_values ev WHERE ev.entitlement_value = u.costcenter AND ev.customproperty3 = u.customproperty5) THEN (SELECT ev.CUSTOMPROPERTY19 FROM entitlement_values ev WHERE ev.entitlement_value = u.costcenter AND ev.customproperty3 = u.customproperty5 LIMIT 1) WHEN u.customproperty5 IS NOT NULL THEN (SELECT c.customproperty19 FROM customer c WHERE c.customproperty3 = u.customproperty5 LIMIT 1) ELSE '' END AS id FROM users u;