In-DB tool to write SQL

[Glopez76]

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.

[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);

[apathetichell · Accepted solution]

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!