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

[RAGHAV ARORA]

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
> >
>
>