OQL query with Customized Request Form extension - use field value in JOIN

[Giuseppe De Fazio]

Hi,
I want get the organization used in a custom field created with the Customized Request Form extension; to do this I'm tring with this query:

SELECT U FROM TemplateFieldValue AS V
JOIN Organization AS U ON V.field_value=U.id
WHERE V.field_code='Divisione'
AND V.obj_key = 239

(239 is the UserRequest's ID used for my tests)

When I executed this query I got the error below:
[cid:image003.png@01D942CC.022CC3B0]

But if I try another query with field_value filtered in WHERE condition it works...
Please can anyone suggest me a way to fix this problem?

Thanks,
Giuseppe.

Giuseppe De Fazio
[AizoOnPaesi4]

Via San Vincenzo 2 |16121 GENOVA
www.aizoongroup.comhttp://www.aizoongroup.com/ | www.aizoon.ithttp://www.aizoon.it/
AUSTRALIA Sydney NSW
EUROPE Bari Bologna Cuneo Genova Milano Roma Torino ITA | Sheffield UK
USA Cambridge MA | Lewiston ME | New York NY | Troy MI

[Jeffrey Bostoen]

Are you sure the first part is correct? Select from seems to be SQL instead of OQL?

[Pierre Goiffon]

It is, see https://www.itophub.io/wiki/page?id=latest:oql:oql_syntax

[Giuseppe De Fazio]

yes, it works, try with a simple query; you can write in this way if you need to select the second class used in the join.

Giuseppe.

[Giuseppe De Fazio]

Solved! You have to join the link class before, so you can avoid the field class because the link class had the value field. This is the correct OQL query:

SELECT U FROM TemplateFieldValueLnk AS V
JOIN Organization AS U ON V.field_target_key=U.id
  WHERE V.field_code='Divisione' 
   AND V.obj_key = 239

sorry to bother you and thanks for your help.
Giuseppe.

[Pierre Goiffon]

As a reference : https://www.itophub.io/wiki/page?id=extensions:request-templates#reporting