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@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha