Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Advanced Query Command

Answered
Request Demo

Hi There - 

Trying to use the Advanced query command under the Tabular Transformation Bizapp.  There is a note at the bottom of the query section that says the SQL commands of INSERT UPDATE and CREATE are not supported (see screen shot below).  Is there a list of SQL commands that are supported?  I want to use the CONCAT SQL function but received an error that "no such function" exists. 

Thanks.

0

Comments

1 comment

Date Votes
  • Jeff Hickey

    Hi Bradley, the Advanced Query command allows you to write SQLite SELECT commands against files as input. Right above the Query field is the Tables section. Here is where you specify the files as input which creates temporary tables from them.

    This command supports SELECT statements and complementary JOIN statements, but not statements such as INSERT, UPDATE, or CREATE. To insert rows, use the Stack Files command. To update rows, use the Find and Replace command.

    Other things to keep in mind when build the query:

    • If column names or identifiers contain spaces or special characters, use brackets. For example, [Column A], [Column B].
    • To format data with two decimal spaces, use the syntax SELECT PRINTF('%.2f',(SUM(DATA))) AS EBITDA FROM HFMDat.
    • To select the first instance of a duplicate, such as if two records have the same ID, use the syntax select * from group by ID having MIN(ID) ORDER BY ID.
    • To concatenate multiple strings together, use the || operator such as string1 || string2 [ || string_n ].

    Below is a sample screenshot with one file (Converted File variable) as input with the temporary table name of "wdata". The query is selecting all id's who name is like the value of the download file variable from the wdata temporary table.

    0

Please sign in to leave a comment.