About sub query in EncodedQuery

[satoru]

Hi All,
I am trying to use a subquery to retrieve records from a table that belongs to a table that is joined to another table.

The schema and execution results are as follows

1.Table:Affiliation[u_affiliation](Global Scope)

• u_user_name Reference User[sys_user]
• u_active True/False
• u_department String(255)

2.Table: Contractor[x_xxx_contractor](Private Scope)

• u_user_id Reference User[sys_user]
• active True/False

(1) OK
var gr = new GlideRecord("u_affiliation");
gr.addJoinQuery("x_xxx_contractor", "u_user_name", "u_user_id").addCondition("active", "=", true);
gr.query();

(2) Encoded Query ... NG
var gr = new GlideRecord("u_affiliation");
gr.addEncodedQuery("SUBQUERYu_user_name,u_user_id,x_xxx_contractor^active=true^ENDSUBQUERY");
gr.query();

(3) Encoded Query ... OK
var gr = new GlideRecord("sys_user");
gr.addEncodedQuery("SUBQUERYsys_id,u_user_id,x_xxx_contractor^active=true^ENDSUBQUERY");
gr.query();

EncodedQuery is planned to be used in the Reference qual of the reference field to check the operation.
The result of processing (2) is an error.
Error message:.
>>> Subquery term invalid: u_user_name,u_user_id,x_xxx_contractor: no thrown error

What is wrong?
Does SUBQUERY always have to be followed by sys_id?

[Sai Kumar B]

@satoru 

I would recommend you use addJoinQuery() instead of a subquery

References - addJoinQuery method in GlideRecord 

SubQuery usage in Encoded Queries.

[Sai Kumar B]

@satoru 

Thanks for marking my answer as helpful, you can mark it as correct if it resolved your query.

[Hitoshi Ozawa]

Hi Satoru,

Beside requiring sys_id, there's a need to set the join relationship between tables as a filter condition.

For example, have reference field "affiliation" to table u_affiliation. Set filter condition to be sys_id of a record in u_affiliation  table.

gr.addEncodedQuery("SUBQUERYsys_id,u_user_id,x_xxx_contractor^affiliation=" + sys_id + "^ENDSUBQUERY");