ISBN field from OPAC marc records

Trying to get ISBN numbers from Innovative OPAC via SQL (from Sierra Listserv)

Hi Liza:

Ray Voelker had some good suggestions for you. I have a very small database compared to many of you so I rarely run into the timeout issues you are experiencing. Ray's suggestions for use of LIMIT and OFFSET along with ORDER may be what you need to do.

I have a few other suggestions that may also help.

1) Get rid of unnecessary JOINs

In your original query, you join bib_view to subfield_view unnecessarily, I think.  subfield_view contains the column "record_type_code" with which you can limit your output to bib records without the need for the join.

On my small database, your original query takes approximately 14 seconds to run whereas the following alternative takes approximately 5.5 seconds. That's quite a saving.>

-- ============================================================================
-- Option 1 - no joins to other views
-- ============================================================================
SELECT
  isbn.content                     AS "ISBN"
FROM
  sierra_view.subfield_view        AS isbn
WHERE
  isbn.record_type_code = 'b'
  AND
  isbn.marc_tag IN ('020','024')
  AND
  isbn.tag = 'a'
; 

Note that I have also used the IN ('n1','n2','n3',etc) format in place of the ORs in your original query. I don't think it saves anything on processing time but it sure saves typing time.


2) Removing extraneous data from ISBNs

You have already limited your output to only subfield 'a' of 020 or 024 so you shouldn't be seeing any subfield delimiters. But, you may have extraneous information within subfield 'a' following the number. Assuming that there is always a space between the number and extraneous data (which may be an incorrect assumption), you could extract the portion of the string that appears before the space like this:

-- ============================================================================
-- Option 2 - with CASE statement to remove data that follows ISBN number
--   Assumes that there is a space following the number
-- ============================================================================
SELECT
  CASE
    WHEN POSITION(' ' IN isbn.content) != 0
    THEN SUBSTR(isbn.content,1,POSITION(' ' IN isbn.content)-1)
    ELSE
    isbn.content
  END                                              AS "ISBN (Edited)"
FROM
  sierra_view.subfield_view                        AS isbn
WHERE
  isbn.record_type_code = 'b'
  AND
  isbn.marc_tag IN ('020','024')
  AND
  isbn.tag = 'a'

On my small database, I didn't see a noticeable increase in processing time with the addition of the CASE statement but you might see one with a large database. You'll have to weigh the pros and cons.


3) Limiting to first ISBN per record

Each bib record can have multiple ISBNs. Depending on your project, you may not need to export all of them. If your project could get by with only one ISBN per record, you can trim off some more time by limiting to the first ISBN per record. The following query run over my small database takes a bit less than 4 seconds:

-- ============================================================================
-- Option 3 - Exclude all but the first ISBN found for each record
-- ============================================================================
SELECT
  CASE
    WHEN POSITION(' ' IN isbn.content) != 0
    THEN SUBSTR(isbn.content,1,POSITION(' ' IN isbn.content)-1)
    ELSE
    isbn.content
  END                                              AS "ISBN (Edited)"
FROM
  sierra_view.subfield_view                        AS isbn
WHERE
  isbn.record_type_code = 'b'
  AND
  isbn.marc_tag IN ('020','024')
  AND
  isbn.tag = 'a'
  AND
  isbn.occ_num = 0
;
 

--
Brent Searle
Library Systems Manager

On 2015-05-01 12:00 PM, Liza wrote:

Hi all,

I am trying to pull a list of ISBNs to give to vendors. The SQL query times out with this error:

----------------------------------------------------

ERROR: canceling statement due to statement timeout

SQL state: 57014

----------------------------------------------------

I have tried adding a LIMIT and it will run if the limit is under 100000. If I run the query with a limit, how do I run it again where the first query left off? 

I would also like to get just the ISBNs without the delimiters. I can put the results into Excel and get rid of the delimiters there, but is there a way to get just the numbers in the SQL results? 

Here's the query:

SELECT subfield_view.content as ISBN

FROM sierra_view.bib_view, sierra_view.subfield_view

  WHERE (subfield_view.record_id = bib_view.id) AND ((subfield_view.marc_tag = '020') OR (subfield_view.marc_tag = '024')) AND (subfield_view.tag = 'a');

Query with the limit:

SELECT subfield_view.content as ISBN

FROM sierra_view.bib_view, sierra_view.subfield_view

WHERE (subfield_view.record_id = bib_view.id) AND ((subfield_view.marc_tag = '020') OR (subfield_view.marc_tag = '024')) AND (subfield_view.tag = 'a')

LIMIT 100000;

Thanks for your help,

Liza






 



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Liza Miller Arend 

Systems Administrator 

Minuteman Library Network 

Dynamic new books list Innovative OPAC

From Sierra Listserv, here is SQL to get new books with in a specific date range, and LC call number
Page is http://library2.udayton.edu/newbooks -- Source code 

SELECT  *

FROM
  s_v.bib_record b JOIN S_V.record_metadata r  ON (b.record_id = r.id)
  LEFT OUTER JOIN S_V.bib_record_call_number_prefix n ON (b.record_id=n.bib_record_id)
  LEFT OUTER JOIN S_V.bib_record_property p  ON (b.record_id = p.bib_record_id)
  --    now link the bib, to the record(s)
  LEFT OUTER JOIN S_V.bib_record_item_record_link l  ON (b.record_id = l.bib_record_id)
  LEFT OUTER JOIN S_V.item_record_property I ON (l.item_record_id = i.item_record_id)

WHERE    -- limit to one call number prefix (eg, law)
  n.call_number_prefix like \'' . $call_prefix . '%\' AND
  b.cataloging_date_gmt >= date(\'' . $date_from . '\') AND
  b.cataloging_date_gmt <  date(\'' . $date_to   . '\') AND b.is_suppressed = FALSE

ORDER BY
            i.call_number_norm

PostgreSQL and NULL

If activity_gmt for a particular row is null is NULL does an SQL query activity_gmt>='2012-03-01' retrieve it?      It will not be included, see answer from Sierra Listserv:

It won’t be included.  I’ll explain because how NULL works in Boolean expression is counter-intuitive at first.

Mostly commonly NULL means “missing value” or “no value at all”.  But in SQL NULL means “could be any value at all, but that value is unknown to you”.

If I was to ask you “I’m thinking of some date and you don’t know what that date is. Now tell me if that date is >= 2012-03-01.”  Your answer is going to be “I don’t know. How could I possibly know?”

In SQL that same conversation would be like this.

[sierra-db iii] psql> SELECT NULL >= DATE '2012-03-01';

+----------+

| ?column? |

+----------+

| (null)   |

+----------+

In a WHERE clause of a SELECT statement, you want to know if your Boolean expression is true and the row will be included, or false and the row won’t be included.

So the next question is “Is NULL = TRUE?”. Read this as “Is some Boolean value that is unknown to you equal to true”. The answer obviously is (except to Microsoft) “I don’t know. How could I possible know?”.  That is, the answer is NULL.

[sierra-db iii] psql> SELECT NULL = TRUE;

+----------+

| ?column? |

+----------+

| (null)   |

+----------+

It turns out — and this is as much a surprise to programmers as it is to librarians, and where Microsoft went wrong with Access and SQL Server — that in SQL there is not just TRUE and FALSE, but TRUE, FALSE and NULL. That’s right, in SQL NULL is actually a third Boolean state, not a distinct type or value. Booleans in SQL are like those check boxes you see sometimes in a dialog box where the checkbox can be clear (FALSE), ticked (TRUE) or grey (NULL).

So actually in a WHERE clause you need to know about third NULL state.  In ANSI-standard SQL, a WHERE clause that calculates as NULL for a row will excluded that row.

BTW Is NULL = NULL?  Microsoft says it is, and most programming languages will say is.  ANSI-standard SQL says “You’re asking me if 2 values unknown to me are equal!? How could I possibly know!? NULL.”

Regards,

Jim

JIM NICHOLLS | Library Technician

------------------------------

Hi,

If I write an SQL query like activity_gmt>='2012-03-01' and activity_gmt for a particular row is null, will that record get returned or not? On Sybase, it would not be returned but I want to make sure the same is true in PostgreSQL.  I looked up the documentation and am leaning toward it not being returned but I'd like to make sure. (http://www.postgresql.org/docs/9.1/static/functions-comparison.html).  For now to be on the safe side,  I'll write the queries like (activity_gmt IS NOT NULL AND activity_gmt>='2012-03-01') but I'd like to know how essential this is.

Thoughts?

Thanks,

Gem Stone-Logan
IT Application Engineer
High Plains Library District

Removing narration from powerpoint presentation

Had a MS Powerpoint presentation with audio clips on each slide, want to remove all to get file with slides, here is quick method:

Go to the Slide Show menu » Record Slide Show » Clear Narrations.


From:  http://superuser.com/questions/462720/remove-the-audio-narration-from-a-powerpoint-presentation

Fuzzy images in Powerpoint

In powerpoint slides, to make images sharper, use best resolution setting in slide show, and make sure aspect ration is locked:


From MS knowlegebase:

To avoid distortion of imported bitmap graphics, follow these steps:

1. In Slide view, click the video or bitmap that you want to resize.
2. On the Format menu, click Picture, and then click the Size tab.
3. Click Reset, and then click OK.
4. On the Format menu, click Picture, and then click the Size tab.
5. Click to select the Lock aspect ratio check box.
6. Click to select the Best scale for slide show check box.
7. In the Resolution list, click the resolution that most closely matches the resolution of the display on which the presentation will be shown, and then click OK.