uf_in_clause_from_array()


Link to this posting

Postby Ursego » 17 Apr 2013, 14:21

The function dynamically builds an IN clause (ready to be used in a WHERE clause) based on the passed field name and array of values.

Example of use:

Code: Select all
ls_statuses_frag = gn_util.uf_in_clause_from_array("status", {"A", "N", "R"}) // returns "status IN ('A', 'N', 'R')"
ls_depts_frag = gn_util.uf_in_clause_from_array("dept", {7, 11, 16}) // returns "dept IN (7, 11, 16)"
ls_sql = "SELECT " + as_list_of_fields_to_print + " FROM emp WHERE " + ls_statuses_frag + " AND " + ls_depts_frag
lds_emp = gn_util.uf_ds_from_sql(ls_sql, true /* ab_retrieve */) // uf_ds_from_sql(): http://code.intfast.ca/viewtopic.php?f=4&t=12

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).

Here is the function's code:

Code: Select all
/**********************************************************************************************************************
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.

            Example:
            string ls_countries[] = {"Canada", "USA", "Australia"}
            ls_in_clause = gn_util.uf_in_clause_from_array("country", ls_countries[]) // returns "country IN ('Canada', 'USA', 'Australia')"
            
            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:

Code: Select all
/**********************************************************************************************************************
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):

Code: Select all
/**********************************************************************************************************************
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

ls_in_clause = uf_in_clause_from_array(as_field, ls_arr, false /* ab_data_is_textual */)

return ls_in_clause

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.
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker