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 *jcoehoorn@york.edu <jcoehoorn@york.edu>*
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 <s.graham4@herts.ac.uk> 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:koha-bounces@lists.katipo.co.nz] On Behalf Of Francois Charbonnier Sent: 21 April 2016 17:14 To: koha@lists.katipo.co.nz 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 francois.charbonnier@inLibro.com <mailto:francois.charbonnier@inLibro.com>
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 s.graham4@herts.ac.uk
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha