Accepting parameters for an IN() expression is a common and well known situation among database developers, and the typical solution involves a split() function of similar that's supported by just about every db but MySQL. Look me up on Stack Overflow, you'll see I know what I'm talking about in this area.
Sent from my iPad > On Apr 22, 2016, at 9:55 AM, Paul A <[email protected]> wrote: > > At 09:09 AM 4/22/2016 -0500, Coehoorn, Joel 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. > > Given that the query below works perfectly on CLI (using real values 1, 2, 3 > rather than the << input, input2, input3 >> construct), maybe we should look > at the code that prepares the MySQL query, rather than just saying that MySQL > doesn't remove the single quotes. > > I haven't had time to chase down whether it's Koha or a standard library that > adds the quote marks, but wherever it is, it should be fairly trivial to > remove them. > > Best -- Paul > > >> 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 _______________________________________________ Koha mailing list http://koha-community.org [email protected] https://lists.katipo.co.nz/mailman/listinfo/koha
