Koha-US The Terrific Every-Other-Thursday Training Videos, 4 Feb 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: TTEOT Training Video - S1E11 - SQL: ExtractValue

Description

In this exciting, action-packed episode, George goes in for an extraction - of subtitles in Marc records. Will he get them out in one piece? Watch, and find out!

Referenced code: https://wiki.koha-community.org/wiki/SQL_Reports_Library

Music: https://www.bensound.com

A

Welcome to another training video, it's that time again, I'm christopher brannan from the coeur d'alene public library in coeur, d'alene, idaho, with the cooperative information network and I'm george williams from northeast kansas library in lawrence kansas and uh today we're going to be talking about another thing you can do in reports, um so this is actually something.

A

What was that another report? Another report, another report thing like I said uh last week like I handed out this is the last like little thing uh in a report, and next week will be a longer video about a report that I use regularly.

A

That combines everything that we've talked about recently, like uh links and reports how to use concatenation in a report how to use a.

A

Unions, how to do extract mark, which is what I'm going to talk about today. There have been like six videos in a row. We've been talking about these things. You can do in a report and next week I'm going to have a report that uses every single one of those things. Then, after that we'll finally get away from yeah, be cool for a little bit and do some other yeah. Then we can then we'll do something else for a week, then we'll be back in reports.

A

uh So I'm at the koha u.s site and if you go to uh koha, dash, us.org and click on, learn from us, you can find these videos and all of our education stuff on the learn from us page. The quick way to get to the videos is click on koha us videos and uh well. I guess right here is a good, a good example of what we've been talking about.

A

Lately, we've got links and reports concatenations and if statements like versus equals, dates and sub queries, coalesce and sql unions, so uh they'll be by the time we're done with this, there will be eight in a row on sql wow. So that's that's. uh We've probably beaten this uh dead horse into submission. Finally,.

A

uh But many of the reports, many of the techniques that we use you can find a lot of reports on the the quahog reports, library, which is on the wiki community.org.

A

Wiki.Coha-Community.Org and then, if you look for sql reports library, you can find a lot of this stuff and a bunch of the stuff we've talked about is already here. What we're going to talk about today is query, mark and there's a couple of different ways to do this. I'm going to talk about uh um this version here, uh which is the way I learned it, and actually it was uh you know I I understood how to do this.

A

I'd seen it, um but christopher was the first person who wrote a report um that he shared with me, like you know, eight nine years ago, that used this technique, and so you know I what I know about this when I started using, it was something I learned from christopher.

A

And I learned that from either the reports uh wiki itself or from bywater one or the other bywater has been extremely helpful in this as well yeah. So.

A

So, let's go to the staff client, I'm using my cohort, I'm not using the koa us demo, because we don't have a lot of stuff in the koa us demo. Yet.

A

uh But I've got uh some reports here and you know one of the first things that uh that I use this for was for um getting subtitles, and that was what I saw you using it for was getting the subtitles, uh the stuff in the 245 b field and the mark record into a report so that, if you're looking at data on the page, if you just use the title field- and your was your report returns a result of diary of a wimpy kid.

A

Well, there's like eight diary of a wimpy kid books and the title is all diary of a wimpy kid and then there's a colon and the subtitle is what tells you which one of the books it is now if you're in a library like any of the ones I've worked in when you go to the diary of a wimpy kid shell fund in the youth services, section there's about this many books in a row and it's diary of a wimpy kid there's a kid dog days diary of a woman. Wimpy kid!

A

uh I don't remember all the different titles. I if my kids were a little younger I'd, probably remember all of them, but there's a bunch of them and if all you got in the report, it says, diary kid, you're not gonna be able to find them, um and so this method uh I did some samples. So let me run the first one. I think the first one is without any information at all.

A

So let's run it, I'm just going to run it from the atchison library on books.

A

And I think this report is just showing us the title yeah. So all we've got here is the title field and I'm going to show the sql, and so what we've got here is.

A

Title and so with you, if you've got any subtitles in those records, um you're not going to see those subtitles when you do bibliotitle as the field. So the second report I've got here, actually shows us what the difference is. I'm going to open that and do the exact same search and search for book at atchison.

A

So let me collapse that there we go so I can see this one here. This is the first report. I ran this line: the third one down north american churches, colon uh dinner with edward colon, dangerous territory, colon.

A

If we look at the second report, when we get to that same line, north american churches, colon from chapels to cathedrals, so it this has the subtitle information on all of those yeah, several of them yeah a whole bunch of them. Subtitles are all over the place in um in bibliographic records and here's the difference in the in the sequel. If we go back and look at this one, I've just got biblio title.

A

If we look at this one, I've got a concatenated field here that I'm calling full title and I've concatenated biblio title uh concatenation with a separator. So the first line is that separator the space and then I've got bibliotitle and then I'm doing this thing called extract value and it's looking at a different table.

A

It's looking at biblio metadata is where the mark record actually lives and in that metadata field, um I'm telling quahog to find the biblio metadata metadata field and from that we're going to extract the value and the value we're going to extract.

A

Is this data field at tag 245 subfield code and I'm getting four of them h, b, p and n, and so that'll give me all essentially all of the data in the 245 I'm going to have it put in this in this in the output for this report, okay and uh some libraries, if you're, fully rda compliant you're not going to have the 245h, because that's the uh general materials designator and if you're 100 compatible with rda you're, not going to have that field anymore.

A

So now here's the thing is um this: is this: is an old-school way of getting this data because now with cohab? If we go to the administration um in the cohort to mark mapping, as of the last upgrade we're on version 1911., um as of the last upgrade the 245b, you can map that to a subfield and it's currently now being mapped uh in our system. It's being mapped to biblio subtitle, so now there is a field in the database where I could just as easily um remove this 245b and have that be biblio.subtitle.

A

Not every library has that set up yet and maybe you're not using using a version. That does that, if you do um add that mark mapping to your system, you have to have uh you have to do a re-index to get the data to populate properly okay, um but this is the old school way of doing it.

A

um I've got another example.

A

Let's see that one was number two right or this one's number two, so I want number three here's another example of something you can get out of the.

A

Out of the extract mark, um I think this one is the 490 information you can also get out of there. So the 490 is the series information. Okay, the this is another one that can be mapped to a. uh I believe, it's a field in the biblio items table, so you can either get it from this way from the metadata or there's another way to get it. If you're mapping over here somewhere along here would be, let's just do a search and find it so biblio items volume is 490 v and there's another one.

A

I think it's biblio atom or bibliosyria serial title is 498. So that's the series statement, so this data can be found too, but some of this data is in the biblio table and some of it is in the biblio items table. So it's in two different places, um so you have to do two different joins, but this gets me series and volume.

A

Okay- and let me show that so I've got uh here- we're doing uh extract value 490.. Oh this one, I've also got you can also pull indicators so uh strictly speaking in the 490 you're not supposed to use, I think there's certain.

A

I don't remember what the rules for indicators are, but I've got a lot of faulty indicators, and so I wrote a report to find the bad indicators and so the difference between extracting the subfield data is, you know the subfield data is the data field at tag and then subfield at code a uh but the indicators is just at indicator, one at indicator: two and the uh when you extract these these values. If there's more than one 490 indicator, one it automatically concatenates them, it automatically. Does a group concat?

A

So this one uh grover's bad awful day? I think I've read that um has three different subfields and they all have an indicator and the indicator is zero, so it plugs all three of them into one field, and so this is a growing up book, a sesame street book and a golden book. So it's got all three of those series: titles concatenated into one field.

A

There, but, like I said this, also gets mapped, um it may be mapped in your system. The difference is, is that the indicators are never mapped. um The series title um in my system is mapped to biblio's serial title and then the other one. uh The volume is mapped to biblio items, which is kind of confusing there.

A

And then I've got another example that one was number three.

A

You know I wrote these reports about a little while ago. uh It's been long enough ago. I don't remember 100 what to do. Okay, so we'll just have to run them and see. What's going on sure, atchison book.

A

I think this next one. I wrote it. Oh uh this one uh other things you can get that are completely not mapped. Is this one is written to get the uh additional authors in the 700 fields, so I've got everything I need to know to find this book on the shelf. There's the author data, but if there are any additional authors, I wrote this so that it gets the additional authors, nice and let me show the the sequel on this.

A

So here we go um so this one. I did concatenate with separator and I did a dash so that if there are.

A

Multiples they'll be separated with the dash. I don't know why I did that because they automatically concatenate.

A

That was an unnecessary step, but the the extract value here is the metadata from biblio metadata, and I just you just have to know the field which in this case is 700 and then it's subfield code a and then that gets me all the additional uh data here and the, and I took the additional step here.

A

One of the things about extracting the value is, if you want to do where a where statement, um when you do the extract value, it's going to extract the data as a string, and so, if you want to say just show me the ones that have data you have to say where the data is not like empty string.

A

um If you do a uh is null you're still going to get uh blank spaces there because is null and empty string are not the same uh in my sequel, yeah.

A

Sorry about the noise, if I can hear that noise in the background we got somebody using a leaf blower to blow uh snow drifts away from the sidewalk.

A

I can't hear it so. uh Okay, I have to see how it turns out on the final video- and uh hopefully we don't have to re-record this section, but reminds me of when we we moved into this house and that we lived in in troy, idaho and uh the neighbors were uh were uh the first day. Were there the neighbors, I can hear them using the weed eater and I'm thinking. Oh, this is nice.

A

We've moved into a neighborhood where people actually take care of their yard and then two hours later, when the weed eater was still running, I realized, oh, my god, they're, not they're, not just trimming. The yard they're mowing their yard, with the weed eater.

A

It lasted it was days it seemed like before they got that yard mode. Oh my gosh, and then so this is another one. uh This is another report. I ran uh 2 205 and this time I'm extracting the 655, which is the genre heading all right, and so let me show how to do that.

A

I've got uh uh extract value, biblio metadata, and in this case I know that the uh subfield is or that the field is 655 and subfield a and then I've got that written down here, where the that subfield is not like uh empty string all right, and so this gets me a list of all of the the titles with the genres.

A

Now there's uh one last one. I've got here so this is all useful. If you're running a report, you want to get that information in there, but you can also we can limit to a specific.

A

If we know what the term is. So I'm going to look for this time, I'm going to look for at the osawatomy library I'm going to look at books.

A

I actually have a report like this written for one of our libraries, where they are trying to build a collection of erotic fiction, and so I actually have.

A

I have a report that uses this, so they can run a report to see what their circulation is on their erotic, fiction, collection and so I've got um you can actually uh limit based on that uh term, and so, if I run this report for the osawatomy library, um this will show me everything where one of the genre terms includes the word erotic, and I can show you the the sequel here. So I've got the same as the last report.

A

We're doing, extract value, biblio.metadata data field, 655 at subfield, a and then down here. I've got that same uh that same uh subfield written in here, but I'm doing like uh and uh and I'm concatenating two wild cards around a search term.

A

That way I don't um it's not just getting the things that say, erotic fiction, some of them. The heading is erotic stories um and actually, if I do edit, I can show you exactly how that where clause is written because what I've got is in the in the where statement. I'm saying where the value I'm looking for is like and then I'm doing, concat and then a wild card comma, and then here I'm doing one of those runtime parameters. So I've got the double angle brackets, which allows me each time I run this report.

A

I can put a different term in there, so I can search different terms and because I've got the wildcard before and after it's just going to look for those terms, whether there's whether they're at the beginning of the sentence or the end of the sentence anywhere. The word erotic when I type that word in there appears in that in that search is going to give me a result.

A

Okay, so that's how you uh that's, how you add the biblio, uh the mark data, that's how you can use a report to get the markdown and, like I said, if you go to the koha community wiki, there are several different ways that they show examples of how to do that, um and this is just one of those ways, but it's the one that I find the most useful for me.

A

It's the easiest one to remember how to write the the thing that that tripped me up for a little bit was uh because that marked mark data that information you can. You can search for data fields, subfields um uh control fields, yeah, uh that's the part that trips me up sometimes is knowing which is which yeah. That's uh that's something I struggle with too, because I'm not a catalog or I've never been a cataloger, and a lot of this is stuff that I've had to learn.

A

um But this is, of course, a great part of being the koha community. Is you if you're uh not sure what you're doing if you're, not sure where to look for something you can always get on? You know. Koha us has the mailing list. If you're a member of the koha us mailing list, you can ask us you can, um if you remember the quahog community mailing list, you can ask there. There are always people willing to help the koha community has their slack, uh not slack channel.

A

They have uh the irc channel, so you can go on the irc and just throw a question out there um by water solutions. Has a slack channel that um by water partners can join and you can ask these questions so and the catalogers um the quahog? Well. Actually, let me share my screen again: that's uh which screen do I want to share this one?

A

um If you go to quahog us and we go back up to the top here, um collaborate with koha us if you go to the collaborate page, we have these this list of.

A

I'm not sure where to find the special interest groups, but here are the mailing lists on the collaborate with us page: go to collaborate and then go to mailing lists, and um there you go special interest groups right down here: special interest groups, um but there are more than just the overall coha uh us mailing list.

A

If you click on special interest groups, there is a cataloging group that meets monthly first thursday of the month, eight to nine, and there is, uh if you go to that and join that group, you can get on the mailing list just for the catalogers group or there's one for acquisitions. There's one for library, consortia, there's one for the demonstration interest, administrator user services, web developments and then there's, of course, the book and crafting club.

A

And uh I've never been to one, but they meet once a month and they just turn on zoom and knit and talk about books and hang out together and be friendly. So you didn't know about that. One. Apparently, I did not.

A

uh So uh these are all the different special interest groups- and you know if you have cataloging questions asking the other catalogers is a great way to give you an answer. So.

A

So that's all I have to say about that all right. Well, thank you very much. So um that's it for the bits and pieces of this whole uh sequel thing. Next week, you're gonna put all this together and wrap it up in a nice neat package with a bow yeah, something like.

A