[Koha] Report question

[Coehoorn, Joel]

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.

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
>