Scott Beeson · Feb 12, 2016

Help with SQL error


    [SQLCODE: <-400>:<Fatal error occurred>]
    [Cache Error: <<SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]
    [Location: <ServerLoop - Query Fetch>]
    [%msg: <Unexpected error occurred:  <SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]


Here is an example of a query that gives the error:

    SELECT COUNT(DISTINCT Criteria) as Relevance FROM HS_IHE_ATNA_Repository.Aggregation

The content of the actual field is XML, but the query simply returns a number.  Here is one that works:

    SELECT COUNT(DISTINCT DestinationURI) FROM HS_IHE_ATNA_Repository.Aggregation

The result here is "8"

Anyone have any ideas?

0 3,902
Discussion (10)0
Log in or sign up to continue

I don't understand so I'll just leave it at 490 for now :)

And yes, there are still long values (this is a log so they won't go away).  I found other fields besides Criteria too.

What is the Criteria datatype?

Can you show the property definition?

If for example you have Criteria defined like this:

Property Criteria As %String(MAXLEN = 2000); // MAXLEN>512

Try to define the property like this:

Property Criteria As %String(COLLATION = "TRUNCATE(490)", MAXLEN = 2000);


How to debug SQL queries:

1. In SMP > System > Configuration > General SQL Settings  enable the following flags (don't forget to press Save):

  • Retains SQL Statement as Comments in .INT Code
  • Cached Query - Save Source  

2. Purge all Cached Queries - execute in terminal (namespace with query):

  • Do $SYSTEM.SQL.Purge()

3. Execute the query again to get an error

4. Open in studio routine (it would be after purge)

5. Go to %0AmEdun+4 in this routine and see what this is about. Set try/catch, and debug.


In this case the most likely scenario is that the code tries to set global subscript longer than the maximum of 511 characters. Since the only change between queries is the column (there is also "as Relevance" part in your error query, but I think it's irrelevant) then it's probably something with that.

Knowing what's in the Criteria field, I assume you are right that it's a length issue.  I don't have the ability to change this on the Cache side, so do you know how I can improve my query to get around these errors?

Modify your query like this:

SELECT COUNT(DISTINCT $EXTRACT(Criteria, 1, 490)) AS Relevance 
FROM HS_IHE_ATNA_Repository.Aggregation 

Only symbols 1 to 490 would be used for comparison in DISTINCT condition, also see $EXTRACT docs.

Sorry I'm just getting back to this.  Your $EXTRACT solution works great, but why did you choose 490?  I did a quick test with 600 and it still seems to work.  What is the maximum?  or is it dependent upon the processor?

OK one more try.

When you use DISTINCT or GROUP BY we are going to build a temp global using the Field values as subscripts.  So say we have a query with GROUP BY Name, Title

the table has data that looks like this:

Scott     Customer

Brendan Support

Eduard Customer

Brendan Trouble Maker


To figure out the GROUP BY we will build a global that looks like this:


^||sql.temp(1,"Brendan","Trouble Maker")=""




With this data the longest global node is for that Trouble Maker, 39 total charaters.  This is well under 511 so no subscript error.  Now if I have a Title that is 500 characters long then the total length of the global would be over 511 and we would get a <SUBSCRIPT> error.

The more fields you put into a GROUP BY the easier it will be to get over the 511 charater limit as each field will be a different subscript in a global node.


Do you still have long values for Criteria in your data?  The documented limit for a global name plus subscripts is 511

Depending on the version of Cache the global used internally might change,  For my example the global is ^||%sql.temp so 12 characters+490+(a subscript we are using 2 characters)+ 1 commas +() and we are up to 507


[%msg: %0AmBk1+6^%sqlcq.datakit.cls30.1>]

Please help me with this..

can you please start a new question for this error.  Include the SQL and the full error message.  -400 should be reported for an COS error like <UNDEFINED>,  <SUBSCRIPT>, <FILEFULL>....