Moon, John [mailto:[EMAIL PROTECTED] wrote:
>
> The following is an attempt to use a placeholder in an "in" clause...
>
> my $placeholder = q{'} . join(q{','}, @accounts) . q{'};
> $\ = "\n"; #set Output record separator
> print " Given Group Id: $std_group";
> print " Group Title is: $title";
> print "Groups Accounts: $placeholder";
> my $sth_budget = $dbh->prepare(q{select to_char(sum(budget_amount),
> '$999,999,999.99') from t_customer_budget
> where fiscal_year = to_date(?, 'dd-Mon-yyyy') and account_num in
> (?)}
> or &MyDieBatch('prepare failed', 80, __LINE__);
> my $budget;
> $sth_budget->bind_columns(\$budget)
> or &MyDieBatch('bind failed', 80, __LINE__);
> $sth_budget->execute($FY, $placeholder);
> $sth_budget->fetch;
>
> I have also tried:
> my $placeholder = join(q{','}, @accounts);
>
> thinking it would put the quotation marks at the beginning and end of the
> "string" ...
A placeholder takes the place of a *single* value. If you want to bind 7
values, you have to use 7 placeholders.
Here's one way to do it:
my $sth = $dbh->prepare(<<"EndOfSQL");
SELECT TO_CHAR(SUM(budget_amount), '$999,999,999.99')
FROM t_customer_budget
WHERE fiscal_year = TO_DATE(?, 'dd-Mon-yyyy')
AND account_num IN ( @{[ join ',', ('?') x @accounts ]} )
EndOfSQL
$sth->execute($FY, @accounts);
That uses the repetition operator (x) to create as many placeholders as
there are entries in the array.
Of course, you will need to prepare a new statement every time the number of
entries changes.
HTH,
Ronald
