Limits to the SQL Statement when creating External Dictionaries

[Peter Lee]

Limits to the SQL Statement when creating External Dictionaries

Does RC not provide the ability to concatenate multiple db columns into a single dictionary field?  When trying a query like:  SELECT Col1 + Col2 AS NewCol1 FROM Tbl1 we get an error("Unknown internal error") after clicking to save/create the new  dictionary.  This is on version 2008.3

[Leslie Tierstein]

This is not supported in external dictionaries, as the dictionary corresponds to a table that needs to be updateable by newScale, and a computed column as shown in your example is not updateable.  (The error description, I admit, could be better.)

[Peter Lee]

Thanks, I will then have to go with setting up an internal free form dictionary then use active form rules to populate the field with an onload database data retrieval.

[Kaz Karl]

What if you created a buffer table in your external database, that is populated by your field-merging query, and then invoke that from Newscale? The temporary table would be updatable by newscale (although, presumably, you wouldn't actually have newscale update it), so then it'd accept that connection.

For example, I think you could do something like that by creating a View in MS SQL Server. Likewise, in other database brands you could create a Trigger that would update the buffer table each time data in the real tables are changed.