[Koha] Handling SELECT with Empty Input

[Guillermo]

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 at 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 at aol.com [h]
>     cmkelleymls at 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>