[Koha] [Koha-devel] Extracting Frequency of a Serial From metadata

[Paul Hoffman]

Each string produced by Pasi's query -- and, if I'm interpreting it 
correctly, by your original query -- is the complete contents of 
subfield $a in a 310 field.  According to the MARC21 standard, this is a 
free-text subfield -- it does not (commonly) contain coded (or even 
readily parseable) information.

Here is the Library of Congress's brief description of that subfield:

$a - Current publication frequency
Complete statement, exclusive of dates, of the current publication frequency.

And here are some examples of its contents:

310 ##$aMonthly
310 ##$aMonthly, including annual cumulation
310 ##$aBimonthly (monthly June-July)
310 ##$aContinuously updated 

See http://www.loc.gov/marc/bibliographic/bd310.html for further 
details.

I hope this helps.

Paul.

On Tue, Jul 02, 2019 at 02:35:40PM +0530, RAGHAV ARORA wrote:
> Sir,
> 
> Even this query gives certain ambiguous values like 'SM', 'BM', 'Q', etc.
> What do these values mean?
> 
> Thanks in advance
> Regards
> *Raghav Arora*
> Sophomore, M.Sc (Hons) Chemistry BE Electrical and Electronics Engineering
> Contact : (+91) 9897597761, 8474975691
> Personal Email <raghavarora999 at yahoo.in> | University Email
> <f20171016 at pilani.bits-pilani.ac.in>
> LinkedIn <https://www.linkedin.com/in/raghav-arora-9820a648/> |  GitHub
> <https://github.com/RAraghavarora/>
> 
> ▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
> *Birla Institute of Technology and Science, Pilani*
> Pilani campus,
> Rajasthan-333031
> 
> 
> On Tue, Jul 2, 2019 at 9:28 AM RAGHAV ARORA <
> f20171016 at pilani.bits-pilani.ac.in> wrote:
> 
> > Thank you sir,
> > I will try this out and let you know.
> >
> > Regards
> > *Raghav Arora*
> > Sophomore, M.Sc (Hons) Chemistry BE Electrical and Electronics Engineering
> > Contact : (+91) 9897597761
> > Personal Email <raghavarora999 at yahoo.in> | University Email
> > <f20171016 at pilani.bits-pilani.ac.in>
> > LinkedIn <https://www.linkedin.com/in/raghav-arora-9820a648/> |  GitHub
> > <https://github.com/RAraghavarora/>
> >
> > ▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
> > *Birla Institute of Technology and Science, Pilani*
> > Pilani campus,
> > Rajasthan-333031
> >
> >
> > On Mon, Jul 1, 2019 at 4:53 PM Pasi Kallinen <pasi.kallinen at koha-suomi.fi>
> > wrote:
> >
> >>
> >> Those locates really made it look too complex. I'd rather use
> >> ExtractValue to get the MARC field values, perhaps something along these
> >> lines:
> >>
> >> select ExtractValue(metadata,
> >> '//datafield[@tag="310"]/subfield[@code="a"]') as frequency from
> >> biblio_metadata where ExtractValue(metadata,
> >> 'count(//datafield[@tag="310"]/subfield[@code="a"])') > 0;
> >>
> >>
> >> --
> >> Pasi Kallinen
> >> Koha-Suomi oy
> >> +358 400 174 235
> >>
> >>
> >>
> >> Jul 1, 2019, 1:26 PM by f20171016 at pilani.bits-pilani.ac.in:
> >>
> >> > Hello everyone,
> >> > The SQL query to get the frequency for different serials was given on
> >> the KOHA SQL Library as :
> >> >                        SELECT
> >> >                         IF
> >> >                         (   LOCATE('<datafield tag="310"',
> >> biblio_metadata.metadata) = 0
> >> >                                 OR
> >> >                             LOCATE('<subfield code="a">',
> >> biblio_metadata.metadata, LOCATE('<datafield tag="310"',
> >> biblio_metadata.metadata)) = 0
> >> >                                 OR
> >> >                             LOCATE('<subfield code="a">',
> >> biblio_metadata.metadata, LOCATE('<datafield tag="310"',
> >> biblio_metadata.metadata))
> >> >                                 > LOCATE('</datafield>',
> >> biblio_metadata.metadata, LOCATE('<datafield tag="310"',
> >> biblio_metadata.metadata)),
> >> >                         '',
> >> >                         SUBSTRING( biblio_metadata.metadata,
> >> >                             LOCATE('<subfield code="a">',
> >> biblio_metadata.metadata, LOCATE('<datafield tag="310"',
> >> biblio_metadata.metadata)) + 19,
> >> >                             LOCATE('</subfield>',
> >> biblio_metadata.metadata, LOCATE('<subfield code="a">',
> >> biblio_metadata.metadata,
> >> >                                 LOCATE('<datafield tag="310"',
> >> biblio_metadata.metadata)) + 19)
> >> >                                 -
> >> >                             (LOCATE('<subfield
> >> code="a">',biblio_metadata.metadata, LOCATE('<datafield tag="310"',
> >> biblio_metadata.metadata)) + 19)
> >> >                             )
> >> >                         )
> >> >                     AS FREQUENCY
> >> >
> >> > When I run this query, it gives the output like 54/yr for some
> >> journals, which is correct, but for some, it returns something like 'M',
> >> '0', 'FN', 'SM', '#N/A', etc.
> >> > Can someone please help me understand what should be the correct SQL
> >> query?
> >> >
> >> > Thanks in advance
> >> > Regards
> >> > Raghav Arora
> >> > Sophomore, M.Sc (Hons) Chemistry BE Electrical and Electronics
> >> Engineering
> >> > Contact : (+91) 9897597761, 8474975691
> >> > Personal Email <mailto:raghavarora999 at yahoo.in>>  | > University Email
> >> <mailto:f20171016 at pilani.bits-pilani.ac.in>
> >> > LinkedIn <https://www.linkedin.com/in/raghav-arora-9820a648/>>  |  >
> >> GitHub <https://github.com/RAraghavarora/>>
> >> >
> >> > ▄▄▄▄▄▄▄▄▄▄▄▄> ▄▄▄▄▄▄▄▄▄▄▄▄▄> ▄▄▄▄▄▄▄▄▄▄▄▄
> >> > Birla Institute of Technology and Science, Pilani
> >> > Pilani campus,
> >> > Rajasthan-333031
> >> >
> >>
> >>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha

-- 
Paul Hoffman <paul at flo.org>
Software Manager
Fenway Library Organization
550 Huntington Ave.
Boston, MA 02115
617-989-5032