WhereExpression on joined field not in tableschema

Data Abstract
1

Hello,

When we have a Table Schema like this:
SELECT usr_Primkey, usr_LastName, usr_FirstName, usr_reg_Primkey
FROM User
LEFT JOIN Region on usr_reg_Primkey = reg_Primkey

Is there a possibility to use a WhereExpression on a field from the Region (for example reg_Description) table without it being available in the schema table (but obviously it exists in the database table)?

Thanks you

2

Hello

In general it is not possible to create a DynamicWhere expression referencing field not exposed in the server Schema.
Otherwise client app would be able to “probe” underlying database table for values of fields that are not exposed in Schema and therefore not expected to be accessible from client apps.

Possible solutions in your case are:

  • Expose additional fields in the Schema
  • Introduce Schema Table parameter like
SELECT usr_Primkey, usr_LastName, usr_FirstName, usr_reg_Primkey
FROM User
LEFT JOIN Region on usr_reg_Primkey = reg_Primkey
WHERE
   reg_Description = :reg_Description OR (:reg_Description IS NULL)

Regards

3

Hello Anton,

We indeed used a Schema table parameter like that, but now the client wants to use an IN Clause, so that’s why we wanted to use a WhereExpression.

Regards

4

Hello

Unfortunately in this case you’ll have to expose the field in the Schema