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:
DROP VIEW FINANCE_USER_TBLS.SR1109_SW_MOU_MASTER_V;
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)
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’