DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD ERROR: error possibly near <*> indicator at char

0
votes

I have 2 tables A and B. I'm taking the name value of each row of table A in the array var1 and then put values of array var1 in WHERE condition of a SELECT query of table B and print the result of SELECT query of table B.

Code:

my $sql2 = 'select name,value from A';

my $sth2 = $dbh1 ->prepare($sql2);
$sth2 ->execute();
while (my @row = $sth2 ->fetchrow_array)
{
    print join(',', @row), "\n\n";
    push(@var1, $row[0]);
}

foreach(@var1 ) {
    print "$_\n";
}

print "\n";

my $sth3 = $dbh ->prepare("select name, value from B where  name in (".join(", ",@var1).")"); -  line 99
$sth3 ->execute(@var1);

while (my @row = $sth3 ->fetchrow_array)    
{
    print join(", ", @row), "\n";
}

But getting error:

DBD::Oracle::db prepare failed: ORA-00904: "5": invalid identifier (DBD ERROR: error possibly near <> indicator at char 143 in 'select name,value from B where name in(1,2,3,4,<>5)') [for Statement "select name,value from B where name in(1,2,3,4,5)"] at hello.pl line 99. Can't call method "execute" on an undefined value at hello.pl line 102.

1
sqloracleperl
You aren't specifying the column that your values are to be in. That is, there should be some column name between the "where" and the "in". - gsiems
@gsiems updated the code with column name .:- Error: DBD::Oracle::db prepare failed: ORA-00904: "5": invalid identifier (DBD ERROR: error possibly near <*> indicator at char 143 in 'select name,value from B where name in(1,2,3,4,<*>5)') [for Statement "select name,value from B where name in(1,2,3,4,5)"] at hello.pl line 99. Can't call method "execute" on an undefined value at hello.pl line 102. - Nitin Punekar
>> use strict; use warnings; use diagnostics; << - mpapec

1 Answers

0
votes

The query you are using is incorrect

select name,value from A where in (1,2,3,4,5)

Add a column name between Where and In 

select name,value from A where *column_you_checking* in (1,2,3,4,5)

my $sth3 = $dbh->prepare(select name,value from B where *column_you_checking* in(".join(",",@var1)."));   -  line 99