Using SQL Bind Variables in JSL

vt_sailor

I'm pulling together large data sets.  In order to keep the return time from the queries manageable and stay within Oracles in-list limitation of 1000 items I'm running multiple queries which are identical except for one set of keys.  PL/SQL supports 'bind variables' which allow the query to be compiled and then the items that change submitted at run time.  This greatly speeds up the response.  Is there a way to do this within JSL?  An example query might be:

 

Select

    Date, Time, Temperature,SerialNum

From

   Data_Table

Where

    SerialNum IN ('1234','2345'.....) but several thousand of these)

 

Thank you

Ray

pmroz

You can do it all in one SQL statement, using UNION between 1000-element in lists.  This function will do it:

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