SQL query syntax for Campaign

[fuko]

Hi Team,

I'm trying to create a manual Certifiers query on a Certification Campaign but I seem to have troubles with the SQL query syntax.

It is simple query to select users that are Entitlement owners Rank 1

(u.username) in (select distinct uf.username from Users uf, Entitlement_owners eo where eo.rank='1' and uf.userkey=eo.userkey)

Is there any solution for this?
Also, is there any document on how to construct queries in configuration UI of all Campaign types?

[rushikeshvartak]

Check logs for actual error and unallowed table

[fuko · Accepted solution]

Based on the log there is two types of queries that are SQL and HQL. Each language applied to different query section.

SQL applied on

• Users query in Advance Campaign Configuration only expose Users table
• Accounts query in Advance Campaign Configuration only expose Accounts table
• Entitlements query in Advance Campaign Configuration only expose Entitlements table

HQL & subquery can be applied on

• Certifier Manual query
• Account Entitlements1 in Advance Campaign Configuration. Normal HQL is also usable like ae1.entitlement_valuekey.entitlement_value like '%Administrator%'
•  Consult/Reassign User query in Advance Campaign Configuration cannot use subquery
•  All primary keys of every table is convert from .***key to . id . For example: user.userkey -> user.id