Re: How to reuse a placeholder

You can't use a ?-style placeholder in a multi-statement SQL batch.

The way around it in your case would be to pass the date in as a string and
interpolate it directly instead of trying to use the placeholder.

Michael

On 10/27/05, Ivor Williams <[email protected]> wrote:
>
> I have a query, which needs a date substituting twice. Declaring an
> intermediate variable seemed the natural way to do this viz:
>
> my $nots = $dbh->selectall_arrayref(<<SQL, {}, $end);
> declare \@eom datetime
> select \@eom = ?
> select ccy.currencyCode Currency,
> swp.loadStatus,
> sum(leg.notionalAmount) Notional,
> count(*) Trades
> from $swap_table swp,
> SWAPFLOATINGLEG leg,
> $reference..CURRENCY ccy
> where swp.terminationDate > \@eom
> and swp.registeredBusinessDay <= \@eom
> and leg.oid = swp.$leg_column
> and ccy.oid = leg.currency
> group by ccy.currencyCode, swp.loadStatus
> SQL
>
> Unfortunately, this is not being parsed properly, as can be seen from
> running with DBI_TRACE=1
>
> <- selectall_arrayref('declare @eom datetime
> select @eom = ?
> select ccy.currencyCode Currency,
> swp.loadStatus,
> sum(leg.notionalAmount) Notional,
> count(*) Trades
> from PLAINVANILLASWAP swp,
> SWAPFLOATINGLEG leg,
> REFERENCE_TST2..CURRENCY ccy
> where swp.terminationDate > @eom
> and swp.registeredBusinessDay <= @eom
> and leg.oid = swp.floatingLeg
> and ccy.oid = leg.currency
> gro...' HASH(0x8ac34c) ...)= undef at eom_stats.pl line 177
> DBD::Sybase::db selectall_arrayref failed: Server message number=7332
> severity=15 state=1 line=1 server=SYB_SWPCLR_TST procedure=DBD1 text=The
> untyped variable ? is allowed only in in a WHERE clause or the SET clause of
> an UPDATE statement or the VALUES list of an INSERT statement
> ERROR: 7332 'Server message number=7332 severity=15 state=1 line=1
> server=SYB_SWPCLR_TST procedure=DBD1 text=The untyped variable ? is allowed
> only in in a WHERE clause or the SET clause of an UPDATE statement or the
> VALUES list of an INSERT statement
> ' (err#0)
>
> ...
>
> I could pass in the parameter twice, but I was hoping to be able to reuse
> the placeholder by declaring an intermediate variable, which works in isql.
> What am I doing wrong?
>
> Alternatively, is there a syntax in DBI for reusing a placeholder.
>
> Ivor.
>
>