Endpoint Dynamic attribute query

[HarishG]

Hi,

Using Dynamic attributes in endpoint, we are fetching some data from entitlement table based on user's costcenter value.

Usecase:

1. if user has costcenter and if there is an entitlement with same costcenter name then it should fetch data from entitlement table
2. if user do not have costcenter then it shoud fetch data from org table
3. ifuser has costcenter and similar costcenter name is not available in entitlements then it should fetch data from org table

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

[rushikeshvartak]

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;

[stalluri]

@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

[HarishG]

@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

[stalluri]

@HarishG 

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

[HarishG]

@stalluri ,

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'

[rushikeshvartak]

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;