41773_de-DE

liliconcom

Verwenden von SQL-Bindungsvariablen in JSL

Ich ziehe große Datensätze zusammen.  Um die Rückkehrzeit aus den Abfragen überschaubar zu halten und innerhalb der in der Liste von Oracles enthaltenen Beschränkung von 1000 Elementen zu bleiben, werden mehrere Abfragen ausgeführt, die bis auf einen Schlüsselsatz identisch sind.  PL / SQL unterstützt 'Bind-Variablen', die die Kompilierung der Abfrage und die anschließende Übergabe der Elemente zur Laufzeit ermöglichen.  Dies beschleunigt die Reaktion erheblich.  Gibt es eine Möglichkeit, dies in JSL zu tun? Eine Beispielabfrage könnte sein:

 

Wählen

    Datum, Uhrzeit, Temperatur, Seriennummer

Von

   Datentabelle

Woher

    SerialNum IN ('1234', '2345' .....), aber mehrere tausend davon)

 

Vielen Dank

Strahl

Re: Verwenden von SQL-Bindungsvariablen in JSL

Sie können dies alles in einer SQL-Anweisung tun, indem Sie UNION zwischen 1000-Elementen in Listen verwenden.  Diese Funktion wird es tun:

//------------------------------------------------------------------------------
/*
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