× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



From an off-list reply:

I assume you're building the entire statement. If so, simply modify the
logic so that if no values are returned, replace:

where myfield2 in (val1, val2, val3)

with

where (1 = 0)

So, instead of trying to come up with an empty list, just come up with an
always false clause.

Solves the problem quite elegantly. Probably runs faster too.

I'm not building the entire statement. Not at once, anyway.

The reason I would like specifically to have an expression that means
"collection of values", and have it work for any number of values
(including no values), and any type of values, is that I would like to
have a convenience function that converts a Python collection (list,
tuple, or set) into a string suitable for interpolating into an
existing piece of otherwise static SQL (that is, an SQL "template").

This convenience function is meant for very broad use, not just for
one program or one project or one application. It's more at the level
of standard library function.

Your suggestion could also be turned into a library function, but it
would be more specific and require more parameters, and it would lift
more out of the template and into the function. What do I mean by
that?

Let's take the example I've been using in this thread. The template
I'm envisioning looks like this:

tmp = "select myfield1 from mylib.myfile where myfield2 in ({})"

The curly braces mark the replacement or interpolation. If my
convenience function is called values_string() and my Python
collection is called mylist, then I would build the final SQL
statement like so:

sql = tmp.format(values_string(mylist))

With your suggestion, the template would have to be

tmp = "select myfield1 from mylib.myfile where {}"

The convenience function would have to, at minimum, accept a field
name and the collection of values. If we call this function
field_in_values(), then interpolation becomes

sql = tmp.format(field_in_values('myfield2', mylist))

With such a small example, either approach may seem about equally
easy, but to me your suggestion makes the template less useful
(because we'd be most of the way toward having an application-specific
function that takes lots more parameters and just builds the whole SQL
statement, dispensing with the template altogether) and makes the
convenience function both less convenient and less widely applicable.

John Y.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.