[Koha] Report question

[Paul A]

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