Re: [Koha] [Koha-devel] Extracting Frequency of a Serial From metadata
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@yahoo.in> | University Email <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 On Mon, Jul 1, 2019 at 4:53 PM Pasi Kallinen <pasi.kallinen@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@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
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@yahoo.in> | University Email <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 On Tue, Jul 2, 2019 at 9:28 AM RAGHAV ARORA < f20171016@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@yahoo.in> | University Email <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
On Mon, Jul 1, 2019 at 4:53 PM Pasi Kallinen <pasi.kallinen@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@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
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@yahoo.in> | University Email <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
On Tue, Jul 2, 2019 at 9:28 AM RAGHAV ARORA < f20171016@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@yahoo.in> | University Email <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
On Mon, Jul 1, 2019 at 4:53 PM Pasi Kallinen <pasi.kallinen@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@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
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Paul Hoffman <paul@flo.org> Software Manager Fenway Library Organization 550 Huntington Ave. Boston, MA 02115 617-989-5032
Hi!
Even this query gives certain ambiguous values like 'SM', 'BM', 'Q', etc. What do these values mean?
My guess is that the one & two letter values are abbreviations, e.g.: SM = Semimonthly BM = Bimonthly Q = Quarterly 0 = Irregular... Current cataloging under RDA (Resource Description and Access) avoids abbreviations because they can be very ambiguous and confusing, so those abbreviations might be able to be upgraded to the full words, if that is what those abbreviations stand for. There is also a place for codes for frequencies in a MARC21 record, field 008 position 18 in Continuing Resources records: https://www.loc.gov/marc/bibliographic/bd008s.html Perhaps those values in your records might help? Cheerio! h2 ~~~~~~~~~~~~~~ Heather Hernandez (she, her, hers) Technical Services Librarian San Francisco Maritime National Historical Park Research Center 2 Marina Blvd., Bldg. E, 3rd floor, San Francisco, CA 94123-1284 415-561-7032, heather_hernandez@nps.gov Library catalog: http://keys.bywatersolutions.com/
participants (3)
-
Hernandez, Heather -
Paul Hoffman -
RAGHAV ARORA