Polaris SQL/Reports Forum Notes

Conferences & Events IUG Conference 2024
1

Polaris SQL/Reports Forum

March 27, 2024

SQL Resources

Why should I learn SQL? How does this differ from SimplyReports?

  • SimplyReports is useful for creating custom reports with a (somewhat) intuitive interface, but it does have limitations. Specifically, it can only output specific columns and can only use the pre-determined filters available in the content drawers.
  • SQL gives complete access to the Polaris database. Every piece of information in Polaris is stored somewhere in a table in the database. With SQL, users can query those tables and retrieve exactly what they are looking for.
  • SQL is also completely text-based, so queries can be copied and stored for later. Rather than re-creating an entire search in SimplyReports, it’s sometimes easier to simply copy and paste a previously built query.
  • SimplyReports is based on SQL. In the background, SimplyReports builds a SQL query based on the user input and sends that query to the Polaris database.

How do I see the query that SimplyReports builds?

  • Create and run a report in SimplyReports as normal. After clicking Submit, you’ll be taken to the report results page.
  • On the report results page, right click and select “View Page Source” or in most browsers, press CTRL+U.
  • If prompted with a “Confirm Form Resubmission” message, hit Refresh in the browser or press F5. The browser will display the HTML that generates the page.
  • Press CTRL+F to search the HTML page. Search for SQLStatementHide
  • Following the SQLStatementHide text, the “value” field will display the SQL query. (It starts with “Select”). Select the text and copy it.
  • Paste the text into NotePad or a preferred text editor. The query will be formatted for the web, so it may be necessary to add line breaks for readability or convert certain text like date fields.

Why can’t SimplyReports search for specific MARC data? Coming from a Sierra system with access to Create Lists, this feels like a very serious limitation.

  • That is correct that SimplyReports cannot search for specific MARC fields/subfields.
  • The only way to search for specific MARC tags is through SQL. This can be a barrier for many staff, so it’s recommended to save some pre-built queries to the Bib Find Tool:

Bibs with Specific Tag

SELECT DISTINCT BR.BibliographicRecordID
FROM BibliographicRecords BR
JOIN BibliographicTags BT ON BR.BibliographicRecordID = BT.BibliographicRecordID
WHERE BR.RecordStatusID = 1
AND BT.TagNumber = 263

Bibs with Specific Tag and Subfield Data

SELECT DISTINCT BR.BibliographicRecordID
FROM BibliographicRecords BR
JOIN BibliographicTags BT ON BR.BibliographicRecordID = BT.BibliographicRecordID
JOIN BibliographicSubfields BS ON BT.BibliographicTagID = BS.BibliographicTagID
WHERE BT.TagNumber = 250
AND BS.Subfield = 'b'
AND BS.Data LIKE '%'
  • For both queries, the tag number can be replaced with the 3-digit tag number.
  • For subfields, the subfield should be placed between single quotes.
  • For subfield data, the percent sign is a wildcard (when used with LIKE). The wildcard should almost always be used at the end of the phrase (to account for punctuation), but it can also be used at the beginning too.

The MARC data tables seem unnecessarily complicated. Why do we need to join 3 tables together just to look at a single field? Wouldn’t it make more sense to have all of this data in one table?

  • We’re speculating, but this likely has to do with database theory and how tables should be structured. The Polaris data tables were designed to accommodate MARC as it existed when it was built, and into the future. As new tags/subfields have been added to the MARC model, the Polaris tables are able to easily include them.
  • Theoretically, a more human-friendly structure would likely include tags and subfields in a single table that contained columns for the tag number, Indicators 1 & 2, the subfield code, and the actual data. For example:
TagID TagNumber Indicator1 Indicator2 Subfield Data
1234 245 1 0 a SQL in a nutshell :
1234 245 1 0 b a desktop quick reference /
1234 245 1 0 c Kevin Kline, Regine O. Obe, and Leo S. Hsu.
  • In this theoretical example, the TagID, number, and indicators are repeated which adds to database size and creates the potential for data mismatches. In database-speak, this violates 2nd Normal Form.
  • The general assumption is that these Polaris tables were designed to follow normalization rules. As a result, we end up with a structure that makes sense for the database, but is more difficult for human readability.
3 Likes
2

Great point that is almost certainly the case.

Beautiful example and great table!

The good news is that sometimes you CAN have the best of both worlds, proper database form + human readable, through something called SQL Views.

You interact with SQL views much like data tables*, but the views are typically stitched together from several different sources and usually formatted in such a way to make the data more human readable.

Although, I’m not 100% certain, I believe much of Sierra SQL is actually using views, rather than direct table access which could be what makes interacting with them feel more natural.

The good news is that Polaris database programmers like efficiency too and in many cases if you find yourself having to do lots of joins to get at some data, there is probably a SQL view available.

For the bib/tag example, there is a view called BibliographicTagsAndSubfields_View, that when you run this statement SELECT TOP (1000) * FROM [Polaris].[Polaris].[BibliographicTagsAndSubfields_View] produces an output nearly identical to @mhammermeister ideal table example:

image
image1994×494 187 KB

And since SQL Views behave like selecting data from a table, we can adjust our query to match the example:

image
image1588×534 150 KB

You can see all the views available to you in SQL Server Management Studio (SMSS) by expanding the views folder:

image
image770×1192 110 KB

Views can also be a handy way to get around Find Tool SQL limitations. If you find yourself unable to use your complex SQL as a find tool search, you may be able to create a view that is accessible through the find tool instead.

*Note: I think views provide read-only access to the data, whereas with tables, you can insert/update/delete data. Of course, I wouldn’t be updating bib data via SQL under any circumstances given all the myriad of places that would likely need updated to avoid corrupting data.

3 Likes
3

Completely correct, Sierra only allows view access, though the great majority of those views are simply 1 to 1 copies of the underlying tables.

2 Likes
4

I am WAAAY too lazy to be adding my own line breaks in SQL, so, I use: Instant SQL Formatter & Beautifier Online (or something similar)

Of course, I also do the same thing with SQL I write. Especially when I can’t get something to work and have to share it with our developer and I don’t want to be embarrassed by the way I poorly format my joins :laughing:

5

I’d like to encourage everyone who codes SQL to develop and adhere to a style which you find facilitates readability and comprehension over the long term.  It is important when revisiting a query used for an annual report (or annual effort such as library card enrollment drives) that last month’s/quarter’s/year’s code be readily understood.  Add an explanatory comment block at top and comment as you go, particularly any time obscure numeric or alpha codes appear in CASE statements, JOIN predicates and/or the WHERE clause.

I am of the attitude that whitespace is free, so my SQL style employs a lot of 4-space tabs and newlines.  While I do not leave entire lines blank, SELECT, CASE, WHEN, ELSE, GROUP BY, ORDER BY, UNION and the terminating semicolon tend to appear alone on a line.  In the context of CTEs (Common Table Expressions) and medium-large subqueries, the enclosing parentheses will likewise be preceded by a newline.