The Use of SQL Joins

The ability to write SQL joins opens up new possibilities for Analytics reporting.

Here is a working sample query

SELECT
	A.mms_id, 
	A.title, 
	B.due_date 
FROM (
	SELECT
   	"Physical Items"."Bibliographic Details"."Language Code" lang_code,
   	"Physical Items"."Bibliographic Details"."MMS Id" mms_id,
   	"Physical Items"."Bibliographic Details"."Title" title
	FROM "Physical Items"
	WHERE
		("Physical Items"."Bibliographic Details"."Language Code" = 'mon')
) A
LEFT OUTER JOIN (
	SELECT
	   	"Fulfillment"."Bibliographic Details"."MMS Id" mms_id,
  	 	"Fulfillment"."Loan Details"."Due Date" due_date
	FROM "Fulfillment"
) B
ON A.mms_id = B.mms_id
WHERE B.due_date IS NOT NULL

A few notes:

This query retrieves the titles, MMS IDs, and due dates of all Mongolian items. It only displays items with a due date. This is accomplished by joining the "Physical Items" and "Fulfillment" subjects areas on the MMS ID.
The final WHERE clause is what filters the list to those with a due date. Removing this clause will display all Mongolian titles - the due date field will simply be blank for items that do not have one. Adding such a WHERE clause essentially turns the LEFT OUTER JOIN into an inner join (returning records with matching values from both tables. Note: LEFT JOIN will give different results. See information on SQL joins below.
SQL is not case sensitive but syntax and punctuation are important, including the placement of periods, commas, and single vs. double quotes. Slight variations can cause generic "syntax errors" that are hard to track down.
Custom labels to the columns can be applied, but not by adding the labels after the list of output columns at the beginning of the query (as is shown in the ExL documentation). Instead, this was accomplished by renaming the s_1, s_2, etc. values every place where they occurred in the query.
The dialog opened by the "New Analysis" button may not always allow one to directly type into the text box. (The problem occurs inconsistently). It may be easiest to construct and edit the query in a text editor (e.g. Notepad), and then just copy and paste into the text box.
Information on SQL joins: https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj29840.html https://www.w3schools.com/sql/sql_join.asp