Joel, I think this has more to do with the code perl code rather than MySQL ... there are other transformations that we do on the parameters that get passed to the SQL It would be nice if it handled paramters handed to 'IN ()' as well.
Having said that, I too would like to see Koha un-married from MySQL -- ideally, it would all be database independent. I think that we're gradually working that direction by virtue of DBIC, but do a 'git grep -i select' to see how far we still have to go. --Barton On Fri, Apr 22, 2016 at 10:09 AM, Coehoorn, Joel <[email protected]> wrote: > This is one of those things that would be EASY in Sql Server, Oracle, or > Postgresql, which all have a split() function that can produce a set > suitable for use with an IN() clause. > > Sadly, MySql has failed to keep pace with other DB platform, going even > back before it's acquisition by Oracle. It lacks split and a few other > functions, uses non-compliant NULL handling by default, does not have CTEs, > Windowing Functions, a lateral join/APPLY operation, or a FULL JOIN > operation. I'd really love the option of running Postgresql instead. These > days, it outclasses MySql in pretty much every category, and has done so > for nearly a decade now. > > > > Joel Coehoorn > Director of Information Technology > 402.363.5603 > *[email protected] <[email protected]>* > > The mission of York College is to transform lives through > Christ-centered education and to equip students for lifelong service to > God, family, and society > > On Fri, Apr 22, 2016 at 1:48 AM, Graham, Stephen <[email protected]> > wrote: > > > Thanks François, I suspected as much. I've tried doing things like: > > > > 35','36','37 > > > > , but the report tool just escapes the ' with a backslash. Very > > frustrating! > > > > I guess I could use an OR, but I don't know how many values would be > > passed each time. I guess I'll need to write a custom cgi script which > > outputs some JSON for what I want. > > > > Cheers, Stephen > > > > -----Original Message----- > > From: Koha [mailto:[email protected]] On Behalf Of > Francois > > Charbonnier > > Sent: 21 April 2016 17:14 > > To: [email protected] > > Subject: Re: [Koha] Report question > > > > Hi Stephen, > > > > I don't see any workaround but right now, it can't work. > > > > Let's say the <<BIBIDS>> value is : 34,35,36 > > > > With the IN operator, you have to get the query this way : IN(34,35,36) > > but with the <<BIBIDS>>, you'll get IN('34,35,36') instead. The query > will > > look for the expression '34,35,36' which is not a biblionumber. > > Therefore, you don't have any result. > > > > I guess you could use multiple "OR"... > > > > Hope it helps! > > > > François Charbonnier, > > Bibl. prof. / Chef de produits > > > > Tél. : (888) 604-2627 > > [email protected] <mailto: > [email protected]> > > > > inLibro | pour esprit libre | www.inLibro.com <http://www.inLibro.com> > Le > > 2016-04-21 11:16, Graham, Stephen a écrit : > > > Hi All - I want to set up a SQL report that can take a variable number > > of the same value. My SQL looks like: > > > > > > select > > > biblionumber, > > > concat (branchname, ', ', lib) as location, itemcallnumber, if(onloan > > > is null,'Available',onloan) onloan from > > > items,authorised_values,branches where biblionumber in (<<BIBIDS>>) > > > and authorised_values.category = 'LOC' > > > and items.location = authorised_values.authorised_value > > > and items.homebranch = branches.branchcode > > > > > > I want the <<BIBIDS>> value to be able to receive several values when I > > run the report from the staff client, but I cannot get it to work. Any > > advice/tips much welcome! > > > > > > Stephen > > > > > > Stephen Graham > > > Online Information Manager > > > Information Collections and Services > > > Library and Computing Services > > > University of Hertfordshire > > > Hatfield AL10 9AB > > > UK > > > Tel. 01707 286111 Ext: 77751 > > > Email [email protected] > > > > > > _______________________________________________ > > > Koha mailing list http://koha-community.org [email protected] > > > https://lists.katipo.co.nz/mailman/listinfo/koha > > > > _______________________________________________ > > Koha mailing list http://koha-community.org [email protected] > > https://lists.katipo.co.nz/mailman/listinfo/koha > > _______________________________________________ > > Koha mailing list http://koha-community.org > > [email protected] > > https://lists.katipo.co.nz/mailman/listinfo/koha > > > _______________________________________________ > Koha mailing list http://koha-community.org > [email protected] > https://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org [email protected] https://lists.katipo.co.nz/mailman/listinfo/koha
