The general rule for using parameter markers ('?'s) is that a parameter
marker can appear wherever a literal can appear. (As far as I know, this is
true in any ANSI-compliant database; it goes back to the early years of DB2
and SQL/DS.) The substituted parameters are equivalent to literals. Hence,
it makes sense that MySQL would interpret the list of values as a single
string.One way of getting around this would be to substitute the value in the perl script before passing it to the database: "select sum(column) as columnSum from table where value in ( $ValueIDs ) and row_date between cast( ? as date) and cast( ? as date) "); ... but this might not be appropriate in your application. On Tue, Apr 5, 2016 at 12:24 PM, Bruce Ferrell <bferr...@baywinds.org> wrote: > I'm generating a sql statement like this: > > sth = $mysql_dbh->prepare( > "select sum(column) as columnSum from table where value in ( ? ) and > row_date between cast( ? as date) and cast( ? as date) "); > > sth->execute( $ValueIDs ,$week_start_date,$week_end_date); > > $ValueIDs is a series of unquoted values: > > 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164 > > When observed at the mysql server, the sql appears as follows: > > select sum(column) as columnSum where value in ( > '01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164' ) > and row_date between cast( '2016-03-29' as date) and > cast( '2016-04-05' as date) > > resulting in no data being returned. > > When the sql is manually entered as follows: > > select sum(column) as columnSum where value in ( > 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164 ) > and row_date between cast( '2016-03-29' as date) and > cast( '2016-04-05' as date) > > The correct values are returned. > > How can I suppress the quoting for the IN clause? > > >
