I know the original question was about views and parameters, but if the OP just needs the answer to this kind of query and a view isn't mandatory, I've been able to use a parameter with the HAVING clause. The query could be rewritten:
select artistname from artist join ITEM_ARTIST on artist.artistid = ITEM_ARTIST.artistid group by artistname having count(ITEM_ARTIST.artistid) = @desireditemcount Regards Tim Romano Cariotoglou Mike wrote: > I thought of that, and the answer is NO. > actually, since my post, I did a little investigation : > it is not the view that is the problem, it is the parameter when used on a > column that contains > the COUNT function. Weird, it seems like a genuine bug. > > if you replace > > WHERE CNT LIKE :PARAM > > in my original code, or in your example, it will work ! > > I tried this : (the subselect was originally the view) > > select * from > ( > select > *,(select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) CNT > from ARTISTS > ) where cnt like :a > > and it works. > changing the operator from "LIKE" to "=" breaks it. > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org on behalf of Darren Duncan > Sent: Mon 12/14/2009 2:44 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Using Parameters with views > > Do bind parameters work for you if you replace the view with a subselect? > > Does this work for you? > > select * from ( > select t1.*, > (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) as > CNT > from artists > ) where CNT=:PARAM > > -- Darren Duncan > > Cariotoglou Mike wrote: > >> I don't know if this has come up before, is so please point me to the >> right direction :) >> >> I believe that using parameterized queries with views does not work >> as expected. >> >> consider this (more or less self-explanatory) schema: >> >> create table artists(artist_id) >> create table songs(song_id) >> create table song_artist(song_id,artist_id) >> >> create view VARTISTS as >> select t1.*, >> (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) >> as CNT >> from artists >> >> The sql may be a little off as I am typing this from scratch, but you >> get the >> idea. >> >> now, if I do this: >> >> select * from VARTISTS where CNT=10 >> >> it works. >> >> This, however, always returns an empty result set : >> >> select * from VARTISTS where CNT=:PARAM, >> >> for any value I bind to :PARAM. >> >> is this known/documented ? >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ------------------------------------------------------------------------ > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ------------------------------------------------------------------------ > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.427 / Virus Database: 270.14.106/2563 - Release Date: 12/13/09 > 19:47:00 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
