[Koha] Report question

[Joel Coehoorn]

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