SQL CREATE issue

Help Activities
, , ,
1

Hello,

I inherited a SQL process with 350 lines of code with multiple drops, creates, updates, alters, inserts, etc. Though it is a legacy process, it did require a few edits, but the entire process runs flawlessly from my editor.

In order to stay organized and avoid multiple files, I split the various steps into their component statements on individual Excel sheets within one master workbook. Sheet names are descriptive, eg, dropping table XXX is named ‘DrpTblXxx’. Maybe too much detail, but like I said, trying to stay organized.

I am reading the sheet names with ‘Get Workbook Sheets’, then I am performing a For Each on each sheet name and doing the following:

  • Reading “” from the sheet
  • Convert the data table result to a string “str_SqlToRun”
  • Executing Teradata with ExecuteQuery
  • Go to the next tab

I have NOT been successful in getting “Execute Query” (database.activities 1.7.1) to complete my table or view CREATE’s.

My master workbook contains over 20 sheets. However I reduced a test workbook to just 3 sheets. They relate to the final view where I want to select (*) and capture those results.

Here is the SQL on the final three tabs (shown) of my test workbook, with tab names included:

DrpVwMouMstr tab
DROP VIEW FINANCE_USER_TBLS.SR1109_SW_MOU_MASTER_V;

CreVwTblMstr tab
CREATE VIEW FINANCE_USER_TBLS.SR1109_SW_MOU_MASTER_V AS (SELECT SCENARIO,FISCAL_YEAR,REPORT_LINE,NATURAL_ACCOUNT, OPERATING_AREA, SEGMENT, MEASURES, NET_OF_AFFILIATE,ESS_MONTH,LINE_OF_BUSINESS, TOTAL FROM FINANCE_USER_TBLS.SR1109_SW_MOU_MASTER WHERE ESS_MONTH = CAST((ADD_MONTHS(CURRENT_DATE- EXTRACT(DAY FROM CURRENT_DATE)+1,-1)(FORMAT ‘MMMM’)) AS VARCHAR(20)) AND FISCAL_YEAR = 'FY ’ || CAST((ADD_MONTHS(CURRENT_DATE- EXTRACT(DAY FROM CURRENT_DATE)+1,-1)(FORMAT ‘YYYY’)) AS CHAR(4));

Above is contained in one cell (A1)

SelAllTblMstV tab
SELECT * FROM FINANCE_USER_TBLS.SR1109_SW_MOU_MASTER_V;

Through trial and error, I can tell you that when all three tabs are resident, the DROP occurs as expected. I get no error that the CREATE did not take place. The select * will execute if it is in a workbook by itself and the view is created manually in my SQL editor.

I can’t get the CREATE to work, even when I hard code the SQL above manually in one line to the Input->SQL Query property.

I have tried both ‘Text’ and ‘Stored Procedure’ options for ‘Command Type’

Thanks,
Geoff

2

Hi @gpftr

It sounds like you are trying to automate the execution of SQL statements contained in an Excel workbook. You are reading the sheet names, then reading the contents of each sheet, converting the contents to a string, and attempting to execute the SQL statement using the “Execute Query” activity. However, you are having trouble getting the “CREATE” statements to execute successfully.

It’s hard to say exactly what the problem is without more information about the specific error messages or issues you’re encountering. However, here are a few things to consider that might help:

  • Make sure the connection to the Teradata database is set up correctly and that you have the necessary permissions to execute the SQL statements.
  • Check the syntax of the SQL statements to ensure that they are valid and that there are no errors or typos.
  • Try running the SQL statements manually in a SQL editor to see if they execute successfully and return the expected results.
  • If you’re still having trouble, you may want to consider using UiPath’s “Log Message” activity to log the contents of the “str_SqlToRun” variable at various points in the process to help troubleshoot the issue.

It may also be helpful to isolate the problem by testing one step at a time, for example, test the first sheet with only drop statement and see if it works as expected, and then test the second sheet with only create statement and see if that works as expected. This will help you identify where the problem lies.