Getting Temp Table In-DB without Data Stream In and Security Permissions

mhumpher

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. 

Is there any better way to do this?

Is there any ways to improve the macro?

In-DB Values Query.yxmc

hhaigh1

Hi @mhumpher

That is a nice workaround for an issue users like you run into with the in-db tools! 

The field info tool also outputs Type and Size, you could use that information to create the CAST statements in your select. Something like this: 

Are you running into the character limit with the dynamic input in-db? The formula tool should not be limiting the length of your query to only 8000 characters. 

Depending on your database, you might run into limitations on the database side. E.g. MS SQL server limits these types of queries to 1000 rows (see here for more info)