As you see, string values are automatically ornamented with single quote marks.
Oracle only: If the array contains more than 1000 elements then the function returns a few IN clauses joined by OR to avoid Oracle error "ORA-01795: Maximum number of expressions in a list is 1000". For example, if the passed array contains 2800 elements then uf_in_clause_from_array() returns something like "(last_name IN ('elem1', ..., 'elem1000') OR last_name IN ('elem1001', ..., 'elem2000') OR last_name IN ('elem2001', ..., 'elem2800'))". If your DB is not Oracle, you can remove that functionality from the function (or keep it - it will work fine for any DB).
If you want to avoid duplicated values in the IN clause [like "state IN ('CA', 'NY', 'NY', 'CA')"] then fill the array, sent to this function, using function uf_add_unique_element_to_array().
This function uses uf_replace_all() so add it to your utilities NVO. You can use another similar function which, probably, exists in your framework (like n_cst_string.of_GlobalReplace in PFC).
/********************************************************************************************************************** Dscr: Dynamically builds IN clause (ready to be used in a WHERE clause) based on the passed field name and array of STRING values. To build IN clause from an array of LONG type, use another overload.
More details: http://code.intfast.ca/viewtopic.php?f=4&t=95 *********************************************************************************************************************** Arg: as_field - to be placed just before the IN keyword as_arr - array of STRING values to build IN clause ab_field_is_textual: true - textual data type, use single quotes: "... IN ('123', '456')"; false - numeric data type, don't use single quotes: "... IN (123, 456)" *********************************************************************************************************************** Ret: string *********************************************************************************************************************** Developer: Michael Zuskin - http://linkedin.com/in/zuskin | http://code.intfast.ca/ **********************************************************************************************************************/ char lc_quote boolean lb_OR_used = false int li_upper_bound int i int li_elements_counter = 1 string ls_result // comma delimited list be used in SQL's IN clause
if ab_field_is_textual then lc_quote = "'" end if
li_upper_bound = UpperBound(as_arr) choose case li_upper_bound case 0 return "(1=0)" // produce FALSE in the place where the IN clause should be case 1 return as_field + " = " + lc_quote + as_arr[1] + lc_quote // produce "equals" expression instead of less efficient "IN" end choose
ls_result = as_field + " IN (" for i = 1 to li_upper_bound if IsNull(as_arr[i]) or Trim(as_arr[i]) = '' then continue if li_elements_counter = 1000 /* prevent error "ORA-01795: Maximum number of expressions in a list is 1000" */ then li_elements_counter = 0 ls_result = Left(ls_result, Len(ls_result) - 2) // remove the last comma and space (", ") ls_result += ") OR " + as_field + " IN (" lb_OR_used = true end if // Replace single quote with double quote to prevent damage to dynamic SQL the IN clause will be used in (uf_replace_all: http://code.intfast.ca/viewtopic.php?f=4&t=96): uf_replace_all(ref as_arr[i], "'", "''") ls_result += lc_quote + as_arr[i] + lc_quote + ", " li_elements_counter++ next ls_result = Left(ls_result, Len(ls_result) - 2) // remove the last comma and space (", ") ls_result += ")"
if lb_OR_used then ls_result = "(" + ls_result + ")" // ensure correct work of the IN clause when it is added to another condition with AND end if
return ls_result
Create an overload without the argument ab_data_is_textual for the cases the field is textual. The source:
/********************************************************************************************************************** Dscr: Dynamically builds IN clause (ready to be used in a WHERE clause) based on the passed field name and array of STRING. More details: http://code.intfast.ca/viewtopic.php?f=4&t=95 *********************************************************************************************************************** Arg: as_field - to be placed just before the IN keyword as_arr - array of STRING values to build IN clause *********************************************************************************************************************** Ret: string *********************************************************************************************************************** Developer: Michael Zuskin - http://linkedin.com/in/zuskin | http://code.intfast.ca/ **********************************************************************************************************************/ return uf_in_clause_from_array(as_field, as_arr[], true /* ab_data_is_textual */)
Finally, create one more overload for arrays of the type LONG (it will be useful when creating IN clauses for ID fields):
/********************************************************************************************************************** Dscr: Dynamically builds IN clause based on the passed field name and array of LONG. To build IN clause from an array of STRING type, use another overload. Example of use: http://code.intfast.ca/viewtopic.php?f=4&t=95 *********************************************************************************************************************** Arg: as_field - to be placed just before the IN keyword al_arr - array of LONG values to build IN clause *********************************************************************************************************************** Ret: string *********************************************************************************************************************** Developer: Michael Zuskin - http://linkedin.com/in/zuskin | http://code.intfast.ca/ **********************************************************************************************************************/ int li_upper_bound int i string ls_in_clause // comma delimited list be used in SQL's IN clause string ls_arr[]
li_upper_bound = UpperBound(al_arr) for i = 1 to li_upper_bound ls_arr[i] = String(al_arr[i]) next
Generally speaking, using SQLs in the client side is not a good practice. The client should call a stored procedure which encapsulates (and keeps in the server side) any existing (or potential) complexity. But if you have no choice (for example, supporting an application with all the SQLs kept in PB), then uf_in_clause_from_array() can make your life easier - you will call it instead of creating a loop.