Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!

I am trying to run this code but it does not work due to the second "IN" on the query, that's where I think the problem is, I can't get it solved because of the first element of the first array not been matched with "only" the first element of the second array, it should be matching like, 12345 = 03 (the first element of the first array equal the first element of the second array) instead, the "IN" is checking for all the values of the array month and it is giving me the wrong results. What I am looking for is 12345=03 and the same for all the elements on the arrays. Can I build this using Perl?
Can the "WHERE" be formatted with the arrays values to be like: 

where $reg_num_values[0]=$month_values[0]

[download]


Or can I to this for both arrays using the first join(","...

Here is some code to show what I am trying to do: 

my @reg_num = qw/12345 98564 33234 112345 87564 2345678 938566 1234486
+ 223456 123488/;
my @month = qw/03 01 01 10 05 11 03 05 02 09/;


my $dbh = DBI->connect("DBI:ODBC:$myserver",$u, $p,);

my $reg_num_values = 
  join("," 
       ,map { $dbh->quote($_) } @reg_num 
      );

my $month_values = 
  join("," 
       ,map { $dbh->quote($_) } @month
      );


my $statement = "SELECT 
                        name, last_name, reg_num, month, year
                               
                 FROM   members
                 WHERE  reg_num IN ($reg_num_values)
                 AND    month   IN ($month_values)
                 AND    year='2009'

my $sth = $dbh->prepare($statement);
   $sth->execute();

[download]


Thanks for all the help!

Replies are listed 'Best First'.
Re: Query using more than one array!
by ELISHEVA (Prior) on May 13, 2009 at 06:11 UTC

    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

[reply]
[d/l]
[select]
Re: Query using more than one array!
by CountZero (Bishop) on May 13, 2009 at 06:13 UTC
    Really, this is an SQL-question.

    I think you want something (in SQL) like: 

    SELECT name, last_name, reg_num, month, year
FROM   members
WHERE  (reg_num = '12345' AND month = '03') OR
       (reg_num = '98564' AND month = '01') OR
       (reg_num = '33234' AND month = '01') OR
...
AND    year='2009'

    [download]
    SQL has no concept of arrays and cannot synchronize between the values of your 'IN' clauses.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

[reply]
[d/l]
[select]
      The problem is how to build this SQL query using the arrays?
[reply]

        Use a loop to build the query string (and the parameters array passed to execute).

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]

Back to Seekers of Perl Wisdom