uf_in_clause_from_array()

Share this topic:



Link to this posting

Postby Ursego » 17 Apr 2013, 14:21

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

Code: Select all
string ls_countries[] = {"Canada", "USA", "Australia"}
ls_in_clause = gn_util.uf_in_clause_from_array("country", ls_countries[])

ls_in_clause contains the string "country IN ('Canada', 'USA', 'Australia')".

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, remove that functionality from the function.

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 (or use a similar function which, probably, exists in the framework you are using).

Here is the function's coode:

Code: Select all
/**********************************************************************************************************************
Acc:   public
-----------------------------------------------------------------------------------------------------------------------
Dscr:   Dynamically builds IN clause based on the passed field name and array of values.
      See here: http://forum.powerbuilder.us/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 - enclose values with single quotation marks: "... IN ('123', '456')";
                           false - numeric data, don't enclose values with single quotation marks: "... IN (123, 456)"
-----------------------------------------------------------------------------------------------------------------------
Ret:   string
**********************************************************************************************************************/
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 uf_empty(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
   uf_replace_all(ref as_arr[i], "'", "''") // replace ' with '' to prevent damage to dynamic SQL the IN clause will be used in
   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
/**********************************************************************************************************************
Acc:   public
-----------------------------------------------------------------------------------------------------------------------
Dscr:   Dynamically builds IN clause based on the passed field name and array of STRING.
      Example of use: http://forum.powerbuilder.us/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
**********************************************************************************************************************/
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
/**********************************************************************************************************************
Acc:   public
-----------------------------------------------------------------------------------------------------------------------
Dscr:   Dynamically builds IN clause based on the passed field name and array of LONG.
      Example of use: http://forum.powerbuilder.us/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
**********************************************************************************************************************/
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
User avatar
Ursego
Site Admin
 
Posts: 112
Joined: 19 Feb 2013, 20:33

Return to Tips and Tricks

Who is online

Users browsing this forum: No registered users and 1 guest


uf_in_clause_from_array()

Share this topic:


If you think that this site is not too bad, please LIKE it in Facebook. Thanks!





free counters

eXTReMe Tracker