Question about subselect DQL query

ThomasAC

I'm trying to write a dql query to return a list of dmi_workitem that includes the name of the associated activity.  I thought that this would work, but I get a syntax error:

Here's the query:

select wi.r_object_id, wi.r_workflow_id, wi.r_creation_date, wi.r_act_def_id, (select act.object_name from dm_activity act where act.r_object_id = wi.r_act_def_id) from dmi_workitem wi

Here's the error I get

Error occured during query execution :[DM_QUERY_E_SYNTAX]error: "A Parser Error (syntax error) has occurred in the vicinity of: select wi.r_object_id, wi.r_workflow_id, wi.r_creation_date, wi.r_act_def_id, (select"

Incidently, I tested the subquery with a fixed r_object_id and it wokred;

select act.object_name from dm_activity act where act.r_object_id = '4cde75d1800002c4'

Likwise, I tested the outer select without the subselect and it worked fine. 
Any idea what's wrong with the syntax, or does DQL just not allow subselecting a column like that?
Is there a better approach to get to the activity name?
Thanks.

JorgKrause

Hi,

you can't have the subselect in the attribute list

Try

select wi.r_object_id, wi.r_workflow_id, wi.r_creation_date, wi.r_act_def_id,act.object_namefrom dm_activity act,dmi_workitem wi where act.r_object_id = wi.r_act_def_id

Regards

Jørg