41773_es-ES

liliconcom

Usando variables de enlace SQL en JSL

Estoy reuniendo grandes conjuntos de datos.  Con el fin de mantener el tiempo de retorno de las consultas manejable y permanecer dentro del límite de 1000 elementos de la lista de Oracles, estoy ejecutando varias consultas que son idénticas excepto por un conjunto de claves.  PL / SQL admite 'variables de enlace' que permiten que la consulta se compile y luego los elementos que cambian se envían en tiempo de ejecución.  Esto acelera enormemente la respuesta.  ¿Hay una manera de hacer esto dentro de JSL? Una consulta de ejemplo podría ser:

 

Seleccionar

    Fecha, hora, temperatura, número de serie

Desde

   Tabla de datos

Dónde

    SerialNum IN ('1234', '2345' .....) pero varios miles de estos)

 

Gracias

Rayo

Re: Usando variables de enlace SQL en JSL

Puede hacerlo todo en una declaración SQL, utilizando UNION entre 1000 elementos en las listas.  Esta función lo hará:

//------------------------------------------------------------------------------
/*
Function Name: get_sql_in_list

Description:    Build an IN list from the list provided.  If there are more than 1000
    items in the list, separate them into 1000-element chunks connected
    via UNION to avoid the 1000 limit in Oracle.
    
    If the elements are of type String, single quotes will be replaced with 
    two single quotes, and single quotes will be put around each item.

Sample calls:
// List of Case IDs
case_id_list = {111, 222, 333, 444, 555};
preamble = "(SELECT ci1.case_id FROM jnjdss.case_info ci1 WHERE ci1.case_id IN (";

x = get_sql_in_list(case_id_list, preamble);

Returns: "((SELECT ci1.case_id FROM jnjdss.case_info ci1 WHERE ci1.case_id IN (111,222,333,444,555)))"


// List of product names
product_list = {"aaaaaaa", "bbbbbbbb", "cccccccc", "dddddddd"};
preamble = "(SELECT rc1.alert_name FROM brmsupport.rts_calendar rc1 WHERE rc1.alert_name IN (";

z = get_sql_in_list(product_list, preamble);

Returns: "((SELECT rc1.alert_name FROM brmsupport.rts_calendar rc1 WHERE rc1.alert_name IN ('aaaaaaa','bbbbbbbb','cccccccc','dddddddd')))"

Arguments: 
_item_list List of items to create an IN list from
_preamble SQL string to preface the IN list with

Version Date        Author      Details of revision
--------------------------------------------------------------------------------
1.0  05-May-2016 Peter Mroz First Draft
*/
get_sql_in_list = function({_item_list, _preamble},
 {Default Local},

 pname = "get_sql_in_list";

 nr = nitems(_item_list);
 if (nr < 0,
  throw("Empty list");
 );

 _sql_in_list = _preamble;
 
 one_type = type(_item_list[1]);
 if (one_type == "String",
  single_quotes = 1;

// for strings replace single quotes with two single quotes
  for (i = 1, i <= nr, i++,
   one_item = _item_list[i];
   if (contains(one_item, "'"),
    _item_list[i] = substitute(one_item, "'", "''");
   )
  );
  ,
  single_quotes = 0;
 );

// This is the Oracle limit for IN lists
 max_items = 1000;
 keep_going = 1;
 istart = 1;
 iend = min(max_items, nr);

 while(keep_going,
  if (single_quotes,
// Convert a portion of the list to an IN list with each element surrounded by single quotes
   if (nr > 1,
    _sql_in_list = _sql_in_list || "'" || concat items(_item_list[istart::iend], "','") || "'";
    ,
    _sql_in_list = _sql_in_list || "'" || _item_list[istart::iend] || "'";
   )
   ,
// Convert a portion of the list to a string
   one_segment = char(_item_list[istart::iend]);
// Strip off the leading and trailing curly braces
   if (nr > 1,
    _sql_in_list = _sql_in_list || substr(one_segment, 2, length(one_segment) - 2);
    ,
    _sql_in_list = _sql_in_list || one_segment;
   );
  );
  istart = istart + max_items;
  iend   = min(nr, (iend + max_items));
  if (istart <= nr,
  // then
   _sql_in_list = _sql_in_list || ")) UNION " || _preamble;
   ,
  // else we're done
   keep_going = 0;
  );
 );

// Wrap it up
 _sql_in_list = "(" || _sql_in_list || ")))";
); // end get_sql_in_list