New post
Question
· Feb 12, 2016

Help with SQL error

#SQL #Caché

 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
    WHERE EventType = 'CROSS GATEWAY QUERY'

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
    WHERE EventType = 'CROSS GATEWAY QUERY'

The result here is "8"

Anyone have any ideas?

Discussion (10)0
Log in or sign up to continue
Eduard Lebedyuk · Feb 15, 2016

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 %sqlcq.HSREGISTRY.cls966.1.int routine (it would be %sqlcq.HSREGISTRY.cls1.1.int 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.

2 0
Brendan Bannon · Feb 25, 2016

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","Support")=""

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

^||sql.temp(1,"Eduard","Customer")=""

^sql.temp(1,"Scott","Customer")=""

 

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.

1 0
Brendan Bannon · Nov 16, 2016

Scott

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

http://docs.intersystems.com/cache20152/csp/docbook/DocBook.UI.Page.cls?...

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

0 0