Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Write Data In-DB passing field names as string

tehc0w
5 - Atom

Hello,

 

I'm using the Write Data In-DB tool with "Append Existing" creation method and it's generating the query:

 

INSERT INTO `target_table` WITH `Tool3_da47` AS (Select test_db.source_table.* From test_db.source_table) SELECT "string_col", "integer_col", "timestamp_col", "boolean_col" FROM `Tool3_da47` AS `a`

 

Which errors out with

 

Error: Write Data In-DB (17): Error running PreSQL on "NoTable": [Simba][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: Target table 'test_db.source_table' is incompatible with SELECT / PARTITION expressions. Expression ''integer_col'' (type: STRING) is not compatible with column 'integer_col' (type: INT)

 

It looks like the query is attempting to pass the column name as a string to the integer field.  For tables that only have string fiends, every row is populated with "string_col" rather than the correct value.

 

How do I modify the query so the fields don't have double quotes around "string_col", "integer_col", etc?

 

Thanks!

2 REPLIES 2
fadib
Alteryx Alumni (Retired)

Hi @tehc0w

 

To change the Table/Field Name SQL style, in the Connect In-DB tool, click on the arrow then Manage Connection. Select the Data Source you're using and change the Table/Field Name SQL style to None. Do the same for both Read and Write. 

 

1.png

 

2.png

 

Fadi Bassil,
Manager - Sales Engineering, RoEMEA
tehc0w
5 - Atom

Hi @FadiB

 

That works.  Thanks!

 

Quick observation: when I go back into the Manage In-DB Connections screen, the SQL style still shows Quoted even though the query generated now uses ` instead of "

Labels