This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
One of the problems that I've faced is getting a small temporary table from my workflow to In-DB so I can use it with my In-DB tools, particularly when there are permission restrictions on creating temp tables. To address this problem, I've created a macro that uses a SQL Values statement to generate a table from the query which I can use down stream. Currently the macro returns all columns as string columns so numeric columns have to be cast after the output. Also, one disadvantage is that the dynamic input query I generate is limited by ~8000 character limit for a v_string, so it can only be a relatively small table.
Thanks! Goes to show I should have tested first. I had thought that since strings were limited to ~8000 characters per the Alteryx reference that I would be limited on the size of the SQL statement I could generate as a string. Is it only the String and WString that have the limitation and the V_String and V_WString do not?
I'm going to tweak the macro a bit more than put up a final version here.
String and WString are fixed length fields, which means that the text contained in the field will be padded to match the field length. If you have one character in a 100 length field, you will end up with 99 spaces. If you set them to a large field size, you are potentially carrying a lot of blank spaces around with your data.
V_String and V_WString are variable length, meaning that the length of the string is not defined by the field size but but by the size of the string contained within the field. For those fields, you can think of the field size as the maximum allowed length of the string.
Variable length strings are limited by the total record size allowed in Alteryx which is 2GB. This translates into 1073741823 characters for V_WString (accommodates Unicode) and 2147483647 for V_String (only Latin-1 characters). If you have a string that is actually 2147483647 characters long, you can't have any other columns in your record (which doesn't happen very often).
Thanks again for sharing the macro with us! Other users run into the same issue so this could be helpful for them as well!
Henriette Haigh Principal Support Engineer -- Knowledge Management Coach Alteryx, Inc.
I've included an updated macro here. A caveat is that it was designed for use with Impala and so there seem to be some conventions with using the Values Clause there, but the concept is hopefully transferable.
Also, I don't know if it is a difference between Impala SQL and T-SQL, but the Values clause isn't limited to 1000 rows (I test on creating two columns with 10,000 rows). In the above link, it seems to relate to using an Insert with a Values clause whereas I'm doing a Select on a Values clause.