Anything can be done in Perl :-). The issue here is the SQL.

X IN (val1, val2, ...) only returns true(1) or false(0) so your where clause is merely checking to see if regnum and month exist in the pick list (see IN()). It doesn't compare the position in the list at all. To compare position, you (a) need an SQL function to retrieve the position within a list (b) need to compare the two positions one to another.

Retrieving the order number of an item in a list isn't a standard SQL built-in function so you will have to write your own SQL function. Lets call it LIST_POSITION(...). Then your generated query would look something like this:

# Note: reordered where clause
# to put quick to evaluate conditions first
# the query has a better chance of actually being optimized

my $statement = <<EOF;

SELECT name, last_name, reg_num, month, year
FROM   members
WHERE  year='2009'
       AND LIST_POSITION(month, $month_values)
          = LIST_POSITION(reg_num, $reg_num_values)
EOF

[download]

In MySQL, you can define the custom function using the CREATE FUNCTION. You can also write Perl stored procedures.

Your mileage may vary with other SQL dialects.

Best, beth

In reply to Re: Query using more than one array! by ELISHEVA
in thread Query using more than one array! by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.