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@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@yahoo.in>>  | > University Email <mailto:f20171016@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
>