[Koha] Report question

[Barton Chittenden]

Joel,

I think this has more to do with the code perl code rather than MySQL ...
there are other transformations that we do on the parameters that get
passed to the SQL It would be nice if it handled paramters handed to 'IN
()' as well.

Having said that, I too would like to see Koha un-married from MySQL --
ideally, it would all be database independent. I think that we're gradually
working that direction by virtue of DBIC, but do a 'git grep -i select' to
see how far we still have to go.

--Barton

On Fri, Apr 22, 2016 at 10:09 AM, Coehoorn, Joel <jcoehoorn at york.edu> 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.
>
> 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
>