Обсуждение: How to build a query
I have to build a query at run-time which will contain a WHERE clause. There is a field in a table that has to be tested against several values but I do not know in advance how many values exist. For example,
SELECT fld1, fld2, fld3 FROM tblTable
WHERE fld1 = value[1] OR fld1 = value[2] OR fld1 = value[3]
value[] is an array that contains all of the possible values that fld1 may take but I do not know how many items such array has. A value of -1 in the array position indicates that it will not be taken into account. So, I need to traverse the whole array and include those values different from -1 in the WHERE clause. I can do a FOR...NEXT and concatenate the conditions getting only those values different than -1 but I wonder if this is a good approach; the query might grow depending on the number of valid values in the array. Please advice if there is a better way to build my query.
Respectfully,
Jorge Maldonado
On 01/20/2012 06:20 PM, JORGE MALDONADO wrote: > I have to build a query at run-time which will contain a WHERE clause. > There is a field in a table that has to be tested against several > values but I do not know in advance how many values exist. For example, > > SELECT fld1, fld2, fld3 FROM tblTable > WHERE fld1 = value[1] OR fld1 = value[2] OR fld1 = value[3] > > value[] is an array that contains all of the possible values that fld1 > may take but I do not know how many items such array has. A value of > -1 in the array position indicates that it will not be taken into > account. So, I need to traverse the whole array and include those > values different from -1 in the WHERE clause. I can do a FOR...NEXT > and concatenate the conditions getting only those values different > than -1 but I wonder if this is a good approach; the query might grow > depending on the number of valid values in the array. Please advice if > there is a better way to build my query. > > Respectfully, > Jorge Maldonado SELECT columns FROM tables WHERE column1 in (value1, value2, .... value_n); Mickg
On 21 January 2012 01:20, JORGE MALDONADO <jorgemal1960@gmail.com> wrote: > I have to build a query at run-time which will contain a WHERE clause. There > is a field in a table that has to be tested against several values but I do > not know in advance how many values exist. For example, > > SELECT fld1, fld2, fld3 FROM tblTable > WHERE fld1 = value[1] OR fld1 = value[2] OR fld1 = value[3] > > value[] is an array that contains all of the possible values that fld1 may > take but I do not know how many items such array has. A value of -1 in the > array position indicates that it will not be taken into account. So, I need > to traverse the whole array and include those values different from -1 in > the WHERE clause. I can do a FOR...NEXT and concatenate the conditions > getting only those values different than -1 but I wonder if this is a good > approach; the query might grow depending on the number of valid values in > the array. Please advice if there is a better way to build my query. Perhaps not what you want to hear, but if you had your values in a separate table instead, you could do this: SELECT fld1, fld2, fld3 FROM tblTable INNER JOIN values ON tblTable.id = values.tblTable_id WHERE values.value != -1; But I suspect you know that. -- Michael Wood <esiotrot@gmail.com>