Using Left() or Substring() IN-DB Formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi everyone,
i want to get the first 50 letters of my "TEXT" column to avoid long text.
Here i want to use an IN-DB Formula Tool.
I tried left("TEXT",50) aswell as Substring("TEXT",0,50). Both of those ideas gave an error.
The formula does work with a normal formula Tool, but i have to find a solution with the IN-DB version for future plans.
Does anyone have another idea?
We are working with Oracle Databases.
Sincerely
Gabriel
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @grandepaci,
In SQL Substring is indexed from 1 so maybe try Substring("TEXT",1,50) or Substring('TEXT',0,50). What does the error say?
SQL SUBSTRING: Extract a Substring from a String (sqltutorial.org)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Don't you need to write it out in SQL form, rather than the standard syntax we use in workflows? I.e. front it with SELECT and define the table:
SELECT LEFT (column, 50) FROM table
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@irawatt
i tried that aswell and it was the same error.
Error: Formula In-DB (35): DataWrapOCI: Unable to prepare the query: "WITH "Tool22_c105" AS (SELECT...
this is the same error i get when typing in wrong querys
@DataNath
i understand the idea behind referencing it directly from the table. Unfortunately, i got the same error
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@grandepaci are you able to show the full error and perhaps a screenshot of your In-DB formula tool configuration? Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Try streaming your data out using a Data Stream Out tool to the workflow, pass the data into a regular formula tool with your Left and Substring functions then stream the data back after the data is transformed and cleansed using Data Stream In if you need to pass it back to your in-db database call.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi There,
I would have done it this way:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@DSzivropulosz I think he already tried that
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
functions in formula db must be used in the db's native sql.
SUBSTR would have worked.
