Error in dataflow transform

user00344

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`

BlueRooster

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.

user00344

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)&gt;1 And DATEPART(dw,@StartDate)&lt;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&lt;@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)&gt;1 And DATEPART(dw,@StartDate)&lt;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)&gt;1 And DATEPART(dw,@StartDate)&lt;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
End

 

So 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()

BlueRooster

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! 

user00344

Awesome thanks so much!

user00344

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)&gt;1 And DATEPART(dw,@StartDate)&lt;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&lt;@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)&gt;1 And DATEPART(dw,@StartDate)&lt;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)&gt;1 And DATEPART(dw,@StartDate)&lt;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
End

Here 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