[Koha] Handling SELECT with Empty Input

[Charles Kelley]

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.