SEO

#1 (**permalink**) 02-28-2008, 08:31 AM

Is teher anyway to replace a where clause like :
I have a serie of string values in an array ["01", "02", "58", "61"...]

is there anyway to not to build the SELECT clause with a loop :
WHERE dpt_code = '01' OR dpt_code = '02' OR dpt_code = '58' OR dpt_code
= '61'...
but rather use something like WHERE dpt_code IN [ a set.. of values]

thanks

joss

#2 (**permalink**) 02-28-2008, 08:31 AM

Josselin wrote:
> Is teher anyway to replace a where clause like :
> I have a serie of string values in an array ["01", "02", "58", "61"...]
>
> is there anyway to not to build the SELECT clause with a loop :
> WHERE dpt_code = '01' OR dpt_code = '02' OR dpt_code = '58' OR dpt_code
> = '61'...
> but rather use something like WHERE dpt_code IN [ a set.. of values]

Yes, but you cannot avoid the loop (*). Just change a little bit the way
you are building the SELECT clause. Instead of

clause = "SELECT ... WHERE"
foreach array_element BEGIN
clause = clause + " dpt_code = '" + array_element + "' OR"
END
clause = clause - 3 characters
clause = clause + " ORDER BY ..."

change the *'d lines

* clause = "SELECT ... WHERE dpt_code in ["
foreach array_element BEGIN
* clause = clause + array_element + ", "
END
* clause = clause - 2 characters
* clause = clause + "] ORDER BY ..."

(*) there may be languages that do the foreach loop internally, with a
single instruction.

--
I (almost) never check the dodgeit address.
If you *really* need to mail me, use the address in the Reply-To
header with a message in *plain* *text* *without* *attachments*.

#3 (**permalink**) 02-28-2008, 08:31 AM

On 2006-11-07 21:17:03 +0100, Pedro Graca <hexkid@dodgeit.com> said:

> Josselin wrote:
>> Is teher anyway to replace a where clause like :
>> I have a serie of string values in an array ["01", "02", "58", "61"...]
>>
>> is there anyway to not to build the SELECT clause with a loop :
>> WHERE dpt_code = '01' OR dpt_code = '02' OR dpt_code = '58' OR dpt_code
>> = '61'...
>> but rather use something like WHERE dpt_code IN [ a set.. of values]
>
> Yes, but you cannot avoid the loop (*). Just change a little bit the way
> you are building the SELECT clause. Instead of
>
> clause = "SELECT ... WHERE"
> foreach array_element BEGIN
> clause = clause + " dpt_code = '" + array_element + "' OR"
> END
> clause = clause - 3 characters
> clause = clause + " ORDER BY ..."
>
> change the *'d lines
>
> * clause = "SELECT ... WHERE dpt_code in ["
> foreach array_element BEGIN
> * clause = clause + array_element + ", "
> END
> * clause = clause - 2 characters
> * clause = clause + "] ORDER BY ..."
>
> (*) there may be languages that do the foreach loop internally, with a
> single instruction.

thanks Pedro, indeed using Ruby I can do it with internal loop

#(read array... split limits + insert current one)...
_LIMITS = @city.department.dpt_limits.split(",").insert(0,@c ity.zip_codel[0,2])
# read all record using Rails ActiveRecord , loop on each one 'd' and test
@dpt_ids = Department.find_where(:all) {|d| d.code === _LIMITS }

joss

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

where value in array