In-DB tool to write SQL

Hello,

I am new to Alteryx and I am building a workflow to extract data from a MS SQL Server database and import it into Teradata, where the data needs to go through a process of data manipulation.

For the Teradata import piece, I am using the Connect In-DB tool. The problem is that I cannot find a tool that connects to the Connect In-DB tool and that allows me to paste my Teradata SQL code to perform the data manipulation. I am looking for something similar to the Input or Output data tools where there are sections to write the SQL code but that can be used with the In-DB tools.

Developer Tools

Accepted answers

apathetichell

You cannot use a drop statement in-db. You can use it in presql/postsql in input data/output data. In addition you would not use a create statement In-db. You would start with your select statement.

All comments

alexnajm

The Connect In-DB tool is your way to paste in that code similar to the Input Data tool!

Glopez76

Thank you.

This code runs fine in Teradata but I am getting all sorts of errors when trying to run the code in Alteryx, though. I am wondering if there is some other tool that I can connect to the Connect In_DB tool and that can help me with the troubleshooting process.

For example, I am trying to run the below code in the Connect In_DB tool but it is giving me the attached error message.

/*Creating a working table to perform all the data manipulation and processing in Teradata.*/

DROP TABLE [DB].GL_Alteryx_Work;

/*To create a working table with all the data from the previous table*/
CREATE MULTISET TABLE [DB].GL_Alteryx_Work AS(
SELECT  
CASE 
WHEN Length ("DateTime") = 23 --To account for bad data-DateTime fields containing extra characters                THEN Cast ("DateTime" AS TIMESTAMP(3) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(3)')
WHEN Length ("DateTime") <> 23
THEN Cast(Cast("Date" AS DATE FORMAT 'YYYY-MM-DD') AS TIMESTAMP(3) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(3)')
ELSE NULL
END AS New_DateTime,
--"DateTime" AS New_DateTime,    Cast ("Date" AS DATE FORMAT 'YYYY-MM-DD') AS "Date",
Call_Type_ID,
Call_Type_Desc, 
Call_GUID, 
DNIS,
Digits_Dialed,
Caller_ID,
Cast (Call_Disp AS INT) AS Call_Disp,
Call_Disp_Desc,
Cast (Call_Disp_Flag AS INT) AS Call_Disp_Flag,
Call_Disp_Flag_Desc,
Cast (Call_Duration_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Call_Duration_sec,
--casting the value in seconds to the HH:MI:SS format    Cast (Ring_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Ring_Time_sec,
Cast (Delay_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Delay_Time_sec,
Cast (Time_to_Aband_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Time_to_Aband_sec,
Cast (Hold_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Hold_Time_sec,
Cast (Talk_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Talk_Time_sec,
Cast (Work_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Work_Time_sec,
Cast (Local_Q_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Local_Q_Time_sec,
Cast (Conference_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Conference_Time_sec,
Cast (Network_Time_sec * INTERVAL '0000:01' MINUTE TO SECOND AS INTERVAL HOUR TO SECOND) AS Network_Time_sec,
Agent_Skill_Target_ID,
Person_ID,
Agent_First_Name,
Agent_Last_Name
FROM    ENTPR_INTEL_CIHUB_USER.GL_Alteryx_All)WITH DATA
PRIMARY INDEX comp_index_all (Call_GUID ,New_DateTime, Caller_ID) --Non unique primary index with aliasPARTITION BY Range_N ("Date" --Index partitioning by dateBETWEEN DATE '2020-01-01' 
AND DATE '9999-12-31' EACH INTERVAL '1' DAY,NO RANGE);

2024-04-15_12-30-18.jpg

apathetichell

You cannot use a drop statement in-db. You can use it in presql/postsql in input data/output data. In addition you would not use a create statement In-db. You would start with your select statement.

alexnajm

This ^

Nice @apathetichell!

Glopez76

Thank you. I had no idea. Do you know where I can find these rules?

apathetichell

This has some rules - https://help.alteryx.com/current/en/designer/tools/in-database-overview.html#idm45439309718480 - but tbh - hitting an error and searching in community is the best place to learn.

Glopez76

Thank you very much!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703