Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day
I am trying to build an On Demand app using the script below in template app to bind. My problem is that there are certain field names with spaces such as "Customer ID " in script below.The code shows syntax error on all instances where "Customer ID" is included. Unfortunately I cannot change the field names because these fields are used in other systems which would also need to be changed. Is there any way I can go around this problem? I have tried using "", ``, ' ' but none have worked.
//Creates a WHERE statement which can be used in table loads to restrict the data to a smaller set
SUB ExtendWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART) > 0 THEN //If WHERE clause exists then extend it with further values
LET WHERE_PART = '$(WHERE_PART) AND MATCH($(ColName),$(Values))';
ELSE
LET WHERE_PART = ' WHERE MATCH($(ColName),$(Values))'; //If WHERE clause does not exist then create it
ENDIF
ENDIF
END SUB;
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Here we set the variables ready to hold the names of any fields we want considering for ODAG filtering (selection)
//The ods_ field names bind the fields to those in your Selections Application.
SET Sel_Implementation = ; //resets the variable from any previous reload
SET Sel_Implementation = $(od_Implementation); // follow 'ods_' with the field name as it appears in your UI / final load
SET Sel_Implementation_COLNAME ='Implementation'; // enter the field name as it appears in the source data
SET "Sel_Customer ID" = ;
SET "Sel_Customer ID"= $(od_Customer ID);
SET "Sel_Customer ID_COLNAME" ='Customer ID';
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//This section builds the WHERE_PART variables that are passed to the SUB
SET WHERE_PART = '';
// WHERE_PART to be applied to only TIM loads that contain Suffix
FOR EACH fldname IN 'Sel_Implementation', 'Sel_Customer ID'
LET vallist = $(fldname);
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','$(vallist)');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
I made 2 changes to my original script. Firstly I added the square brackets as you suggested. Secondly I removed the double quotes from the field names on the left hand side of the SET command
My final script has the changes made as highlighted
SUB ExtendWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART) > 0 THEN //If WHERE clause exists then extend it with further values
LET WHERE_PART = '$(WHERE_PART) AND MATCH([$(ColName)],$(Values))';
ELSE
LET WHERE_PART = ' WHERE MATCH([$(ColName)],$(Values))'; //If WHERE clause does not exist then create it
ENDIF
ENDIF
END SUB;
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Here we set the variables ready to hold the names of any fields we want considering for ODAG filtering (selection)
//The ods_ field names bind the fields to those in your Selections Application.
SET Sel_Implementation = ; //resets the variable from any previous reload
SET Sel_Implementation = $(od_Implementation); // follow 'ods_' with the field name as it appears in your UI / final load
SET Sel_Implementation_COLNAME ='Implementation'; // enter the field name as it appears in the source data
SET Sel_CustomerID = ;
SET Sel_CustomerID = $(od_Customer ID);
SET Sel_CustomerID _COLNAME" ='Customer ID';
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//This section builds the WHERE_PART variables that are passed to the SUB
SET WHERE_PART = '';
// WHERE_PART to be applied to only TIM loads that contain Suffix
FOR EACH fldname IN 'Sel_Implementation', 'Sel_CustomerID'
LET vallist = $(fldname);
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','$(vallist)');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);