Error in dataflow transform
I am looking to store a couple values in variables, to avoid clutter, of my MYSQL transform and am running into problems. How can I set variables in a transform and then use later in that transform?
Here is what I have currently and then I call these variables later in the transform...
-- Variables of actions taking place.
SET @finalcheck := max(case when `action` = 'Checked Final' then `date` end)
SET @exin := max(Case when `action` = 'Exhibits In' then `date` end)
SET @exscan := max(case when `action` = 'Scanned Exhibits' then `date` end)
SET @exdone := max(Case when `action` = 'Exhibits Done' then `date` end)
,timestampdiff(hour, @exin,@exdone) as `ExProcessingHours`
0
Comments
-
What I've done before is create a stored procedure in 1 transform (declaring my variables here) and then Call the procedure in the next transform.
Here's an example I keep on hand (You can use the syntax if nothing else, but not the DECLARE statement):
CREATE PROCEDURE testProcedure()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE (i <= (SELECT COUNT(DISTINCT `Column`) FROM testing)-1) DO
INSERT INTO mytable (Column, counter, one, two) VALUES ((SELECT `Column` FROM testing LIMIT i,1),i, 1, 2);
SET i = i + 1;
END WHILE;
END;And then the next transform:
CALL testProcedure();
Hope this helps,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1 -
Thank you for the quick response! I found this code on StackOverflow which has a generic procedure I want to use. Here:
Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
AS
Begin
Declare @WorkMin int = 0 -- Initialize counter
Declare @Reverse bit -- Flag to hold if direction is reverse
Declare @StartHour int = 9 -- Start of business hours (can be supplied as an argument if needed)
Declare @EndHour int = 17 -- End of business hours (can be supplied as an argument if needed)
Declare @Holidays Table (HDate DateTime) -- Table variable to hold holidayes
-- If dates are in reverse order, switch them and set flag
If @StartDate>@EndDate
Begin
Declare @TempDate DateTime=@StartDate
Set @StartDate=@EndDate
Set @EndDate=@TempDate
Set @Reverse=1
End
Else Set @Reverse = 0
-- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)
If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)) -- If Start time is less than start hour, set it to start hour
If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day
If @StartDate>@EndDate Return 0
-- If Start and End is on same day
If DateDiff(Day,@StartDate,@EndDate) <= 0
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If day is between sunday and saturday
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- If day is not a holiday
If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
Else Return 0
Else Return 0
End
Else Begin
Declare @Partial int=1 -- Set partial day flag
While DateDiff(Day,@StartDate,@EndDate) > 0 -- While start and end days are different
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If this is a weekday
Begin
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- If this is not a holiday
Begin
If @Partial=1 -- If this is the first iteration, calculate partial time
Begin
Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate))
Set @Partial=0
End
Else Begin -- If this is a full day, add full minutes
Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60
Set @StartDate = DATEADD(DD,1,@StartDate)
End
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If last day is a weekday
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- And it is not a holiday
If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
End
If @Reverse=1 Set @WorkMin=-@WorkMin
Return @WorkMin
EndSo assuming I put it in a previous transform as you suggest. When I call would I update the variables in the produce within the parenthetical like this?
CALL GetWorkingMin(max(case when `action` = 'Checked Final' then `date` end), max(Case when `action` = 'Exhibits Done' then `date` end))
Or would I set them first, then call like this:
SET @StartDate := max(case when `action` = 'Checked Final' then `date` end)
SET @EndDate := max(Case when `action` = 'Exhibits Done' then `date` end)
CALL GetWorkingMin()0 -
You're going to need to calculate the start/end dates ahead of time. The simple case when in your examples won't work when used just in the procedure call. You would need something like this:
DECLARE @StartDate DateTime = (SELECT MAX(`Date`) FROM table)
And I would even put it in the procedure itself, but that's a personal coding choice.
Best of luck!
1 -
Awesome thanks so much!
0 -
So I settled on this code in a transform but I am getting an error
Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
Begin
Declare @WorkMin int = 0 -- Initialize counter
Declare @Reverse bit -- Flag to hold if direction is reverse
Declare @StartHour int = 8 -- Start of business hours (can be supplied as an argument if needed)
Declare @EndHour int = 21 -- End of business hours (can be supplied as an argument if needed)
-- If dates are in reverse order, switch them and set flag
If @StartDate>@EndDate
Begin
Declare @TempDate DateTime=@StartDate
Set @StartDate=@EndDate
Set @EndDate=@TempDate
Set @Reverse=1
End
Else Set @Reverse = 0
If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)) -- If Start time is less than start hour, set it to start hour
If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day
If @StartDate>@EndDate Return 0
-- If Start and End is on same day
If DateDiff(Day,@StartDate,@EndDate) <= 0
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If day is between sunday and saturday
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- If day is not a holiday
If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
Else Return 0
Else Return 0
End
Else Begin
Declare @Partial int=1 -- Set partial day flag
While DateDiff(Day,@StartDate,@EndDate) > 0 -- While start and end days are different
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If this is a weekday
Begin
If @Partial=1 -- If this is the first iteration, calculate partial time
Begin
Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate))
Set @Partial=0
End
Else Begin -- If this is a full day, add full minutes
Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60
Set @StartDate = DATEADD(DD,1,@StartDate)
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If last day is a weekday
If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
End
If @Reverse=1 Set @WorkMin=-@WorkMin
Return @WorkMin
EndHere is the error I am getting:
The database reported a syntax error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int Begin ' at line 1
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive