Controlling WKF table field length | Community
Skip to main content
A
awijaya
March 3, 2020
Solved

Controlling WKF table field length

  • March 3, 2020
  • 1 reply
  • 12349 views

I have a schema with a string field of length 2100.

In enrichment, an expression field is added which does a substring of that field such as this: Substring(@recommendation, 101, Length(@recommendation)-100)

 

 

This is the error I get:

03/03/2020 12:01:29 PM enrich PostgreSQL error: ERROR: value too long for type character varying(255)

 

Which mean the WKF field is created with length of 255, while the source of that expression field is much longer than that.

 

Is there a way to define the length for the expression field or maybe controlling the WKF field length instead?

 

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Jonathon_wodnicki

Hi,

 

This isn't possible with ordinary ootb query activities.

Alternative options in descending order of maintainability:

  • Use sql or js activity to alter the temp table's structure and populate it with substrings
  • Write substrings to a file then load them into workflow with Data loading (file) activity formatted with long text col
  • Create a permanent temp schema with big string col and in workflow truncate then use Update data activity to write substrings to it, with outgoing transition (replaces enrichment)
  • Create your own temp schema in the workflow in js with undocumented registerSchema(), setSchemaSqlTable(), buildSqlTable(), etc.

 

Thanks,

-Jon

1 reply

Jonathon_wodnicki
Community Advisor
Jonathon_wodnickiCommunity AdvisorAccepted solution
Community Advisor
March 3, 2020

Hi,

 

This isn't possible with ordinary ootb query activities.

Alternative options in descending order of maintainability:

  • Use sql or js activity to alter the temp table's structure and populate it with substrings
  • Write substrings to a file then load them into workflow with Data loading (file) activity formatted with long text col
  • Create a permanent temp schema with big string col and in workflow truncate then use Update data activity to write substrings to it, with outgoing transition (replaces enrichment)
  • Create your own temp schema in the workflow in js with undocumented registerSchema(), setSchemaSqlTable(), buildSqlTable(), etc.

 

Thanks,

-Jon

    A
    awijayaAuthor
    March 6, 2020
    Seems like Edit Schema is just editing the temporary schema (WKF). Just like editing schema source in configuration, but instead it's on the fly. So, an expression field can be added to the WKF to be used further downstream. Since it is just an expression to the temp schema, it doesn't create a database column and no data being written. In my impression, WKF creation of an enrichment should factor in at least the largest column size involved in the formula/expression. This is not ideal, but maybe better than just use default 255. Thanks for the tip on the undocumented stuff. Really neat.
    Terms & ConditionsAccessibility statement

    Loading footer...