In-DB tool to write SQL
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Developer Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The Connect In-DB tool is your way to paste in that code similar to the Input Data tool!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This ^
Nice @apathetichell!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you. I had no idea. Do you know where I can find these rules?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you very much!
