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
