Datawindow Retreive With Large In Clause

Our DBMS limits the number of Items in an “IN Clause” to 255. If you attempt to retrieve with an array of a larger size, the retrieve fails. One method of dealing with this constraint is to create a temp table, populate this with your retrieval arguments, and then have your datastore join to the temp table during the retrieve. Another way is to use a function similar to this:

//////////////////////////////////////////////////////////////////////////////
// Function: of_RetrieveInChunks
//
//   Description: Because the datawindow retrieve an array with more than 255
//              elements, this will chop the array size into chunks which are
//              smaller in size.
//
//     Arguments: n_ds           ads            (ref)
//                long           ala_ret_args[]
//                integer        ai_chunk_sz
//              string         as_msg         (ref)
//
//       Returns: integer
//
//////////////////////////////////////////////////////////////////////////////
//   Revision History
//
// Date      By   Task   Description/Comments
// --------  ---  ----  ---------------------------------------
// 10/04/06    fjs         Initial Version
//
//////////////////////////////////////////////////////////////////////////////

boolean        lb_append
integer        li_rc = SUCCESS, li_chunk_indx
long           lla_chunk_args[], lla_empty[], ll_arg_total, ll_indx, ll_rows

//Overhead
if li_rc = SUCCESS then
   lb_append = ads.of_GetAppend()
   ads.of_SetAppend(True)
end if

//Validate arguments
if li_rc = SUCCESS then
   if NOT IsValid(ads) then
      li_rc = FAILURE
      as_msg = "Passed Datastore is not valid"
   end if
end if

if li_rc = SUCCESS then
   if IsNull(ala_ret_args) then
      li_rc = FAILURE
      as_msg = "Data Array must not be null"
   end if
end if

if li_rc = SUCCESS then
   if IsNull(ai_chunk_sz) then
      li_rc = FAILURE
      ls_msg = "Chunk size argument must not be null"
   end if
end if

if li_rc = SUCCESS then
   if ai_chunk_sz < 1 then
      li_rc = FAILURE
      ls_msg = "Chunk size argument must not be a positive number"
   end if
end if

if li_rc = SUCCESS then
   if ai_chunk_sz > 255 then
      li_rc = FAILURE
      as_msg = "Chunk Size argument must be < 255"
   end if
end if

if li_rc = SUCCESS then
   if UpperBound(ala_ret_args) < 1 then
      li_rc = NO_ACTION
   end if
end if

// Can we validate the retrieval args should be only a long array?

// Work begins
if li_rc = SUCCESS then
   ads.Reset()
   ll_arg_total = UpperBound(ala_ret_args)
   ll_indx = 0
   do while ll_indx < ll_arg_total
      li_chunk_indx = 0
      lla_chunk_args = lla_empty
      do while ll_indx < ll_arg_total and li_chunk_indx < ai_chunk_sz
         ll_indx ++
         li_chunk_indx ++
         lla_chunk_args[li_chunk_indx] = ala_ret_args[ll_indx]
      loop
      ll_rows = ads.Retrieve(lla_chunk_args)
      if ll_rows < 0 then
         li_rc = FAILURE
         as_msg = "Error retrieving chunk of data"
      end if
   loop
end if

//Clean up
ads.of_SetAppend(lb_append)

Return li_rc

  of_RetreiveInChunks is a public function which resides on a NVO inherited from the PFC’s n_base.

About these ads