Alteryx SQL "Text Truncated"

[bertal34]

Hello,

Came across something I have never seen before.  On input data tool, I am querying oracle database and when the sql reaches a certain length, I get the error "Text Truncated".  This causes the query to error and no results returned.  See highlight in attached screenshot.

Any help is appreciated!

Jesse

[CharlieS]

Hi @bertal34 

Sometimes we need to have some lengthy WHERE clauses which can bump into string limitations. A common way to get around this is to group your WHERE arguments into groups so you query say, 10 at a time instead of all 30 at once. I've attached an example workflow to show how you can use a Record ID, Formula, and Summarize tool to make the replacement groups (of any size you'd like). Add a Dynamic Input tool after this set of tools to use those different argument groupings and it'll all come out just like you need.

Check it out and let me know if you have any questions. 

[bertal34]

@CharlieS 

Thank you for the quick reply and example!!  Very clever solution and I happened to be using dynamic input already.  One question about the proper setting on dynamic input for Modify SQL Query.  Is it more appropriate to use "Replace a specific string" or "SQL: Update WHERE Clause"?

[CharlieS]

Happy to help!

When you're building the string like this, I find it's best to "Replace a specific string" (and make sure that string is unique in the configuration).

[nagols5445]

If this was a SQL query if you break up the where clause you will get a different result.  I am running into this issue also.  My result contains 26 columns.  The where clause contains 13 conditions and there is a group by.  The total length of the SQL is about 3500 characters.

[apathetichell]

are you using dynamic input in-db and passing the query in as a value there? I find that string column size tends to be more of an issue than SQL query size.

[nagols5445]

I am.  Increase the size of the string?  Different data type?