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
