Hello, I think the solution is to do it directly from the Mysql command interface, creating a stored procedure and running it from the Mysql console. Developing a modification so that you can do it from the reports GUI interface is something else. Ing. Guillermo González El mar., 19 de oct. de 2021 9:04 p. m., Charles Kelley < cmkelleymls@gmail.com> escribió:
Hi, all!
In one of my library's reports, the full SELECT statement reads as follows:
SELECT items.barcode AS 'Barcode Number' ExtractValue(metadata,'//datafield[@tag="092"]/subfield[@code="a"]') AS 'Class Number', ExtractValue(metadata,'//datafield[@tag="092"]/subfield[@code="b"]') AS 'Cutter', items.ccode AS 'Collection', items.location AS 'Shelf Location', items.itype AS 'Item Type', items.itemnumber AS 'Item Record Number', biblio.biblionumber AS 'Bib Record Number' FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber) WHERE items.dateaccessioned = <<Which day?|date>> AND items.ccode = <<Which collection?|ccode>> AND items.location = <<Which shelving location?|loc>>
I have a question: On a test run, I forgot to input a date (dateaccessioned = <<Which date? ...>) and I got a software error. On the next and subsequent runs, I included a date, and it ran perfectly well.
My question is this: is it possible to test for input and substitute a default value. With IF, WHILE, etc. statements, program application code handles this sort of thing well. SQL, I don't know. It might look like the following in pseudo SQL code picking up from the SQL:
WHERE (items.dateaccessioned = <<Which collection?|ccode>> BUT IF items.dateaccessioned IS NULL THEN items.dateaccessioned = TODAY) AND (items.ccode = <<Which collection?|ccode>> BUT IF items.ccode IS NULL THEN items.ccode = THIS_COLLECTION) AND (items.location = <<Which shelving location?|loc>> BUT IF items.location IS NULL THEN items.ccode = THIS_LOCATION)
in order to avoid a software error. A conditional input check, so to speak.
(BUT, IF, and THEN are my pseudocode extensions to SQL statements.)
I know this is partly built on my limited understanding of SQL and I probably hope against hope. That's fine; I'll just make sure the staff are taught to select something from the dropdown menus or enter something in textboxes if te want to avoid the software error.
If it's possible in a Perl routine or, dare I say, required in a Perl routine, I'll try to teach myself enough Perl to do the Perl analog to something like this with the understanding that in future editions of Koha, the schema and structure may change enough to render the Perl routine obsolete.
Thanks, all.
--
気を付けて。 /ki wo tukete/ = Take care.
-- Charles.
Charles Kelley, MLS PSC 704 Box 1029 APO AP 96338
Charles Kelley Tsukimino 1-Chome 5-2 Tsukimino Gaadenia #210 Yamato-shi, Kanagawa-ken 〒242-0002 JAPAN
+1-301-741-7122 [US cell] +81-80-4356-2178 [JPN cell]
mnogojazyk@aol.com [h] cmkelleymls@gmail.com [p]
linkedin.com/in/cmkelleymls <http://www.linkedin.com/in/cmkelleymls> Meeting Your Information Needs. Virtually. _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha