I was wondering if anyone could help me understand what the below code means and what is the purpose of using it.
DECLARE @INPUT_BatchID string = "9035";
DECLARE @BatchID int? = (int?) int.Parse(@INPUT_BatchID);
@dataset1 =
SELECT col1,col2,col3
FROM Table AS T
@dataset2 =
SELECT col1,col2,col3
FROM Table2(@dataset1, 0, 20, @BatchID)
TVF Code below:
CREATE FUNCTION [Table2](
@baseValues TABLE(ENT_MBR_NM string, [ALS] string,
[NC] string, [DELD] string,
PRNT_MBR_NM string, HIER_LVL int,
GEO_ENT_GENERATION_ID int?, [PATH] string,
RGN_CD string, GLB_GRP string),
@CurrentIteration int = 0,
@MaxIterations int = 20,
@BatchID int? = -1)
RETURNS @result TABLE(ENT_MBR_NM string, [ALS]
string, [NC] string, [DELD]string, PRNT_MBR_NM
string, HIER_LVL int, GEO_ENT_GENERATION_ID int?,
[PATH]string, RGN_CD string, GLB_GRP string) AS
BEGIN
DECLARE @cont1 string = "EMEA";
DECLARE @cont2 string = "EUROPE";
DECLARE @cont3 string = "TOTAL CHINA";
DECLARE @cont4 string = "ASIA PACIFIC";
DECLARE @cont5 string = "UNITED STATES";
DECLARE @cont6 string = "OTHER AMERICAS";
DECLARE @cont7 string = "TOTAL AMERICAS";
DECLARE @cont8 string = "MIDDLE EAST/AFRICA";
DECLARE @cont9 string = "OTHER ASIA PACIFIC";
IF (@CurrentIteration >= @MaxIterations) THEN
@result = SELECT * FROM @baseValues;
ELSE
@nextLevel =
SELECT A.ENT_MBR_NM,
A.[ALS],
A.[NC],
A.[DELD],
A.PRNT_MBR_NM,
T.HIER_LVL + 1 AS HIER_LVL,
A.GEO_ENT_GENERATION_ID,
T.[PATH] + " | " + (A.PRNT_MBR_NM ?? "*") AS [PATH],
A.ENT_MBR_NM.Trim().ToUpper() IN (@cont5, @cont6, @cont2, @cont8, @cont3, @cont9) ? A.ENT_MBR_NM : T.RGN_CD AS RGN_CD,
A.ENT_MBR_NM.Trim().ToUpper() IN (@cont7, @cont1, @cont4) ? A.ENT_MBR_NM : T.GLB_GRP AS GLB_GRP
FROM @baseValues AS T
INNER JOIN
[Wz].[Fc].[DH_FCS_GEO_ENT] AS A
ON T.ENT_MBR_NM == A.PRNT_MBR_NM
WHERE A.PartitionID == @BatchID.Value;
@baseValues =
SELECT *
FROM @baseValues
UNION ALL
SELECT *
FROM @nextLevel;
@result = [Table2](@baseValues, @CurrentIteration + 1, @MaxIterations, @BatchID);
END;
END;
This code takes lot of execution time which I want to reduce but I am not sure what the code part (@dataset1, 0, 20, @BatchID) in the @dataset2 means and the use of this.
Any help will be appreciated.
Thanks in Advance.
Table2
is most likely a table valued function(tvf): a function that, optionally, takes some parameters (like your example) and returns a set of records.
Somewhere the Table2
function is defined in your script or a reference.
The same applies to Table
. It might be a tvf as well.
Note: it has been a while since I last saw a question using u-sql. You know already the service is being retired?