Calculated fields on external sql-based types

#1

Hi,

a customer has an external type that maps to a table in an SQLDataSource.

entity type MyTable mixes external schema name 'schema.tablename' 
{
  id: ~ schema name 'id'
  field1: ~ schema name 'FIELD1'
  field2: ~ schema name 'FIELD2'
  ...
}

How do I create a calc field that maps to the execution of a SQL command over a field? The following approach tries to create field3 as the execution of SUBSTRING command over the database FIELD2. MyType.fetch() fails with an invalid SQL statement error.

entity type MyType mixes external schema name 'schema.tablename' 
{
  id: ~ schema name 'id'
  field1: ~ schema name 'FIELD1'
  field2: ~ schema name 'FIELD2'
  field3: ~ schema name 'SUBSTRING(FIELD2,0,5)'
  ...
}
0 Likes

#2

You shoud define your field3 as calc field

entity type MyType mixes external schema name ‘schema.tablename’
{
id: ~ schema name ‘id’
field1: ~ schema name ‘FIELD1’
field2: ~ schema name ‘FIELD2’
field3: string calc ‘substring(FIELD2,0,5)’

}

In this way the field3 won’t be retrieved from your sql source but just calculated at runtime

0 Likes

#3

Unfortunately I need this to be a reference field to a different external type that is defined in the same database, so it needs to be retrieved from the SQL source.

0 Likes

#4

@santiago.lopez, We don’t support expressions as column names. Other than what @GiuseppeBarbaro recommended, the only think I can think of at this point is for them to create a database view that has a column with that expression.

0 Likes