[Koha-devel] Extracting Frequency of a Serial From metadata
Pasi Kallinen
pasi.kallinen at koha-suomi.fi
Mon Jul 1 13:23:23 CEST 2019
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
>
More information about the Koha-devel
mailing list