Toad Team,
I’m wanting to do an if condition activity in which I create an expression to look at the day of the week. Therefore, lets say I want a certain action to run on Monday. I want the If condition activity to show if the day of the week is Monday run that action, if it’s not run the action in the else condition. In SQL for IBM DB2, there’s a dayofweek function that can accomplish this and it produces a numeric day of the week like Sunday being 1 through Saturday being 7. I was hoping there was something similar in TDP. Any ideas? If not, I can probably accomplish this automation task, but it requires me to create multiple automation tasks that run on different days, as opposed to a single possible one that knows which condition to run on which day of the week. Any thoughts are appreciated.
Chad not sure if you are doing this in the visual query builder or even if you are using MySQL but a case statement would work here with the function
SELECT CASE
WHEN DAYOFWEEK(contososalessmallss.DateKey) = 2 THEN ‘Monday’
WHEN DAYOFWEEK(contososalessmallss.DateKey) = 3 THEN ‘Tuesday’
ELSE ‘No Day’
END
AS CALCULATED_COLUMN1
FROM contoso.contososalessmallss contososalessmallss
The contososalessmallss.DateKey field is a datetime field in MySQL stored on Toad Intelligence Central published from SQL Server you can change the THEN part to what ever you want to run in SQL - Update a column etc.
Let me know if this is what you are after
Regards
Peter
I’m sorry if I created confusion. I’m completely fine on the SQL end to get this, its the if condition activity in automation that I’d like to support this to tell if it’s a certain day of the week when running the activity, then run a specific query, else run another specific query. Thanks…Chad
There is an If Condition in Toad Automation where you can query a variable and take action A or action B or even neither. You probably still want to use SQL to get the day of the week and store the result in a Toad variable. Then use that variable in the Condition evaluation step. Be careful if you use the loop when setting a variable value as there are two different variable value setting methods. Set Variable (which creates the variable) and Set Variable Value (which updates the value in the variable). If you use Set Variable again inside of the loop on the same variable name you created outside of the loop, you actully get a second separate variable with the same name with a scope of only inside that loop. To set or change a variables value from outside the loop while you are inside of the loop use Set Variable Value and not Set Variable. What you want to do is totaly possible. I did not see a day of week function in Toad so I would suggest using SQL to set the variable value. Select Variable Type of SQL and then put your code in below.
Greg, that’s awesome! I didn’t know you could use SQL as the variable value. I think I should be able to get this to work then. Thanks a bunch!!
No problem, just make sure your SQL returns only one value. In the loop function, a loop variable is created from your SQL that can hold an entire dataset and then you can set a variable with the LoopVariableName.FieldName or [tag:LoopVariableName].FieldName# for each row that is returned from your SQL query as the loop steps through dataset row by row. Very useful. Have fun.