Table of Contents

SQL Error Messages

When using Alma Analytics, sometimes trying to run a report will result in an error message. To find the type of error, click on Error Details to expand. The useful part of the error message is probably ½ to ⅔ of the way down.

Note: Technically, we’re running analysis, not reports. Reports mean something different in Oracle Analytics Server but when talking about Alma Analytics we tend to use the term reports interchangeably with analysis.

Troubleshooting Specific Types of Errors 

Illegal Syntax

Syntax errors almost always occur due to customizing Analytics using SQL. When troubleshooting, focus on the parts of the report you manually changed. Look for:

• Incorrect number of parenthesis

• Using curly braces instead of parentheses

• Smart quotes

• Forgetting a beginning or ending quote

• Using double quotes instead of single quotes, and vice versa

• Misspelling SQL Keywords (such as CONT instead of COUNT)

Example Errors

Smart Quote Example

State: HY000. Code: 26002. [nQSError: 26002] Near <">: End of input has been reached. Illegal syntax.

Missing Parenthesis Example

State: HY000. Code: 27001. [nQSError: 27001] End of input has been reached. Illegal syntax.

Corresponding Arguments

Make sure there are the same number of parameters (%1, %2, etc) as arguments.

For example:

EVALUATE('regexp_replace(%1,%2,%3,%4,%5,%6)', "Bibliographic Details"."Publication Date", '[^0-9]', '')

In the example above, we are giving it 3 arguments:

• The Column name: "Bibliographic Details"."Publication Date"

• The regular expression: '[^0-9]'

• Non-numbers should be replaced with nothing: ''

However, we had told it there were 6 parameters so when it can't find an argument for parameter number 4, it will stop and return an error message.

Example Error

State: HY000. Code: 22050. [nQSError: 22050] Parameter marker 4 does not have the corresponding argument in the evaluate expression: regexp_replace(%1,%2,%3,%4,%5,%6).

Nonexistent column

• Double quotes are only for column names

• Check the column name for typos

• Make sure the column exists in the selected subject

  • For example, if you're creating an analysis using the Titles subject, you won't be able to use a column from E-Inventory.

Example Error

State: HY000. Code: 27045. [nQSError: 27045] Nonexistent column: "Yes".

Non-Compatible Types

Data type determines the type of values that can be stored in a column. The data type of your column must match the data type of what you're trying to do with it. For example, if your column has a DATETIME format, you cannot use a function designed for strings/text.

• Check the Alma Analytics Subject documentation and see if it says what data type the column is

  • Some data that looks like a number is really a VARCHAR (example, MMS Ids)

  • Some data that looks like text is really datetime (example, creation dates)

• Is what you're trying to do compatible with that data type?

• If not, either

  • Change the data type via the CAST function

  • Figure out a different approach

Common Alma Analytics Data Types

String/Text

• VARCHAR

• CHAR

Date and Time

• DATE

• DATETIME

• TIMESTAMP

Numbers

• INT

• NUMBER

• DOUBLE

• FLOAT

Example Error

State: HY000. Code: 22024. [nQSError: 22024] A comparison is being carried out between non-compatible types TIMESTAMP and VARCHAR.

Maximum Query Governing Execution Time

This is a timeout error, your report likely took more than 30 minutes to run.

• If possible, add filters

• See if you can make your report more efficient

  • For example, feeder reports can really slow a report down, is there an alternative that will work?

• Run at a different time of day

  • Potentially schedule the report

• Create a ticket with Ex Libris

Example Error

State: HY000. Code: 60009. [nQSError: 60009] The user request exceeded the maximum query governing execution time.

Inaccessible Column

Only the original "Designs Analytics" role has access to all subjects in Alma Analytics. See Ex Libris' documentation for more information about the limited Analytics roles.

What the Error Looks Like

Edit

When editing a report you don't have permissions to run, a window will pop-up that says "You do not have access privilege for this subject area.

Open

When trying to open a report you don't have permission to run, you will get an SQL error. When expanded, part of the error will say you have an "Inaccessible column".

Checking Your Roles in Alma Analytics

1. Click the user profile icon on the top right on Alma Analytics

2. Click on My Account

3. Click on the Applications Roles tab

If you have a specific Alma Analytics role, you should see a corresponding role in the Applications Role tab:

Designs Analytics [everything]: ALMA_ANALYTICS_DESIGN

Designs Analytics - Acquisitions: ALMA_ANALYTICS_ACQUISITION_DESIGN

Designs Analytics – Fulfillment: ALMA_ANALYTICS_FULFILLMENT_DESIGN

Designs Analytics - Resources: ALMA_ANALYTICS_PHYSICAL_DESIGN

Designs Analytics – System Usage: ALMA_ANALYTICS_SYSTEM_DESIGN

Designs Analytics – Users: ALMA_ANALYTICS_USER_DESIGN

Example Open Error:

State: HY000. Code: 27046. [nQSError: 27046] Inaccessible column: "Event Details"."Event Creator".

Access Denied for User to Path

This usually happens when someone copied something from their private My Folder to a Shared Folder or if they copied something to Shared Folders/Community from an Institution's individual Shared Folder.

This tends to be a common problem for things like:

• Reports that have a filter based on another report (feeder report)

• A column used in the report was saved in a private folder

Example Error

Access denied for user to path /users/he_50356525640006531_6531_d_na07.alma.exlibrisgroup.com/_filters/Titles/Custom Publication Date

No Fact Table

• Are you trying to create a multi-subject report with subjects that can’t be combined?

• If not, there’s probably something wrong with the underlying schema. Create a ticket with Ex Libris.

  • If you wish to troubleshoot more, try removing columns one-by-one, trying to retrieve results after each removal, to see if you can narrow down which column is the problem

Example Error

State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail

. . . 

State: HY000. Code: 14081. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references

Temp File

You will likely only get a temp file error if you're creating a report using the Analysis From Simple Logical SQL option. 

Your query is retrieving too much data. Your only option is to figure out a way to retrieve less data either via a filter or trying a different approach.

Example Error

State: HY000. Code: 46234. [nQSError: 46234] Operation APagedBufferFile::WriteAndInsertFilePageDescriptor failed, during creation of temp file: /dev/shm/nQS_EX_17898_2965_c33c0004_77384628.TMP, as it'll exceed the current configured limit. Current configured size limit in MB: 5120

Dashboard Errors

A Dashboard Report is Blank

• Was the dashboard copied from another location you don't have access to?

• Instead of copying dashboards, it's better to use the Archive/Unarchive feature

Referenced Catalog Object Inaccessible

• Was the underlying report renamed without choosing "Preserve references to old name of this time"?

Path not found - check the input path entered

• Did the Dashboard's folder get renamed without preserving references? Or, did it get renamed multiple times?

Dashboard Prompts

• Does the report have a prompted filter?

• Does the report already have a local prompt that is conflicting with the Dashboard prompt?

• Make sure there's nothing on the report's prompt tab

C-runtime error

C-runtime errors happen when the system is down. Wait 15 minutes and if it's still a problem, submit a ticket to support.