[Koha] weird report behaviour or just bad SQL?

[Andreas Roussos]

Hi Carlos,

I think that to match on character ranges you'll have to use RLIKE, 
which is the regex-capable counterpart of the LIKE function.

Something like the following should work:

SELECT itemnumber, barcode, ccode, itemcallnumber, datelastseen
FROM items
WHERE location = "DISPLAY"
AND itemcallnumber RLIKE "^[A-M]"
ORDER BY ccode, itemcallnumber

A drawback of this approach is that it will also match items with call 
numbers beginning with lowercase A to M.

I hope this helps.

Kind regards,

Andreas

On 2021-04-19 09:44, Carlos Lopez wrote:
> Hi folks
>
> I have a report that lists items with a location of "DISPLAY":
>
> SELECT itemnumber, barcode, ccode, itemcallnumber, datelastseen
> FROM items
> WHERE location = "DISPLAY"
> ORDER BY ccode, itemcallnumber
>
> At the moment it brings up 18 items.
>
> itemnumber
> barcode
> ccode
> itemcallnumber
> datelastseen
> 270404
> 33865001661351
> BP47 P659 H461
> 2021-04-07
> 270195
> 33865001666699
> DK39 (34) P265
> 2021-03-11
> 269921
> 33865001662516
> DL38 S617
> 2021-03-22
> 270241
> 33865001667093
> KH48 F1 XL21
> 2021-03-12
> 270374
> 33865001661237
> MM22 F778
> 2021-03-29
> 270371
> 33865001661112
> MW61.64 S951
> 2021-03-29
> 270104
> 33865001662771
> MW68 D976 A
> 2021-03-02
> 270293
> National Catholic reporter
> 2021-03-15
> 270181
> 33865001665170
> RC66 C559
> 2021-04-07
> 267602
> 33865001664009
> RK56 E928
> 2021-03-30
> 270281
> 33865001666749
> SA6 P984
> 2021-03-12
> 270432
> 33865001665212
> SD80.91 K49
> 2021-04-14
> 270283
> 33865001666806
> SM31 M191
> 2021-03-15
> 270426
> 33865001667325
> WD78 H582
> 2021-04-09
> 270071
> 33865001662987
> WW10 W456
> 2021-04-13
> 270225
> 33865001667101
> XY28 H973
> 2021-03-15
> 270397
> 33865001667390
> XY28 T814
> 2021-04-01
> 237930
> 33865001656583
> JESUIT
> TE G573
> 2021-03-04
>
> If I modify it to only bring up items with itemcallnumber beginning with A to M
>
> SELECT itemnumber, barcode, ccode, itemcallnumber, datelastseen
> FROM items
> WHERE location = "DISPLAY"
> AND itemcallnumber LIKE "[A-M]%"
> ORDER BY ccode, itemcallnumber
>
> I expect to get the top 7 results from the list above, but instead I get zero results.
>
> Is it that the Reports area on Koha restricts normal SQL functionality, or am I doing something wrong?
>
> With kind regards from the Dalton McCaughey Library Team
>
> Carlos Lopez
>
> Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
> Ph: 03 9340 8888 ext.1 | library at dml.vic.edu.au<mailto:library at dml.vic.edu.au> | library.dmlibrary.org.au
>
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha