A 'Relation' (tdcms010) can be of type Employee, Supplier, or Customer. Depending on the type, the name is retrieved from the 'Employee' (tccom001) or 'Business Partner' (tccom100) data. (In my case, I need the Search Key, so 'Sold-to BP' (tccom110) or 'Buy-from BP' (tccom120).

We are using the 'Relation' in one of our tables, e.g., zrcom038.reln, and I would like to get the Search Key field of the 'referenced' table added so that users can filter based on it.

I added a Session Extension with a Calculated field that uses a Nested Select. The Nested Select has a Case Statement.

This is the Select code that I believe should work, but I am getting a 'SQLState 42I00: Unexpected token ':1' (error 302)':

case
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.employee)
then ( select tccom001.seak
from tdcms010,
tccom001
where tdcms010.reln = zrcom038.reln
and tccom001.emno = tdcms010.emno)
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.supplier)
then ( select tccom120.seak
from tdcms010,
tccom120
where tdcms010.reln = zrcom038.reln
and tccom120.otbp = tdcms010.otbp)
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.customer)
then ( select tccom110.seak
from tdcms010,
tccom110
where tdcms010.reln = zrcom038.reln
and tccom110.ofbp = tdcms010.ofbp)
end

If I reduce the select and replace any two of the 'then ( select ...)', then it works.

case
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.employee)
then zrcom038.reln
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.supplier)
then zrcom038.reln
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.customer)
then ( select tccom110.seak
from tdcms010,
tccom110
where tdcms010.reln = zrcom038.reln
and tccom110.ofbp = tdcms010.ofbp)
end

or

case
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.employee)
then ( select tccom001.seak
from tdcms010,
tccom001
where tdcms010.reln = zrcom038.reln
and tccom001.emno = tdcms010.emno)
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.supplier)
then zrcom038.reln
when exists ( select tdcms010.*
from tdcms010
where tdcms010.reln = zrcom038.reln
and tdcms010.rtyp = tdcms.rtyp.customer)
then zrcom038.reln
end

Any suggestions on what I might be missing or do the Nested Selects not support more than one 'then ( select ...)'?

The problem seems to be in the LN tools. When trimming the Select statement, e.g., removing all whitespaces and using one letter table aliases, it gets validated correctly and works as expected. There must be some buffer problem that cuts off the Select when it gets too long. I reported this to Infor with an incident.