SELECT DISTINCT tax_id FROM gene2acc WHERE genomic_ac_ver = ?
[download]

SELECT tax_id FROM gene2acc WHERE genomic_ac_ver = ? GROUP BY tax_id
[download]

Hi thanks I tried this way,it still does not enter the while loop to print the statement:print "Entering taxID_format with $t\n";

LOOP:foreach $ref(@ref)
{
 print "in foreach loop with $ref\n";

  $count=0;

  $command1 = qq{select distinct tax_id from gene2acc where genomic_ac
+_ver= '$ref'; }; 
   $sth1=$dbh->prepare($command1);

              $sth1->execute() or die $sth1->errstr;

              my($tax_id);

              $sth1->bind_columns(undef,\$tax_id );

              while($sth1->fetch())

              {     

                $t=$tax_id;

                print "Entering taxID_format with $t\n";


                #&taxID_format($t);

               

              }
[download]

please help me

DBI->trace() looks like a good place to get some more information about what's going on.

I was puzzled by this:

  $count = 0 ;

  ....

  while($array = $sth1->fetchrow_array()) {
    $count++ ;
                
    if ($sth1->rows == 0) {
      print "No records matched for $ref\nn" ;
    }
    elsif ($count == 1) {
      $t = $array ;
      print "Entering taxID_format with $t\n" ;
      #&taxID_format($t) ;
    }
  }
[download]

That said, unless the $sth->rows returns 0 (which is not what the documentation suggests), then the problem is elsewhere and some earlier operation is not doing what y'all expected.

$sth->fetchrow_array () returns what it indicates: a list not a scalar

If you use bind_columns () (A GOOD CHOICE!), the call should be fetch_arrayref (), not fetch_array ()

Use UNIQUE or DISTINCT as already suggested if you want just a single value

or, remember what you saw:

$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare (qq;
    select tax_id
    from   gene2acc
    where  genomic_ac_ver = '$ref';
    ); # qq;; is a nice way to make cut-n-paste to sql easier
$sth->execute;
my $tax_id;
$sth->bind_columns (\$tax_id);
my %seen;
while ($sth->fetch) {
    $seen{$tax_id}++ and next; # Only use first occurance
    print "Entering taxID_format with $tax_id\n";
   #taxID_format ($tax_id);
    }
keys %seen or print "No records matched for $ref\nn";
[download]

$sth->fetchrow_array () returns what it indicates: a list not a scalar

...up to a point. In the case:

  while($array = $sth1->fetchrow_array())
[download]

i.e. in scalar context, Programming the Perl DBI says it will return the contents of the first column.

However, the documentation http://search.cpan.org/~timb/DBI-1.607/DBI.pm#fetchrow_array is more equivocal:

If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an undef is returned if there are no more rows or if an error occurred. That undef can't be distinguished from an undef returned because the first field value was NULL. For these reasons you should exercise some caution if you use fetchrow_array in a scalar context.

This doesn't appear to be the root of the problem. But when faced with an apparently hidden problem, I'm a big believer in clearing away the obviously problematic.

thats the output.... when I do the same query in mysql with all these ids: pulls out matching records : here the syntax I used on mysql: SELECT tax_id FROM gene2acc where genomic_ac_ver = 'CP000950.1'; here is what I used in the script: SELECT tax_id FROM gene2acc where genomic_ac_ver = '?'; nin foreach loop with CP000950.1 No records matched for CP000950.1 nin foreach loop with CP000950.1 No records matched for CP000950.1 please help me ....got lot of work!

out put is here...sorry about that: nin foreach loop with CP000720.1 No records matched for CP000720.1 nin foreach loop with CP000950.1 No records matched for CP000950.1 nin foreach loop with CP000950.1